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.
