Interrupted/Misconfigured MariaDB/MySQL migrations leave the database in a corrupt state

[Cyberboss]

Well known limitation of the engine, nothing we can do about it. (How is Sqlite better than MySQL at this?)

https://dev.mysql.com/doc/internals/en/transactions-notes-on-ddl-and-normal-transaction.html

Best remedy is to examine which migrations have been applied (Using the __EFMigrationHistory table), figure out which one was interrupted (The one after the last inserted row), and perform surgery to finish the migration. From there, TGS can recover.

[Cyberboss]

Because having Database:ServerVersion set incorrectly is the primary cause of this.

[Cyberboss]

Diagnosis: Skill issue?

[Cyberboss]

Link broken. Content from Wayback machine:

6.6 Additional Notes on DDL and the Normal Transaction

DDL statements and operations with nontransactional engines do not "register" in thd->transaction lists, and thus do not modify the transaction state. Besides, each DDL statement in MySQL begins with an implicit normal transaction commit (a call to end_active_trans()), and thus leaves nothing to modify. However, as noted above for CREATE TABLE .. SELECT, some DDL statements can start a *new* transaction.

Behavior of the server in this case is currently badly defined. DDL statements use a form of "semantic" logging to maintain atomicity: If CREATE TABLE t .. SELECT fails, table t is deleted. In addition, some DDL statements issue interim transaction commits: for example, ALTER TABLE issues a commit after data is copied from the original table to the internal temporary table. Other statements, for example, CREATE TABLE ... SELECT, do not always commit after themselves. And finally there is a group of DDL statements such as RENAME/DROP TABLE, which don't start new transactions and don't commit.

This diversity makes it hard to say what will happen if by chance a stored function is invoked during a DDL statement -- it's not clear whether any modifications it makes will be committed or not. Fortunately, SQL grammar allows only a few DDL statements to invoke stored functions. Perhaps, for consistency, MySQL should always commit a normal transaction after a DDL statement, just as it commits a statement transaction at the end of a statement.