Debian Codegraph @

mysql-test/suite/binlog/t/binlog_dmls_on_tmp_tables_readonly.test

raw
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 --disable_warnings
21 DROP TABLE IF EXISTS t1 ;
22 --enable_warnings
23
24 --enable_connect_log
25 --echo # READ_ONLY does nothing to SUPER users
26 --echo # so we use a non-SUPER one:
27 CREATE USER test@localhost;
28 GRANT CREATE, SELECT, DROP ON *.* TO test@localhost;
29
30 connect (con1,localhost,test,,test);
31
32 connection default;
33 SET GLOBAL READ_ONLY=1;
34
35 connection con1;
36 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
37
38 --echo # Test INSERTS with autocommit being off and on.
39 BEGIN;
40 INSERT INTO t1 VALUES (10);
41 COMMIT;
42 INSERT INTO t1 VALUES (20);
43
44 --echo # Test UPDATES with autocommit being off and on.
45 BEGIN;
46 UPDATE t1 SET a=30 WHERE a=10;
47 COMMIT;
48 UPDATE t1 SET a=40 WHERE a=20;
49
50 connection default;
51 SET GLOBAL READ_ONLY=0;
52
53 --echo # Test scenario where global read_only is enabled in the middle of transaction.
54 --echo # Test INSERT operations on temporary tables, INSERTs should be successful even
55 --echo # when global read_only is enabled.
56 connection con1;
57 BEGIN;
58 INSERT INTO t1 VALUES(50);
59
60 connection default;
61 SET GLOBAL READ_ONLY=1;
62
63 connection con1;
64 SELECT @@GLOBAL.READ_ONLY;
65 COMMIT;
66
67 connection default;
68 SET GLOBAL READ_ONLY=0;
69
70 --echo # Test UPDATE operations on temporary tables, UPDATEs should be successful even
71 --echo # when global read_only is enabled.
72 connection con1;
73 BEGIN;
74 UPDATE t1 SET a=60 WHERE a=50;
75
76 connection default;
77 SET GLOBAL READ_ONLY=1;
78
79 connection con1;
80 SELECT @@GLOBAL.READ_ONLY;
81 COMMIT;
82
83 SELECT * FROM t1;
84
85 --echo # Clean up
86 connection default;
87 SET GLOBAL READ_ONLY=0;
88
89 disconnect con1;
90 DROP USER test@localhost;
91 --disable_connect_log