MySQL Bugs: #60770: Flush table fails with table read lock

Bug #60770	Flush table fails with table read lock
Submitted:	6 Apr 2011 7:54	Modified:	30 Jun 2011 17:42
Reporter:	Kimseong Loh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.5.11, 5.5.10	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	flush table, myisam, read lock, regression

Description:

mysql> USE world;
mysql> LOCK TABLES Country READ;
mysql> FLUSH TABLES Country; 

The flush tables fail, with MyISAM tables.

Based on http://dev.mysql.com/doc/refman/5.5/en/backup-methods.html
the above should work, and it has been working fine with version 5.1

How to repeat:

Using the sample world database in MyISAM tables

mysql> USE world;
mysql> LOCK TABLES Country READ;
mysql> FLUSH TABLES Country;

Indeed, this works in 5.1.56, but does not work in 5.5. 

If the change in behavior is intended, this manual page, http://dev.mysql.com/doc/refman/5.5/en/backup-methods.html, should be changed seriously as suggested popular backup method does not work any more.

Actually our manual, http://dev.mysql.com/doc/refman/5.5/en/flush.html, explains this:

"As of MySQL 5.5.3, FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_list WITH READ LOCK instead."

So, probably http://dev.mysql.com/doc/refman/5.5/en/backup-methods.html should be just clarified for versions 5.5.3+.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated text:

For storage engines that represent each table using its own files,
tables can be backed up by copying those files. For example, MyISAM
tables are stored as files, so it is easy to do a backup by copying 
files (*.frm, *.MYD, and *.MYI files). To get a consistent backup,
stop the server or lock and flush the relevant tables:
      
FLUSH TABLES tbl_list WITH READ LOCK; 

You need only a read lock; this enables other clients to continue to
query the tables while you are making a copy of the files in the
database directory. The flush is needed to ensure that the all active
index pages are written to disk before you start the backup.