MySQL Bugs: #16088: mysqldump cann't be used when innodb table had been locked by FLUSH READ LOCKS

Bug #16088	mysqldump cann't be used when innodb table had been locked by FLUSH READ LOCKS
Submitted:	30 Dec 2005 2:54	Modified:	19 Mar 2009 21:15
Reporter:	ming lu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	5.0.12+	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
The mysqldump.exe cann't be used when innodb tables had been locked by FLUSH READ LOCKS until they were unlocked. 
This problem exists only in Linux platform, the windows administrator can work properly.

How to repeat:
create several tables in a schema with innodb engine.
flush tables with read lock;
use the mysqldump.exe to backup the schema

Thank you for a problem report. No, it is not only Linux issue. Look:

mysql> create database test4;
Query OK, 1 row affected (0.02 sec)

mysql> use test4;
Database changed
mysql> create table t1 (c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> show processlist;
+----+------+----------------+-------+---------+------+---------------------------------+-----------------------------------------+
| Id | User | Host           | db    | Command | Time | State        | Info                                    |
+----+------+----------------+-------+---------+------+---------------------------------+-----------------------------------------+
|  9 | root | localhost:3972 | test4 | Query   |    0 | NULL        | show processlist                        |
| 11 | root | localhost:4107 | test4 | Query   |    5 | Waiting for release of readlock | LOCK TABLES `t1` READ /*!32311 LOCAL */ |
+----+------+----------------+-------+---------+------+---------------------------------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.01 sec)

That second row is for mysqldump.

But if all your tables are InnoDB, you may use --single-transaction (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html):

" If tables are stored in the InnoDB storage engine, mysqldump provides a way of making an online backup of these (see command below). This backup just needs to acquire a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and lock is released. So if and only if one long updating statement is running when the FLUSH... is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. So if the MySQL server receives only short (in the sense of "short execution time") updating statements, even if there are plenty of them, the initial lock period should not be noticeable."

The following worked OK for me:

mysqldump -uroot -p -P3307 --single-transaction test4 > test4.sql

even after FLUSH TABLES WITH READ LOCK;

So, I can mark this bug report as a feature request for mysqldump or Administrator, if you want. What do you think.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".