| Bug #65035 | FLUSH TABLES is missing on mysqldump with --dump-slave | ||
|---|---|---|---|
| Submitted: | 19 Apr 2012 11:34 | ||
| Reporter: | Mikiya Okuno | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Apr 2012 12:00]
MySQL Verification Team
By the way, mysqldump does not resume replication until dump is done even --single-transaction is specified. This is unacceptable when a user wants to backup from slave online.
=== modified file 'client/mysqldump.c'
--- client/mysqldump.c 2012-04-19 11:50:02 +0000
+++ client/mysqldump.c 2012-04-19 11:56:21 +0000
@@ -5412,7 +5412,7 @@
if (opt_slave_data && do_stop_slave_sql(mysql))
goto err;
- if ((opt_lock_all_tables || opt_master_data
+ if ((opt_lock_all_tables || opt_master_data || opt_slave_data ||
(opt_single_transaction && flush_logs)) &&
do_flush_tables_read_lock(mysql))
goto err;
@@ -5455,6 +5455,10 @@
if (opt_single_transaction && do_unlock_tables(mysql)) /* unlock but no commit! */
goto err;
+ /* Start slave SQL thread when single transaction is specified. */
+ if (opt_slave_data && opt_single_transaction && do_start_slave_sql(mysql))
+ goto err;
+
if (opt_alltspcs)
dump_all_tablespaces();
@@ -5480,7 +5484,7 @@
}
/* if --dump-slave , start the slave sql thread */
- if (opt_slave_data && do_start_slave_sql(mysql))
+ if (opt_slave_data && !opt_single_transaction && do_start_slave_sql(mysql))
goto err;
/* add 'START SLAVE' to end of dump */
[20 Apr 2014 21:50]
Tomokazu YONETANI
Hi, what's the progress on this bug? Since it's marked as Verified, I assume this is a valid bug report, no? I took a quick look on mysqldump.c in both 5.5.37 and 5.6.17, but I couldn't find similar code in it, nor could I find this mentioned in the release notes for MySQL 5.5.
[22 Apr 2014 12:44]
rj03hou rj03hou
great, but I think when execute flush tables with read lock, the sql_thread is already waiting, so the position where the slave is execute is ok, why need to stop sql_thread?
[1 May 2014 2:23]
Tomokazu YONETANI
Well, I believe that the STOP SLAVE SQL_THREAD is issued by do_stop_slave_sql() because --dump-slave is specified on the command line, not something added by the patches in this issue.
[1 May 2014 4:22]
Tomokazu YONETANI
The global read lock alone does not block all operations which can modify the database, e.g., DDL/DML to a temporary table http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-8.html so you can't get rid of STOP SLAVE SQL_THREAD.
Description: mysqldump does not issue FLUSH TABLES WITH READ LOCK statement when --dump-slave option is specified. This result in inconsistency between backup and slave information. How to repeat: shell> mysqldump -hhost -uuser -p db --single-transaction --dump-slave When executing this command, I can see general query log like below: Tcp port: 20777 Unix socket: /tmp/mysql_sandbox20777.sock Time Id Command Argument 120419 20:06:50 6 Quit 120419 20:07:17 7 Connect msandbox@localhost on 7 Query /*!40100 SET @@SQL_MODE='' */ 7 Query /*!40103 SET TIME_ZONE='+00:00' */ 7 Query SHOW SLAVE STATUS 7 Query STOP SLAVE SQL_THREAD 7 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 7 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 7 Query SHOW SLAVE STATUS 7 Query UNLOCK TABLES : snip Which lock does UNLOCK TABLES statement above release? LOCK TABLES WITH READ LOCK should be executed before SHOW SLAVE STATUS. Suggested fix: === modified file 'client/mysqldump.c' --- client/mysqldump.c 2012-04-19 11:19:35 +0000 +++ client/mysqldump.c 2012-04-19 11:21:54 +0000 @@ -5412,7 +5412,7 @@ if (opt_slave_data && do_stop_slave_sql(mysql)) goto err; - if ((opt_lock_all_tables || opt_master_data + if ((opt_lock_all_tables || opt_master_data || opt_slave_data || (opt_single_transaction && flush_logs)) && do_flush_tables_read_lock(mysql)) goto err;