MySQL: Upgrade should enforce new tables with InnoDB

[anatoly techtonik <techtonik@…>]

During upgrade process, when Trac creates tables for MySQL, it should create a test temporary table and check if its type is MyISAM. If it is MyISAM, then Trac should enforce InnoDB engine on all newly created tables during upgrade process.

MyISAM table types breaks transactions, which becomes a source of magical bugs like #8067.

[Christian Boos]

Good idea, PatchWelcome (make that an IEnvironmentSetupParticipant.environment_needs_upgrade check, in the MySQLConnector).

[Jun Omae]

Proposed changes can be found in log:jomae.git:ticket10993.

The following are verified on init_db and environment_needs_upgrade of MySQLConnector:

• MyISAM engine of table isn't used
• utf8_bin or utf8mb4_bin is used as collation of table
• default_storage_engine variable isn't MyISAM
• character_set_database and collation_database variables are one of (('utf8', 'utf8_bin'), ('utf8mb4', 'utf8mb4_bin'))

[Peter Suter]

Looks good to me.

Are you concerned about temporary tables? Since 5.6.3 someone could sneakily set ​default_tmp_storage_engine. (Or other weird things, like use a ​CSV engine?)

comment:8:ticket:10661 proposed to:

to do a "safety check" (creating a (temp) table, verify the encoding; try to insert a few rows in a transaction (with upper/lower case conflicts); rollback and check if all rows are gone).

But I think your proposed changes are simpler and sufficient for the most likely problem of old MySQL with MyISAM default.

[Jun Omae]

Replying to psuter:

Are you concerned about temporary tables? Since 5.6.3 someone could sneakily set ​default_tmp_storage_engine. (Or other weird things, like use a ​CSV engine?)

Okay. Updated log:jomae.git:ticket10993 branch.

I've added the verifications for default_tmp_storage_engine and the engine is neither EXAMPLE, ARCHIVE, CSV nor ISAM that they don't support transactions in obvious.

[Jun Omae]

Pushed in [12148] and merge to trunk in [12149].