MySQL Bugs: #22009: Can write to a read-only server under some circumstances

Bug #22009	Can write to a read-only server under some circumstances
Submitted:	5 Sep 2006 9:24	Modified:	18 Jan 2007 18:04
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.24	OS:	Max OS X
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
When doing a controlled failover it's nice to be able to block writes on the master and promote a slave, existing connections on the old master needs to be handled somehow, it can be kill or something else.
If you try to do this by setting the master to read only it actually still lets connections that queued their lock before the read_only is set to write once the lock is released.

How to repeat:
client1> # City is MyISAM
client1> LOCK TABLE City READ;
Query OK, 0 rows affected (0.00 sec)

client2> DELETE FROM City WHERE Id = 2034;
#Client 2 hangs here.

client1> SET GLOBAL READ_ONLY = 1;
Query OK, 0 rows affected (0.00 sec)

client1> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

#client 2 executes - despite the server running in read-only mode.
Query OK, 1 row affected (13.31 sec)

client2> SHOW GRANTS;  # (Just showing there's no SUPER privilege here)
+---------------------------------------------------------------------------------------------------------------+
| Grants for world2@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'world2'@'localhost' IDENTIFIED BY PASSWORD '*7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C' | 
| GRANT SELECT, EXECUTE ON `world`.* TO 'world2'@'localhost'                                                    | 
| GRANT ALL PRIVILEGES ON `test`.`data` TO 'world2'@'localhost'                                                 | 
| GRANT UPDATE (Population) ON `world`.`country` TO 'world2'@'localhost'                                        | 
| GRANT INSERT, UPDATE, DELETE ON `world`.`city` TO 'world2'@'localhost'                                        | 
+---------------------------------------------------------------------------------------------------------------+

Suggested fix:
Escalate the exclusive lock by READ_ONLY past normal INSERT/DELETE/UPDATE statements.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please, read carefull about when changes of global variable will take effect at http://dev.mysql.com/doc/refman/5.0/en/set-option.html

Thank you for the report.

Verified as described on Linux using last BK sources.

The issue found is very closely related to the findings of Bug#11733.

Investigating if the fix for 11733 address the use case described here.

See related Bug#11733 and WL#3602

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15174

ChangeSet@1.2344, 2006-11-10 20:17:44-07:00, malff@weblab.(none) +8 -0
  WL#3602 (SET GLOBAL READONLY)
  Bug#11733 (COMMITs should not happen if read-only is set)
  Bug#22009 (Can write to a read-only server under some circumstances)
  
  See the work log for details
  
  The change consist of
  a) acquiring the global read lock in SET GLOBAL READONLY
  b) honoring opt_readonly in ha_commit_trans(),
  c) honoring opt_readonly in mysql_lock_tables().
  
  a) takes care of the server stability,
  b) makes the transactional tables safe (Bug 11733)
  c) makes the non transactional tables safe (Bug 22009)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15175

ChangeSet@1.2344, 2006-11-10 19:53:41-07:00, malff@weblab.(none) +8 -0
  WL#3602 (SET GLOBAL READONLY)
  Bug#11733 (COMMITs should not happen if read-only is set)
  Bug#22009 (Can write to a read-only server under some circumstances)
  
  See the work log for details
  
  The change consist of
  [1] acquiring the global read lock in SET GLOBAL READONLY
  [2] honoring opt_readonly in ha_commit_trans(),
  [3] honoring opt_readonly in mysql_lock_tables().
  
  [1] takes care of the server stability,
  [2] makes the transactional tables safe (Bug 11733)
  [3] makes the non transactional tables safe (Bug 22009)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15596

ChangeSet@1.2344, 2006-11-20 20:40:35-07:00, malff@weblab.(none) +8 -0
  WL#3602 (SET GLOBAL READONLY)
  Bug#11733 (COMMITs should not happen if read-only is set)
  Bug#22009 (Can write to a read-only server under some circumstances)
  
  See the work log for details
  
  The change consist of
  a) acquiring the global read lock in SET GLOBAL READONLY
  b) honoring opt_readonly in ha_commit_trans(),
  c) honoring opt_readonly in mysql_lock_tables().
  
  a) takes care of the server stability,
  b) makes the transactional tables safe (Bug 11733)
  c) makes the non transactional tables safe (Bug 22009)

Noted in 5.1.15 changelog.

Incompatible change: The following conditions apply to
enabling the read_only system variable:

  + If you attempt to enable read_only while you have
    any explicit locks (acquired with LOCK TABLES or
    have a pending transaction, an error will occur.

  + If other clients hold explicit table locks or have
    pending transactions, the attempt to enable
    read_only blocks until the locks are released and 
    the transactions end. While the attempt to enable
    read_only is pending, requests by other clients for 
    table locks or to begin transactions also block
    until read_only has been set.

  + read_only can be enabled while you hold a global
    read lock (acquired with FLUSH TABLES WITH READ
    LOCK) because that does not involve table locks.
    This means that the following sequence of statements
    can be used to place a server in read-only state
    prior to performing a backup:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
UNLOCK TABLES;

Previously, the attempt to enable read_only would return
immediately even if explicit locks or transactions were
pending, so some data changes could occur for statements
executing in the server at the same time.

I am returning the report to Patch Pending status in case the
change will also be made to 5.0. If not, please close the report.

This fix introduce some incompatible changes in the behavior of
SET GLOBAL READ_ONLY, which now can block instead of returning immediately.

As a result, this fix is currently for 5.1 only, and is not planned for 5.0.
Closing the report, please re-open if this needs to be re-evaluated.