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:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[19 Apr 2012 11:34] Mikiya Okuno
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;
[19 Apr 2012 12:00] Mikiya Okuno
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.