MySQL Bugs: #66533: lock_wait_timeout ignored with system tables and FLUSH TABLES WITH READ LOCK

Bug #66533	lock_wait_timeout ignored with system tables and FLUSH TABLES WITH READ LOCK
Submitted:	24 Aug 2012 17:17	Modified:	9 Nov 2012 18:12
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The manual (http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lock_wait_timeo...) says that lock_wait_timeout applies, among other statements, to FLUSH TABLES WITH READ LOCK. It is indeed so when a table is modified directly, but not with implicit modification.

In the provided test case, a connection attempts to create a user while another connection holds a global lock acquired by FLUSH TABLES WITH READ LOCK. Despite lock_wait_timeout, the CREATE USER statement hangs, seemingly forever.

If instead of CREATE USER I try to insert a record into mysql.user, the timeout works properly. 

lp:~mysql/mysql-server/5.5
revno 3900

lp:~mysql/mysql-server/5.6
revno 3917

How to repeat:
FLUSH TABLES WITH READ LOCK;

--connect (con1,localhost,root,,)

SET lock_wait_timeout = 1;
CREATE USER test;

Thank you for the bug report. Verified with 5.5.28:

macbook-pro:mysql-test openxs$ ./mtr bug66533
Logging: ./mtr  bug66533
120824 20:40:08 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/CTH7zy7xis/ is case insensitive
120824 20:40:09 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.28
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/5.5/mysql-test/var
Checking leftover processes...
 - found old pid 40512 in 'master.pid', killing it...
   process did not exist!
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.5/mysql-test/var'...
Installing system database...
Using server port 62922

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1] Trying to dump core for [mysqltest - pid: 54776, winpid: 54776]
worker[1] Trying to dump core for [mysqld.1 - pid: 54772, winpid: 54772]
main.bug66533                            [ fail ]  timeout after 900 seconds
        Test ended at 2012-08-24 20:55:51

Test case timeout after 900 seconds

== /Users/openxs/dbs/5.5/mysql-test/var/log/bug66533.log == 
FLUSH TABLES WITH READ LOCK;
SET lock_wait_timeout = 1;

 == /Users/openxs/dbs/5.5/mysql-test/var/tmp/analyze-timeout-mysqld.1.err ==
mysqltest: Could not open connection 'default': 2013 Lost connection to MySQL server at 'reading initial communication packet', system error: 0

 - saving '/Users/openxs/dbs/5.5/mysql-test/var/log/main.bug66533/' to '/Users/openxs/dbs/5.5/mysql-test/var/log/main.bug66533/'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 945 seconds executing testcases

Completed: Failed 1/1 tests, 0.00% were successful.

Failing test(s): main.bug66533
...

How do we know this is the intended behavior and not a server bug?

Hi Paul,

>> How do we know this is the intended behavior and not a server bug?

Since you switched the bug to 'Need Feedback', I presume, you expect a reply from me? But I cannot answer that, I don't know where the question comes from. I never said it was intended behavior (why would I file a bug if I thought it was?). If there is a hidden discussion which explains your question, I don't have permissions to see it.

If you created it as a docs bug, my question is, how do we know it's not a server bug instead? Since neither of us knows, this probably should be recategorized under Server:Locking or something so that the developers can take a look and render a judgment (and possibly fix it if they think it's a server bug).

Sorry, I'm a bit confused. It is already in Server: Locking category, and as far as my records (email notifications) show, has always been in Server: Locking, from the time I filed it and till now...

From:             Elena Stepanova
Status:           Open
Severity:         S3 (Non-critical)
Showstopper:      No
Category:         Server: Locking
Version:          5.5, 5.6
Operating system: Any
OS details:       
Affects customer: No
Bug description:  lock_wait_timeout ignored with system tables and FLUSH TABLES WITH READ LOCK

Updated by:           Valeriy Kravchuk
 Reported by:          Elena Stepanova
 Category:             Server: Locking
 Severity:             S3 (Non-critical)
-Status:               Open
+Status:               Verified
 Version:              5.5, 5.6

etc.

Moving back to "Verified", as Server bug now. So devs should provide info.

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.

Added to lock_wait_timeout description:

This timeout does not apply to implicit accesses to system tables in
the mysql database, such as grant tables modified by GRANT or REVOKE
statements or table logging statements. The timeout does apply to
system tables accessed directly, such as with SELECT or UPDATE.