1# ==== Purpose ==== 2# 3# Check that DMLs are allowed on temporary tables, when server is in read only 4# mode and binary log is enabled with binlog-format being stmt/mixed mode. 5# 6# ==== Implementation ==== 7# 8# Start the server with binary log being enabled. Mark the server as read only. 9# Create a non-SUPER user and let the user to create a temporary table and 10# perform DML operations on that temporary table. DMLs should not be blocked 11# with a 'server read-only mode' error. 12# 13# ==== References ==== 14# 15# Bug#12818255: READ-ONLY OPTION DOES NOT ALLOW INSERTS/UPDATES ON TEMPORARY 16# TABLES 17# Bug#14294223: CHANGES NOT ALLOWED TO TEMPORARY TABLES ON READ-ONLY SERVERS 18############################################################################### 19--source include/have_log_bin.inc 20--source include/have_innodb.inc 21--disable_warnings 22DROP TABLE IF EXISTS t1 ; 23--enable_warnings 24 25--enable_connect_log 26--echo # READ_ONLY does nothing to SUPER users 27--echo # so we use a non-SUPER one: 28CREATE USER test@localhost; 29GRANT CREATE, SELECT, DROP ON *.* TO test@localhost; 30 31connect (con1,localhost,test,,test); 32 33connection default; 34SET GLOBAL READ_ONLY=1; 35 36connection con1; 37CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB; 38 39--echo # Test INSERTS with autocommit being off and on. 40BEGIN; 41INSERT INTO t1 VALUES (10); 42COMMIT; 43INSERT INTO t1 VALUES (20); 44 45--echo # Test UPDATES with autocommit being off and on. 46BEGIN; 47UPDATE t1 SET a=30 WHERE a=10; 48COMMIT; 49UPDATE t1 SET a=40 WHERE a=20; 50 51connection default; 52SET GLOBAL READ_ONLY=0; 53 54--echo # Test scenario where global read_only is enabled in the middle of transaction. 55--echo # Test INSERT operations on temporary tables, INSERTs should be successful even 56--echo # when global read_only is enabled. 57connection con1; 58BEGIN; 59INSERT INTO t1 VALUES(50); 60 61connection default; 62SET GLOBAL READ_ONLY=1; 63 64connection con1; 65SELECT @@GLOBAL.READ_ONLY; 66COMMIT; 67 68connection default; 69SET GLOBAL READ_ONLY=0; 70 71--echo # Test UPDATE operations on temporary tables, UPDATEs should be successful even 72--echo # when global read_only is enabled. 73connection con1; 74BEGIN; 75UPDATE t1 SET a=60 WHERE a=50; 76 77connection default; 78SET GLOBAL READ_ONLY=1; 79 80connection con1; 81SELECT @@GLOBAL.READ_ONLY; 82COMMIT; 83 84SELECT * FROM t1; 85 86--echo # Clean up 87connection default; 88SET GLOBAL READ_ONLY=0; 89 90disconnect con1; 91DROP USER test@localhost; 92--disable_connect_log 93