MySQL Bugs: #37141: Doc page does not account for consequences of FLUSH TABLES WITH READ LOCK

Bug #37141	Doc page does not account for consequences of FLUSH TABLES WITH READ LOCK
Submitted:	2 Jun 2008 17:01	Modified:	16 Oct 2008 15:56
Reporter:	Gordon Shannon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.24-rc-community-log	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	flush tables, innodb, mysqldump

Description:
The documentation page 6.2.1 Backup Policy has an inaccurate statement, which can have serious consequences.

<quote>
shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

This is an online, non-blocking backup that does not disturb the reads and writes on the tables.
</quote>

In this example, the server will issue a "FLUSH TABLES WITH READ LOCK", as indicated by section 4.5.4 on Mysqldump:

<quote>
shell> mysqldump --all-databases --single-transaction > all_databases.sql

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 the lock is released. If and only if one long updating statement is running when the FLUSH statement is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free.
</quote>

(I have no idea what is meant by "if and only if one". What if there's 2?)

"may get stalled" Indeed.

I learned this the hard way when my innodb hot backup processes started to cause the database to lock up for hours, apparently because it also does a FLUSH TABLES WITH READ LOCK, and we have some long-running queries.

How to repeat:
Issue a FLUSH TABLES WITH READ LOCK on a server with long-running queries on innodb tables.

Suggested fix:
The sentence "This is an online, non-blocking backup that does not disturb the reads and writes on the tables." should be qualifed to include the situation where you have long-running queries, and the FLUSH TABLES WITH READ LOCK can freeze your db until the queries are finished.

Thank you for the report.

You qoute the manual: "If and only if one long updating statement is running when the FLUSH statement is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. "

You suggest a change: "should be qualifed to include the situation where you have long-running queries, and the FLUSH TABLES WITH READ LOCK can freeze your db until the queries are finished."

But this situation is clearly described in quoted part of the manual.

"if and only if" means "only in 1 case which would be described below"

"may get stalled" means "your db would be freezed"

I don't see any reason to change this. So I close the report as "Not a Bug"

I am saying that the statement "This is an online, non-blocking backup that does not disturb the reads and writes on the tables." on http://dev.mysql.com/doc/refman/5.1/en/backup-policy.html is wrong.  "flush tables with read lock" is a blocking statement.  Because the statement is contradicted on another page, does not mean this page is therefore correct.

If you want to close this, I would like to understand how that statement is correct.

Thank you for the feedback and clarification.

I mixed 2 pages. Verified as "Please fix http://dev.mysql.com/doc/refman/5.1/en/backup-policy.html to reflect issue with FLUSH TABLES WITH READ LOCK"

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.