Description:
Documentation for MySQL server 5.5 and 5.6 describing mysqldump's --single-transaction option is misleading.
http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction
Says:
"
Before MySQL 5.5.3, while a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail. As of MySQL 5.5.3, metadata locking prevents this situation.
"
The problem is that "As of MySQL 5.5.3, metadata locking prevents this situation." is not correct. Even after 5.5.3 concurrent DDL on tables being dumped by mysqldump --single-transaction call lead to aborted dump due to ER_TABLE_DEF_CHANGED error and even to inconsistent dumps.
Such situation can occur if concurrent DDL manages to sneak between the moment when mysqldump starts transaction and the moment when it starts dumping data for specific table and acquires metadata lock on this table. In this case transaction won't be able to see old version of table which corresponds to it consistent snapshot.
Another similar problem is that new table might get created after consistent snapshot has been started but before mysqldump retrieves list of tables in database to dump. In this case table mysqldump will try to dump table which should be invisible to transaction and will get ER_TABLE_DEF_CHANGED error as well.
(Moreover - getting ER_TABLE_DEF_CHANGED error is something typical for 5.6,
for 5.5 one can easily get wrong data in these cases).
On a more higher level problem can be described as a discrepancy between consistent snapshot transaction for which validity point is set at the start of transaction by creating snapshot and data-dictionary/metadata transaction for which validity point is established by acquiring metadata locks on tables used and which thus is corresponds to the end of transaction/commit time.
How to repeat:
See:
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction
And
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction
Suggested fix:
Remove wording implying that it is safe to run DDL concurrently to "mysqldump --single-transaction" from documentation. Use wording similar to one used at
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction to discourage usage of such scenarios for both 5.5 and 5.6.
