Transactional DDL workflow for MySQL 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?
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


support transactions that are properly isolated with START TRANSACTION/COMMIT blocks
control of DDL by scripting the DDL yourself, running such DDL as either constructor or destructor


Constructor : DDL to make Tables with a New Design
Destructor : DDL to make Tables Revert Back to Previous Design

never combine these operations under one job


WARNING : 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


System Tablespace


Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

InnoDB Buffer Pool


Dirty Data Pages
Dirty Index Pages
Changes to NonUnique Indexes

Other Important Memory Caches


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



Run SET GLOBAL innodb_fast_shutdown = 0;


Run SET GLOBAL innodb_max_dirty_pages_pct = 0;


Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';


Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible


service mysql stop


Take LVM snapshot


service mysql stop



If You Cannot Shutdown But Take Snapshot With MySQL Live



Run SET GLOBAL innodb_max_dirty_pages_pct = 0;


Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';


Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible


Take LVM snapshot


Run 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



Run SET GLOBAL innodb_fast_shutdown = 0;


Run SET GLOBAL innodb_max_dirty_pages_pct = 0;


Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';


Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible


service mysql stop


Take LVM snapshot


service mysql stop



If You Cannot Shutdown But Take Snapshot With MySQL Live

You could enforce a flushing of certain InnoDB tables



Run SET GLOBAL innodb_max_dirty_pages_pct = 0;


Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';


Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible


Run FLUSH TABLES innodb_tbl1,... FOR EXPORT; on critical InnoDB tables


Run FLUSH TABLES WITH READ LOCK;


Take LVM snapshot


Run UNLOCK TABLES;


Run 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 run START 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


It is best to use multiple DB Servers (3 or more) to perform restores and regression tests.
Convert remaining MyISAM tables to InnoDB if those tables do not need to remain MyISAM.
If your data content is sensitive, you should execute a CI job to scrub the data after restoring a snapshot before initiating any tests. As an alternative, you may want to take snapshots of MySQL with the data already scrubbed.