Bug #40473 SET GLOBAL read_only=0|1 performs an unnecessary FLUSH TABLES WITH READ LOCK
Submitted: 2 Nov 2008 10:50 Modified: 12 Jul 2012 14:48
Reporter: Pascal Hofmann Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1.29, 5.1, 6.0 bzr OS:Any
Assigned to:
Triage: Triaged: D4 (Minor)

[2 Nov 2008 10:50] Pascal Hofmann
Description:
MySQL 5.1.29 performs an FLUSH TABLES WITH READ LOCK when executing SET GLOBAL read_only=0|1 - even though the value of read_only does not change.

How to repeat:
Open two connections to mysql.

1: select @@read_only \G
->@@read_only: 0
1: CREATE TABLE IF NOT EXISTS bla (id BIGINT(16), PRIMARY KEY(id));
1: LOCK TABLE bla READ;
2: set global read_only=0;
->Will block until the lock from connection 1 is released.

1: UNLOCK TABLES;
2: set global read_only=1;
1: select @@read_only \G
->@@read_only: 1
1: LOCK TABLE bla READ;
2: set global read_only=1;
->Will block until the lock from connection 1 is released.

1: UNLOCK TABLES;
1: select @@read_only \G
->@@read_only: 1
1: LOCK TABLE bla READ;
2: set global read_only=0;
->Will block until the lock from connection 1 is released.

Suggested fix:
Do not FLUSH TABLES WITH READ LOCK if read_only-new is 0 or read_only-old is 1
[3 Nov 2008 8:40] Sveta Smirnova
Thank you for the report.

Verified as described.
[12 Jul 2012 14:48] Jon Olav Hauglid
Duplicate of Bug#57612