Mysql – How to evolve MySQL schema while maintaining integrity

MySQL

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.

Best Answer

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.

Related Solutions

MySQL import LOCK doesn’t prevent PRIMARY key issues

Whenever you dump a mysql table that has an AUTO_INCREMENT column, the next value is always attached to the definition of the table. You should see something like:

) ENGINE=InnoDB AUTO_INCREMENT=<some-number> ...

You may want to consider one of three(3) things

SUGGESTION #1 : mysqldump the database with table structure

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database > my_database_data.sql

That way, the auto_increment is attached to the table definition

SUGGESTION #2 : mysqldump the data and table structure separately

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info > my_database_data.sql
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-data > my_database_schema.sql

You would load my_database_schema.sql first, then my_database_data.sql

SUGGESTION #3 : Read the next auto_increment of every table into a script

You can use a query like this to make the SQL script:

SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,' AUTO_INCREMENT=',autoinc,';')
FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc
FROM information_schema.tables WHERE table_schema='my_database') A;

In the OS, you can do it like this:

SQLSTMT="SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,'AUTO_INCREMENT=',autoinc,';')"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema='my_database') A;"
mysql -uroot -p -Ane"${SQLSTMT}" > autoinc.sql

Simply run autoinc.sql before my_database_data.sql

Give it a Try !!!

Mysql – Online Schema migration in MySQL : When to start

I think that Percona's pt-online-schema-change might be a better solution for you as it can do an ALTER with very little downtime. It does, however, require adding a TRIGGER to the table.

Runnig DDL on your table will lead you into rebuilding your table, mysql is a row based engine so you will suffer the index rebuild this can be quite expensive depending on your engine, table complexity is factor as well.