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:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.0.12+ OS:Linux (linux)
Assigned to: CPU Architecture:Any

[30 Dec 2005 2:54] ming lu
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
[30 Dec 2005 14:22] Valeriy Kravchuk
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


So, I can mark this bug report as a feature request for mysqldump or Administrator, if you want. What do you think.
[31 Jan 2006 0:00] Bugs System
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".