MySQL Bugs: #91852: SET READ_ONLY=1 sometimes doesn't block concurrent DDL.

Bug #91852	SET READ_ONLY=1 sometimes doesn't block concurrent DDL.
Submitted:	1 Aug 2018 12:59	Modified:	7 Oct 2019 17:54
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.7.24	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Sometimes SET READ_ONLY=1 doesn't fully block DDL from non-SUPER users which is executed concurrently to it.
As result users might observe SET READ_ONLY=1 succeeding and then concurrent DDL successfully proceeding to
completion after that.

In other words there can be data and metadata changes from non-SUPER user DDL in a short period after SET READ_ONLY=1
succeeds.

The same problem affects SUPER_READ_ONLY as well.

This means that users can't rely in their tools that SET SUPER_/READ_ONLY fully block DDL changes.

See How-to-repeat for one example of such situation.

How to repeat:
CREATE USER test@localhost;
GRANT CREATE, SELECT, DROP ON *.* TO test@localhost;

--echo connect (con1,localhost,test,,test);
connect (con1,localhost,test,,test);

--echo connect (con2,localhost,root,,test);
connect (con2,localhost,root,,test);

--echo connection default;
connection default;

FLUSH TABLES WITH READ LOCK;

connection con1;
--send CREATE TABLE t1 (i INT);

connection con2;
--sleep 1
SET GLOBAL READ_ONLY=1;
# SET READ_ONLY=1 has completed, so it is supposed to be in effect!

connection default;
SHOW PROCESSLIST;
#	Id     User    Host    db      Command Time    State   Info
#	3      root    localhost       test    Query   0       starting        SHOW PROCESSLIST
#	4      test    localhost       test    Query   1       Waiting for global read lock    CREATE TABLE t1 (i INT)
#	5      root    localhost       test    Sleep   0               NULL

# As you see CREATE TABLE has not fully started yet.

--echo # Unblock CREATE TABLE
UNLOCK TABLES;

connection con1;
--echo # CREATE TABLE succeeds even though read only mode was set!
--reap

--echo # Read only really has succeded.
--error 1290
INSERT INTO t1 VALUES (1);

Posted by developer:
 
Note that problem affects 8.0 as well, but for limited set of
DDL statements which do-not updated data-dictionary, Note that
such statements still can be written to binary log so they are
not really read-only:

CREATE USER test@localhost;
GRANT CREATE, SELECT, DROP ON *.* TO test@localhost;

--echo connect (con1,localhost,test,,test);
connect (con1,localhost,test,,test);

--echo connect (con2,localhost,root,,test);
connect (con2,localhost,root,,test);

--echo connection default;
connection default;

FLUSH TABLES WITH READ LOCK;

connection con1;
--send DROP TABLE IF EXISTS no_such_table;

connection con2;
--sleep 1
SET GLOBAL READ_ONLY=1;
# SET READ_ONLY=1 has completed, so it is supposed to be in effect!

connection default;
SHOW PROCESSLIST;
#	Id     User    Host    db      Command Time    State   Info
#	4      event_scheduler localhost       NULL    Daemon  1       Waiting on empty queue  NULL
#	9      root    localhost       test    Query   0       starting        SHOW PROCESSLIST
#	10     test    localhost       test    Query   1       Waiting for global read lock    DROP TABLE IF EXISTS no_such_table
#	11     root    localhost       test    Sleep   0               NULL

--echo # Unblock DROP TABLE
UNLOCK TABLES;

connection con1;
--echo # DROP TABLE succeeds even though read only mode was set!
--reap

--echo # Read only really has succeded.
--error 1290
INSERT INTO t1 VALUES (1);

connection default;

SHOW BINLOG EVENTS;
#Log_name       Pos     Event_type      Server_id       End_log_pos     Info
#binlog.000001  4       Format_desc     1       124     Server ver: 8.0.18-debug, Binlog ver: 4
#binlog.000001  124     Previous_gtids  1       155
#binlog.000001  155     Anonymous_Gtid  1       232     SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
#binlog.000001  232     Query   1       389     use `test`; CREATE USER 'test'@'localhost' IDENTIFIED WITH 'caching_sha2_password' /* xid=45 */
#binlog.000001  389     Anonymous_Gtid  1       466     SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
#binlog.000001  466     Query   1       624     use `test`; GRANT SELECT, CREATE, DROP ON *.* TO 'test'@'localhost' /* xid=46 */
#binlog.000001  624     Anonymous_Gtid  1       701     SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
#binlog.000001  701     Query   1       840     use `test`; DROP TABLE IF EXISTS `no_such_table` /* generated by server */

Posted by developer:
 
Fixed in 5.7.29, 8.0.19.

Under certain conditions, enabling the read_only or super_read_only
system variable did not block concurrent DDL statements executed by
users without the SUPER privilege.