I was a little surprised to discover that DDL statements (alter table
, create index
etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database alterations in a transaction locally (that was then rolled back) was an important part of my workflow. For continuous integration, the rollback was used if the migration hiccuped for any reason, so that at least we did not leave the database in a half-migrated state.
How do people solve these two problems when using MySQL with migrations and continuous integration?
Best Answer
For many people, the MySQL Achilles' heel is implicit commit.
According to Page 418 Paragraph 3 of the Book
the following commands can and will break a transaction
ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION
SUGGESTION
When it comes to MySQL, any ContinuousIntegration (CI)/SelfService jobs you construct should always make Transactional jobs and DDL scripts mutually exclusive.
This gives you the opportunity to create paradigms that would
START TRANSACTION/COMMIT
blocksWARNING : If you are using MyISAM for any this, you can (un)kindly add MyISAM to the list of things that can break a transaction, maybe not in terms of implicit commit, but definitely in terms of data consistency should a rollback ever be needed.
WHY NOT LVM ?
LVM snapshots are great and restoring whole instances of databases without having to perform heavy SQL processing is ideal. However, when it comes to MySQL, you have to account for two storage engines: InnoDB and MyISAM.
All-InnoDB Database
Look at InnoDB's Architecture (Picture courtesy of Percona CTO Vadim Tkachenko)
InnoDB has many moving parts
Taking an LVM snapshot of an all-InnoDB database with uncommitted changes floating in the Buffer Pool and Memory caches would yield a dataset that would require InnoDB crash recovery once the LUN is restored and mysqld started.
SUGGESTION FOR ALL-InnoDB
If You Can Shutdown MySQL Before Taking Snapshot
SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL innodb_max_dirty_pages_pct = 0;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
service mysql stop
service mysql stop
If You Cannot Shutdown But Take Snapshot With MySQL Live
SET GLOBAL innodb_max_dirty_pages_pct = 0;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SET GLOBAL innodb_max_dirty_pages_pct = 75;
All-MyISAM Database or InnoDB/MyISAM Mix
MyISAM, when accessed, maintains a count of open file handles against it. If MySQL crashes, any MyISAM table with an open file handle count > 0 will be marked as crash and in need of repair (even if nothing is wrong with the data).
Taking an LVM snapshot of a database that has MyISAM tables in use will have one or more MyISAM tables in need of repair when the snapshot is restored and mysqld started.
SUGGESTION FOR All-MyISAM or InnoDB/MyISAM Mix
If You Can Shutdown MySQL Before Taking Snapshot
SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL innodb_max_dirty_pages_pct = 0;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
service mysql stop
service mysql stop
If You Cannot Shutdown But Take Snapshot With MySQL Live
You could enforce a flushing of certain InnoDB tables
SET GLOBAL innodb_max_dirty_pages_pct = 0;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
FLUSH TABLES innodb_tbl1,... FOR EXPORT;
on critical InnoDB tablesFLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
SET GLOBAL innodb_max_dirty_pages_pct = 75;
Could MySQL Replication Help ?
While you could restore one LVM snapshot to two servers and setup MySQL Master/Slave Replication, that becomes an extra source of housecleaning when restoring snapshots.
If you run CI jobs on a Master and those jobs are small, replication could be a timesaver under certain circumstances. You could just run
STOP SLAVE;
on the Slave, kick off the CI jobs on the Master, and runSTART SLAVE;
on the Slave when the Master's data is certified.If the CI jobs alert too much data, you could restore LVM snapshot and setup replication from scratch. If you find yourself doing this often, you could probably do with setting up MySQL Replication.
FINAL THOUGHTS