Bug #96853 Inconsistent super_read_only status when changing the variable is blocked
Submitted: 12 Sep 2019 15:10 Modified: 19 Sep 2019 10:18
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: super_read_only

[12 Sep 2019 15:10] Przemyslaw Malkowski
Description:
The super_read_only variable new setting state is being visible prematurely when SET GLOBAL command is awaiting. 
Interestingly, the read_only variable works as expected.

How to repeat:
Install simple semi-sync master-slave setup.
Then, set the rpl_semi_sync_master_timeout high enough and block network for replication, so that any write will wait.

master [localhost:19428] {msandbox} (test) > show variables like '%read_only';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

-- block network

-- session 1
master [localhost:19428] {msandbox} (test) > insert into t1 set a=now();
-- waiting
Query OK, 1 row affected (1 min 0.05 sec)

-- session 2
master [localhost:19428] {msandbox} ((none)) > set global super_read_only=1;
Query OK, 0 rows affected (52.94 sec)

-- session 3
master [localhost:19428] {msandbox} ((none)) > show processlist;
+----+----------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------------------+
| Id | User     | Host            | db   | Command     | Time | State                                                         | Info                         |
+----+----------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------------------+
|  7 | msandbox | localhost       | test | Query       |   14 | Waiting for semi-sync ACK from slave                          | insert into t1 set a=now()   |
|  8 | msandbox | localhost       | NULL | Query       |    7 | Waiting for global read lock                                  | set global super_read_only=1 |
|  9 | msandbox | localhost       | NULL | Query       |    0 | starting                                                      | show processlist             |
| 10 | rsandbox | localhost:47498 | NULL | Binlog Dump |  193 | Master has sent all binlog to slave; waiting for more updates | NULL                         |
+----+----------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------------------+
4 rows in set (0.00 sec)

master [localhost:19428] {msandbox} ((none)) > show global variables like '%read_only';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

master [localhost:19428] {msandbox} (test) > insert into t1 set a=now();
Query OK, 1 row affected (30.81 sec)

As seen above, even though the set global super_read_only=1 was waiting on global read lock, the variable was already showing as enabled, even though it wasn't yet active, as the 3rd session could initiate another insert (it did not return an error).
Same test with read_only shows that the variable is still disabled when waiting:

master [localhost:19428] {msandbox} (test) > show processlist;
+----+----------+-----------------+------+-------------+------+---------------------------------------------------------------+----------------------------+
| Id | User     | Host            | db   | Command     | Time | State                                                         | Info                       |
+----+----------+-----------------+------+-------------+------+---------------------------------------------------------------+----------------------------+
|  7 | msandbox | localhost       | test | Query       |   13 | Waiting for semi-sync ACK from slave                          | insert into t1 set a=now() |
|  8 | msandbox | localhost       | NULL | Query       |    7 | Waiting for global read lock                                  | set global read_only=1     |
|  9 | msandbox | localhost       | test | Query       |    0 | starting                                                      | show processlist           |
| 12 | rsandbox | localhost:53198 | NULL | Binlog Dump |  326 | Master has sent all binlog to slave; waiting for more updates | NULL                       |
+----+----------+-----------------+------+-------------+------+---------------------------------------------------------------+----------------------------+
4 rows in set (0.00 sec)

master [localhost:19428] {msandbox} (test) > show global variables like '%read_only';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

Suggested fix:
The super_read_only state should be updated properly and not prematurely as it is now.
[19 Sep 2019 10:18] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report.

regards,
Umesh