# ==== Purpose ==== # # Verify that in statement based replication, no operation update query # is not logged into binlog if the read_only mode is ON. # #==== Requirements ==== # # Ongoing DML transactions that write the binary log cannot be committed # in read-only modes, even when not writing to the database. # # This shall hold in the following cases: # #- super_read_only=1 and user is SUPER #- read_only=1 and user is non-SUPER #- transaction contains no-op UPDATE #- transaction contains SELECT that may use implicit temporary table #- transaction contains a no-op UPDATE and temporary table INSERT # # ==== Implementation ==== # # Test 1 # 1. Create a table 't1' and insert a row in it. # 2. Begin a transaction and execute noop UPDATE query on table 't1'. # 3. Start another connection and set super_read_only mode to ON. # 4. Commit the transaction in step#2 which should fail with ERROR. # 5. Check RO transaction still commits # 6. Check RO txs still can use implicit temporary tables. # Test 2 # 5. Repeat step 2-4 by executing a noop UPDATE and a temporary table # INSERT in the transaction # Test 3 # 6. Repeat Test 1 with non-SUPER user # Test 4 # 7. Repeat Test 2 with non-SUPER user # 8. Clean up. # # ==== References ==== # # Bug #29009092 NOOP UPDATE QUERY IS LOGGED TO BINLOG AFTER READ_ONLY FLAG IS SET # Bug #30810641 CONTRIBUTION BY FACEBOOK: TESTCASE FOR BINLOG WRITES AFTER SUPER_READ_ONLY ... --source include/have_binlog_format_statement.inc SET @saved_value_super= @@GLOBAL.SUPER_READ_ONLY; SET @saved_value= @@GLOBAL.read_only; --source include/count_sessions.inc CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); --source include/save_binlog_position.inc # Test-1: Execute only noop UPDATE in the transaction connect (con1,localhost,root,,test); BEGIN; UPDATE t1 SET a = 1; connection default; SET GLOBAL SUPER_READ_ONLY=1; connection con1; --error ER_OPTION_PREVENTS_STATEMENT COMMIT; --let $assert_text = Binlog position did not advance --let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position --source include/assert.inc --echo check RO transaction still commits BEGIN; SELECT * FROM t1; COMMIT; --echo check RO txs still can use implicit temporary tables BEGIN; SELECT * FROM t1 UNION SELECT * FROM t1; COMMIT; connection default; SET GLOBAL SUPER_READ_ONLY=0; # Test-2: Execute a noop UPDATE and a temporary table INSERT in the transaction connection con1; CREATE TEMPORARY TABLE t2 (a INT ) ENGINE=INNODB; connection default; --source include/save_binlog_position.inc connection con1; BEGIN; UPDATE t1 SET a = 1; INSERT INTO t2 values(10); connection default; SET GLOBAL SUPER_READ_ONLY=1; connection con1; --error ER_OPTION_PREVENTS_STATEMENT COMMIT; --let $assert_text = Binlog position did not advance --let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position --source include/assert.inc DROP TABLE t2; connection default; SET GLOBAL SUPER_READ_ONLY=0; SET GLOBAL READ_ONLY=0; # READ_ONLY does nothing to SUPER users so we setup a non-SUPER user. CREATE USER test@localhost; GRANT CREATE TEMPORARY TABLES, UPDATE, DROP ON *.* TO test@localhost; --source include/save_binlog_position.inc connect (con2,localhost,test,,test); # Test-3: A non-SUPER user execute only noop UPDATE in the transaction. connection con2; BEGIN; UPDATE t1 SET a = 1; connection default; SET GLOBAL READ_ONLY=1; connection con2; --error ER_OPTION_PREVENTS_STATEMENT COMMIT; connection default; --let $assert_text = Binlog position did not advance --let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position --source include/assert.inc SET GLOBAL READ_ONLY=0; # Test-4: A non-SUPER user execute a noop UPDATE and a temporary table # INSERT in the transaction connection con2; CREATE TEMPORARY TABLE t2 (a INT) ENGINE=INNODB; connection default; --source include/save_binlog_position.inc connection con2; BEGIN; UPDATE t1 SET a = 1; INSERT INTO t2 values(10); connection default; SET GLOBAL READ_ONLY=1; connection con2; --error ER_OPTION_PREVENTS_STATEMENT COMMIT; connection default; --let $assert_text = Binlog position did not advance --let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position --source include/assert.inc connection con2; DROP TABLE t2; # Clean up connection default; DROP USER test@localhost; SET GLOBAL SUPER_READ_ONLY= @saved_value_super; SET GLOBAL read_only= @saved_value; DROP TABLE t1; --disconnect con1 --disconnect con2 --source include/wait_until_count_sessions.inc