Bug #93440 Noop UPDATE query is logged to binlog after read_only flag is set
Submitted: 1 Dec 2018 7:45 Modified: 29 Apr 14:14
Reporter: Artem Danilov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7, 5.7.24, 8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[1 Dec 2018 7:45] Artem Danilov
Description:
For statement base replication the noop UPDATE query can be logged to binlog after super_read_only flag is set. This happens only if flag happens to be set between UPDATE operation and COMMIT.

How to repeat:
my.cnf:
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=STATEMENT

connection1> create table t (i int);
Query OK, 0 rows affected (0.01 sec)

connection1> insert into t values(1);
Query OK, 1 row affected (0.01 sec)

connection1> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |     1141 |              |                  | d0bed710-f52c-11e8-ac69-b47b4b094852:1-5 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

connection2> begin;
Query OK, 0 rows affected (0.00 sec)

connection2> update t set i = 1 where i = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

connection1> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)

connection2> commit;
Query OK, 0 rows affected (0.00 sec)

connection1> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |     1456 |              |                  | d0bed710-f52c-11e8-ac69-b47b4b094852:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+

The generated binlog entry:
# at 1141
#181130 21:55:36 server id 0  end_log_pos 1202  GTID    last_committed=5        sequence_number=6       rbr_only=no
SET @@SESSION.GTID_NEXT= 'd0bed710-f52c-11e8-ac69-b47b4b094852:6'/*!*/;
# at 1202
#181130 21:55:09 server id 0  end_log_pos 1281  Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1543643709/*!*/;
BEGIN
/*!*/;
# at 1281
#181130 21:55:09 server id 0  end_log_pos 1385  Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1543643709/*!*/;
update t set i = 1 where i = 1
/*!*/;
# at 1385
#181130 21:55:36 server id 0  end_log_pos 1456  Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1543643736/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

-----

The test in binlog suite to demonstrate the issue:

--source include/have_log_bin.inc
--disable_warnings
DROP TABLE IF EXISTS t1 ;
--enable_warnings

CREATE TABLE t1 (a INT);
BEGIN;
INSERT INTO t1 VALUES (1);
COMMIT;

--let $initial_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
--echo Start binlog position: $initial_binlog_pos 

--enable_connect_log
connect (con1,localhost,root,,test);
BEGIN;
UPDATE t1 SET a = 1;

connection default;
SET GLOBAL SUPER_READ_ONLY=1;

connection con1;
COMMIT;

connection default;
--let $current_binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
--echo Binlog position after super_read_only is set: $current_binlog_pos 

--let $assert_text= Binlog position is advanced after super_read_only is set
--let $assert_cond= $current_binlog_pos = $initial_binlog_pos
--source include/assert.inc

--echo # CLEANUP
--disable_connect_log
SET GLOBAL READ_ONLY=0;
DROP TABLE t1;

disconnect con1;

Suggested fix:
The ro_check in handler::ha_commit_trans  should check if binlog cache have any data and fail with read only error.
[4 Dec 2018 9:24] Umesh Shastry
Hello Artem,

Thank you for the report and test case.
Verified as described with 5.7.24 build.

regards,
Umesh
[29 Apr 14:14] Margaret Fisher
Posted by developer:
 
Changelog entry added for MySQL 5.7.27 only:

With statement-based replication in use, if super_read_only was set to ON for a server at the point when a no-op transaction was between its UPDATE and COMMIT operations, the transaction was written to the binary log and assigned a GTID. The transaction is now blocked in this situation. From MySQL 8.0, the value of super_read_only cannot be changed while a transaction is in progress.