Transactional DDL workflow for MySQL

Question

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?

Answer 1

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

• 1. Run SET GLOBAL innodb_fast_shutdown = 0;
• 2. Run SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 3. Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 4. Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible
• 5. service mysql stop
• 6. Take LVM snapshot
• 7. service mysql stop

If You Cannot Shutdown But Take Snapshot With MySQL Live

• 1. Run SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 2. Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 3. Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible
• 4. Take LVM snapshot
• 5. 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

• 1. Run SET GLOBAL innodb_fast_shutdown = 0;
• 2. Run SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 3. Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 4. Repeat Step 3 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible
• 5. service mysql stop
• 6. Take LVM snapshot
• 7. service mysql stop

If You Cannot Shutdown But Take Snapshot With MySQL Live

You could enforce a flushing of certain InnoDB tables

• 1. Run SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 2. Run SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 3. Repeat Step 2 until Innodb_buffer_pool_pages_dirty is 0 or as close to 0 as possible
• 4. Run FLUSH TABLES innodb_tbl1,... FOR EXPORT; on critical InnoDB tables
• 5. Run FLUSH TABLES WITH READ LOCK;
• 6. Take LVM snapshot
• 7. Run UNLOCK TABLES;
• 8. 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.

Answer 2

If you talk about continuous integration then I assume it's a development environment. In that case I would say the person doing structural changes has to test them to ensure not to break things for others, much the same way someone updating a common library: Test in your own sandbox before commiting such changes.

In a production deployment process you would typically go through dev, QA or even pre-production environments to test your changes, the same way you would for any code changes.

Note that this is not MySQL specific: Oracle databases would also implicitly do COMMIT when issuing 'alter table' etc.

Now if you want to protect yourself you may of course do a backup beforehand, or a LVM or filesystem snapshot if your system can do that. You may also have a slave which you could delay/stop as a security before sensitive operations.