WL#3602: SET GLOBAL READ_ONLY

This Work Log is to track an incompatible change introduced by the fix for:
- BUG#11733 (COMMITs should not happen if read-only is set)
- BUG#22009 (Can write to a read-only server under some circumstances)

Background

The current implementation of :
  SET GLOBAL READ_ONLY=1;
which change the server to a "read-only" mode at run-time, is not safe, and can
cause the following problems with queries already executing in the server:
- a server might still write to files, making it unsafe to backup the file system,
- a server might still write to the binlog, making it difficult to synchonize
a slave with a master.

The possibility of removing this feature has been discussed, but
unfortunately it's used in some scenario where shutting down a server to
restart it in --read-only mode is not an option:
- high availability servers,
- controlled master->slave fail over with replication.

Removing this feature would have a negative impact on MySQL, so the only
option is to actually fix it.

Proposed change

An safe implementation for
  SET GLOBAL READ_ONLY=1;
is available (BUG#11733) and is under review.
so the technical solution is (pending review approval) known.

While this change is safer, however, it introduces behavior differences
compared to the previous implementation, so there is the possibility that
existing applications using this feature might be affected, causing an
incompatibility.

The differences are detailed in the high level specification section.

1) Basic functionality

The following command sets the server to a read/write mode:
SET GLOBAL READ_ONLY=0

The following command sets the server to a read-only mode:
SET GLOBAL READ_ONLY=1

(This is the current behavior, no change)

2) SUPER privilege

Both
SET GLOBAL READ_ONLY=0
SET GLOBAL READ_ONLY=1
require the SUPER privilege to execute.

(This is the current behavior, no change)

3) LOCK TABLES constraints

When the current connection holds locks on tables,
execution of SET GLOBAL READ_ONLY=x must fail.

For example,

a)
LOCK TABLE T1 READ;
SET GLOBAL READ_ONLY=x;
must fail

b)
LOCK TABLE T1 WRITE;
SET GLOBAL READ_ONLY=x;
must fail

This is a behavior change, previously locks on tables did not prevent
seting READ_ONLY.

Rationale:

The implementation of SET GLOBAL READ_ONLY needs to acquire the global read
lock. Keeping locked tables before attempting to acquire the global read
lock will lead to dead-locks, as in the example below:

client1> LOCK TABLE T1 READ;
client2> LOCK TABLE T1 WRITE;
client1> SET GLOBAL READ_ONLY=1;

client1 waits for client2 to get the global read lock,
client2 waits for client1 to get the lock on table T1.

4) Transactional constraints

When the current connection has a pending transaction,
execution of SET GLOBAL READ_ONLY=x must fail.

For example,

a)
BEGIN;
SET GLOBAL READ_ONLY=x;
must fail

This is a behavior change, previously pending transactions did not prevent
seting READ_ONLY.

Rationale:

There is no valid business case to perform these operations (i.e., it makes
no sense), and supporting a pending transaction in the connection that request
to change the read-only mode of the server introduces an unnecessary
constraint to the implementation (read: more bogus code for no reason).
To use SET GLOBAL READ_ONLY, the user/client will have to either commit or
rollback any pending transactions first.

Rationale 2:

A user with SUPER privileges (required to use this command) is expected to
know what he/she is doing, so this is not a practical limitation.

5) FLUSH TABLES WITH READ LOCK

When the current connection has acquired the global read lock,
execution of SET GLOBAL READ_ONLY=x must succeed.

For example,

a)
FLUSH TABLES WITH READ LOCK;
SET GLOBAL READ_ONLY=1;
UNLOCK TABLES;
must succeed.

Note that FLUSH TABLES WITH READ LOCK does not acquire table locks,
so that this is consistent with 3) LOCK TABLES constraints

Rationale:

The sequence:
  FLUSH TABLES WITH READ LOCK;
  SET GLOBAL READ_ONLY=1;
  UNLOCK TABLES;
is the preferred way to stop a server before performing a backup.

6) Timeliness

The response of
SET GLOBAL READ_ONLY=1;
and also of
SET GLOBAL READ_ONLY=0;
might not be immediate, and can take an indefinite time.

When the query returns, the server guarantees that the new read-only status
is in effect for all the clients.

This is a behavior change, since the response is immediate today.

Rationale:

This is why the code is currently broken and the server is unsafe.
It does reply right away, but does not honor the --read-only status
for the queries currently executing in the server.
As noted with the test case in BUG#22009, this can include queries blocked
while waiting for a resource, so it's not a very transient issue but
instead a very serious problem.

The proposed solution will have to acquire the global read lock,
which can take an indefinite time.

7) Replication

The value of the READ_ONLY flag itself is not replicated.
SET GLOBAL READ_ONLY=X executed on a master does not affect a slave
SET GLOBAL READ_ONLY=X can be executed on a slave, independently of the
value of the same flag on a master

Rationale:
READ_ONLY is semantically a property of a server,
not a property of the data replicated.
In case of non trivial replications scenarios, this is the desired result
(1 slave with 2 master for example)

(This is the current behavior, no change)

8) Special cases

Any query performed:
- with the SUPER priviledge
- by a slave thread in case of replication
does not honor the READ_ONLY flag.

Even when a server had been set to read only by executing
SET GLOBAL READ_ONLY=1,
the super user and the replication thread can still perform write operations.

(This is the current behavior, no change)

Changing the READ_ONLY flag :
- acquires the global read lock
- changes the value of opt_readonly
- releases the global read lock

This makes execution of SET GLOBAL READ_ONLY=x safe,
and blocks while the server is performing writes with the threads currently
executing.

Enforcing that no thread writes data when opt_readonly is set is
implemented :
- when locking a table (to protect non transactional engines)
- when performing a commit (to protect transactional engines)