Run Schema changes in a transaction

[goetzk]

Not sure if this is a question or a feature request sorry.

It would be really good if schema upgrades (eg 196 to 197) ran in transactions. I had migration 197 fail on the first attempt (for unrelated reasons) and on the second attempt i_created_on already existed (https://github.com/collectiveaccess/providence/blob/dev/php8/support/sql/migrations/197.sql#L9).

Transactions would allow things to run in a more idempotent way, but a con is that it probably cause an increased load on DB servers during the updates.

[kehh]

Unfortunately MySQL and friends have a wonderful feature called an implicit commit on any ALTER TABLE statement, so any schema changes that actually change the schema can't reliably run inside a transaction.

See https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html for more details.

[goetzk]

Ah I see, yes that does look like it would prevent any substantial transaction wrapping :⁠-⁠\

…

On 11 November 2024 3:43:58 pm AEDT, Kehan Harman ***@***.***> wrote: Unfortunately MySQL and friends have a wonderful feature called an implicit commit on any `ALTER TABLE` statement, so any schema changes that actually change the schema can't reliably run inside a transaction. See https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html for more details. -- Reply to this email directly or view it on GitHub: #1644 (comment) You are receiving this because you authored the thread. Message ID: ***@***.***>