MySQL Bugs: #27850: mysqldump --single-transaction does not work if TRUNCATE/DROP/RENAME/ALTER

Bug #27850	mysqldump --single-transaction does not work if TRUNCATE/DROP/RENAME/ALTER
Submitted:	16 Apr 2007 13:05	Modified:	10 Sep 2007 17:39
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	all	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
All of the below is known, except I think for TRUNCATE TABLE, but I write a complete explanation. These are not InnoDB problems.
"mysqldump --single-transaction" dumps InnoDB tables inside a consistent read. This is efficient, but some commands should not be issued by any connection while the dump is in progress. Because the consistent read is not isolated from these commands. Possible wrong results:
- dumped data is not consistent accross two tables (dump corresponds to a state which never existed)
- dumped data is not matching the binlog's position reported by --master-data (rollforward from binlog is then impossible).
Assume mysqldump has started: it has determined the list of tables to back up (assume T is one of them, and is InnoDB), created a consistent read (via START TRANSACTION WITH CONSISTENT SNAPSHOT), and read the binlog position corresponding to this consistent read. Then before it starts actually SELECTing table T, one of these happens:
- another connection does TRUNCATE TABLE T; then SELECT * FROM T (from mysqldump) will return no rows, instead of returning the data of before-the-truncate; that is inconsistent with the binlog position and with already SELECTed tables.
- another connection does DROP TABLE T; then SELECT * FROM T (from mysqldump) is going to fail (unknown table) instead of returning the data of before-the-drop; that is inconsistent with the binlog position.
- another connection does RENAME TABLE T TO U; then SELECT * FROM T (from mysqldump) is going to fail (unknown table).
- after mysqldump has done before SHOW CREATE TABLE T, but before it has done SELECT * FROM T, another connection does ALTER TABLE T ADD COLUMN etc; then SELECT * FROM T (from mysqldump) will lead to rows with more fields than specified by the CREATE TABLE stored in the dump: importing the dump will fail.

Please, consider adding to the documentation (in the manual and in mysqldump --help) that these commands should not be issued on to-be-dumped tables while "mysqldump --single-transaction" is running. 

How to repeat:
An example of TRUNCATE TABLE confusing "mysqldump --single-transaction". I don't use mysqldump because it's not easy to cause concurrency problems with it, but the logic is the same.

First session:

mysql> create table i(a int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table j(a int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into j values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into i values(1);
Query OK, 1 row affected (0.00 sec)

It now starts a consistent read, and dumps i (playing the role of "mysqldump --single-transaction")
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from i;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

then another concurrent session does:
mysql> update i set a=a+10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> truncate table j;
Query OK, 1 row affected (0.00 sec)

then the first session finishes dumping:
mysql> select * from j;
Empty set (0.00 sec)

So the dump says "i contains 1, j is empty".
But this state never existed in the tables. And it corresponds to no
position in the binlog. It is an invalid dump.
What existed is (i/j): 1 / 2, and 11 / 2, and 11 / empty.

Suggested fix:
document

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The --single-transaction description now includes this paragraph:

While a --single-transaction dump is in process, to ensure a valid
dump file (correct table contents and binary log position), no other
connection should use the following statements: ALTER 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 performed by mysqldump to retrieve the
table contents to obtain incorrect contents or fail.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38115

ChangeSet@1.2561, 2007-11-20 10:25:07+01:00, guilhem@gbichot4.local +1 -0
  BUG#27850 "mysqldump --single-transaction does not work if TRUNCATE/DROP/RENAME/ALTER":
  this has already been closed by a note in the manual, now we
  complete this by a note in "mysqldump --help"

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.54

It seems that FLUSH TABLES WITH READ LOCK causes concurrent ALTER, DROP, RENAME, and TRUNCATE commands to hang, waiting for the lock to release.

That FLUSH command is documented that it acquires a "global read lock."  This phrase is also used in the mysqldump --lock-all-tables option.

Can you confirm that using mysqldump --lock-all-tables does the same thing as FLUSH TABLES WITH READ LOCK?  If so, it would be effective at blocking concurrent clients from performing ALTER, DROP, RENAME, or TRUNCATE.  This would seem to be a good solution to ensure consistent backups.

Hi. --lock-all-tables calls FLUSH TABLES WITH READ LOCK. Thus it is expected to block ALTER, DROP, RENAME, or TRUNCATE (unless there is a bug or I'm wrong). However, --lock-all-tables --single-transaction cannot work (mysqldump throws an error message): because lock-all-tables locks all tables of the server against writes for the duration of the backup, whereas single-transaction is intended to let writes happen during the backup (by using a consistent-read SELECT in a transaction), they are incompatible in nature.

@Guilhem Bichot you said:

Hi. --lock-all-tables calls FLUSH TABLES WITH READ LOCK. Thus it is expected to block
ALTER, DROP, RENAME, or TRUNCATE (unless there is a bug or I'm wrong). 

----------------------------

Can you please tell us how it can be confirmed; I am new to mysql so i dont know exactly how can i confirm it without knowing it i may taking inconsistent backups.

Thanks

To Mubashar Ahmad:
http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
says
"--lock-all-tables, -x
Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables. "
This global read lock blocks any modification to tables, using command "FLUSH TABLES WITH READ LOCK".
FLUSH TABLES WITH READ LOCK is documented in
http://dev.mysql.com/doc/refman/5.5/en/flush.html
"Closes all open tables and locks all tables for all databases with a global read lock until you explicitly release the lock by executing UNLOCK TABLES. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. "
So in theory it should work as I wrote earlier.
For additional safety, you can surely try a backup and verify: create a large table so that mysqldump takes several seconds to dump it; while mysqldump is running, issue another statement (INSERT, TRUNCATE etc) on the table and verify that it blocks until mysqldump ends.