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 --source include/have_innodb.inc
21 --disable_warnings
22 DROP 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:
28 CREATE USER test@localhost;
29 GRANT CREATE TEMPORARY TABLES, SELECT, DROP ON *.* TO test@localhost;
30
31 connect (con1,localhost,test,,test);
32
33 connection default;
34 SET GLOBAL READ_ONLY=1;
35
36 connection con1;
37 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
38
39 --echo # Test INSERTS with autocommit being off and on.
40 BEGIN;
41 INSERT INTO t1 VALUES (10);
42 COMMIT;
43 INSERT INTO t1 VALUES (20);
44
45 --echo # Test UPDATES with autocommit being off and on.
46 BEGIN;
47 UPDATE t1 SET a=30 WHERE a=10;
48 COMMIT;
49 UPDATE t1 SET a=40 WHERE a=20;
50
51 connection default;
52 SET 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.
57 connection con1;
58 BEGIN;
59 INSERT INTO t1 VALUES(50);
60
61 connection default;
62 SET GLOBAL READ_ONLY=1;
63
64 connection con1;
65 SELECT @@GLOBAL.READ_ONLY;
66 COMMIT;
67
68 connection default;
69 SET 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.
73 connection con1;
74 BEGIN;
75 UPDATE t1 SET a=60 WHERE a=50;
76
77 connection default;
78 SET GLOBAL READ_ONLY=1;
79
80 connection con1;
81 SELECT @@GLOBAL.READ_ONLY;
82 COMMIT;
83
84 SELECT * FROM t1;
85
86 --echo # Clean up
87 connection default;
88 SET GLOBAL READ_ONLY=0;
89
90 disconnect con1;
91 DROP USER test@localhost;
92 --disable_connect_log