| Bug #61854 | mysqldump --single-transaction --flush-log breaks consistency | ||
|---|---|---|---|
| Submitted: | 13 Jul 2011 13:52 | Modified: | 27 Jan 2012 13:58 |
| Reporter: | Mikiya Okuno | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 Jul 2011 12:05]
Sveta Smirnova
Bug #60847 was marked as duplicate of this one.
[22 Nov 2011 18:17]
Adam Whelan
I am having the same issue on 5.5.16 x64. With the following backup command I can't produce a consistent backup. I had no problem before when I was on 5.0. I noticed the problem went I used the dump to create a new slave server. When I started up my new slave I had numerous duplication alerts. After reviewing the data from the restore I could see that it did not create a consistent backup. Has there been any updates or progress? I need to create a new slave without stopping or completely locking my tables. Is there any way to currently do this? mysqldump -uxxx -pxxx -AFfq --single-transaction --master-data=2 --routines --triggers | /bin/gzip > backup.sql.gz
[27 Jan 2012 13:58]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
[27 Jan 2012 13:59]
Jon Stephens
Fixed in 5.5+. Documented in the 5.5.21 and 5.6.5 changelogs as follows:
When running mysqldump with both the --single-transaction and --flush-log
options, the flushing of the log caused an implicit commit, causing more
than one transaction to be used and thus breaking consistency.
Closed.
Description: When backing up InnoDB tables with mysqldump, so many people use --single-transaction option. In addition, --flush-log option is popular because it rotate the binary log so that DBA can apply binary logs from the very beginning of the binary log which is newly created upon the rotation. However, this combination doesn't work on MySQL 5.5, due to the change that FLUSH LOGS now causes implicit commit which didn't on 5.1. Actually, mysqldump uses mysql_refresh() C API function, but its effect is identical to FLUSH LOGS. Such implicit commit will causes some inconsistencies of data included in the backup. How to repeat: e.g. shell> mysqldump -h host -uroot -ppass --single-transaction --flush-log db The following is the example general log captured during mysqldump: 110713 22:37:33 5 Connect msandbox@localhost on 5 Query /*!40100 SET @@SQL_MODE='' */ 5 Query /*!40103 SET TIME_ZONE='+00:00' */ 5 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 5 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 5 Query UNLOCK TABLES 5 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME 5 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 5 Init DB test 5 Refresh /home/mikiya/opt/mysql/5.5.13/bin/mysqld, Version: 5.5.13 (MySQL Community Server (GPL)). started with: Tcp port: 5513 Unix socket: /tmp/mysql_sandbox5513.sock Time Id Command Argument 5 Query show tables The "Refresh" command terminates the transaction which is started via "START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */" command. So, the following queries will not be a part of this transaction. Suggested fix: Call mysql_refresh() before executing "START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */".