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