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
• Status: Duplicate
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.1.29, 5.1, 6.0 bzr
• OS: Any
• CPU Architecture: Any

[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