How to evolve MySQL schema while maintaining integrity

Question

As I understand it, when you run a MySQL DDL statement, it will often lock the table, and then unlock it. It will even unlock it even if you had manually locked it. Also, schema modifications cannot be grouped by a transaction.

Therefore, if you have a migration which removes some foreign key constraints, alters the related tables, and then replaces the constraints with new ones, you could have a problem. If another database client has inserted an invalid value while the foreign key constraints were removed, the new foreign key constraints may not be possible to apply even if the migration SQL was correctly written. That's just one example, but there are probably other ways that this problem could manifest.

How do you deal with this in MySQL? I am using version 5.6.

Answer 1

You could probably use the online schema change feature of ALTER TABLE in MySQL 5.6

Please note the MySQL 5.6 Documentation for ALTER TABLE

Specifying ALGORITHM=INPLACE makes the operation use the in-place technique for clauses and storage engines that support it, and fail with an error otherwise, thus avoiding a lengthy table copy if you try altering a table that uses a different storage engine than you expect. See Section 14.11, “InnoDB and Online DDL” for information about online DDL for InnoDB tables.

You would run the ALTER TABLE like this:

ALTER TABLE mytable ALGORITHM-INPLACE ... ;

What this will do is create a shadow copy of the table being changed. All reads and writes can go on with the original table. Additional changes made to the table is placed in the shadow copy. Everything is merged and presented at the end.

CAVEAT #1

The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can fit entirely in tmpdir. For example, if tmpdir is mapped to /tmp and the table you are changing is 7GB, there has to be more than 7GB free in /tmp. If /tmp is not large enough, please consider mounting /tmp on a large volume.

CAVEAT #2

Again, from the MySQL 5.6 Documentation for ALTER TABLE

As of MySQL 5.6.16, ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary.

Changing Columns with older the DATETIME format will not work. You would have to use other means to make the new table.

Answer 2

Note the LOCK option to ALTER TABLE.
From http://dev.mysql.com/doc/refman/5.6/en/alter-table.html :

You can control the level of concurrent reading and writing of the table while it is being altered, using the LOCK clause. Specifying a non-default value for this clause lets you require a certain amount of concurrent access or exclusivity during the alter operation, and halts the operation if the requested degree of locking is not available. The parameters for the LOCK clause are:

LOCK = DEFAULT Maximum level of concurrency for the given ALGORITHM clause (if any) and ALTER TABLE operation: Permit concurrent reads and writes if supported. If not, permit concurrent reads if supported. If not, enforce exclusive access.

LOCK = NONE If supported, permit concurrent reads and writes. Otherwise, return an error message.

LOCK = SHARED If supported, permit concurrent reads but block writes. Note that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. If concurrent reads are not supported, return an error message.

LOCK = EXCLUSIVE Enforce exclusive access. This will be done even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.