WL#3602: SET GLOBAL READ_ONLY
Affects: Server-5.1 — Status: Complete — Priority: Medium
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)
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.