Debian Codegraph @

mysql-test/suite/rpl/include/rpl_implicit_commit_binlog.test

raw
1 ################################################################################
2 # In this test case, we verify if some DDL statements implicitly commit a
3 # transaction and are written directly to the binary log without going
4 # through either the Statement- or Transactional-Cache.
5 #
6 # As any statement that goes through a cache is written to the binary log
7 # wrapped in a BEGIN...COMMIT, we proceed as follows:
8 #
9 # - create a transaction and insert some values into a transactional table.
10 # - execute a DDL statement that is supposed to implicitly commit the previous
11 # transaction.
12 # - Check in the binary log for a COMMIT mark which is supposed to be written
13 # before the DDL statement.
14 # - Check in the binary log if the DDL is not wrapped by a BEGIN..COMMIT.
15 #
16 # For further details, please, read WL#2687 and WL#5072.
17 ################################################################################
18
19 --echo #########################################################################
20 --echo # CONFIGURATION
21 --echo #########################################################################
22 connection master;
23
24 eval CREATE TABLE tt_1 (ddl_case INT, PRIMARY KEY(ddl_case)) ENGINE = $engine;
25 eval CREATE TABLE tt_2 (ddl_case INT, PRIMARY KEY(ddl_case)) ENGINE = $engine;
26 eval CREATE TABLE nt_1 (ddl_case INT, PRIMARY KEY(ddl_case)) ENGINE = MyIsam;
27
28 INSERT INTO tt_1(ddl_case) VALUES(0);
29 INSERT INTO tt_2(ddl_case) VALUES(0);
30
31 --echo #########################################################################
32 --echo # CHECK IMPLICT COMMIT
33 --echo #########################################################################
34 SET AUTOCOMMIT= 0;
35
36 INSERT INTO tt_1(ddl_case) VALUES (43);
37 replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB;
38 eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
39 INSERT INTO tt_1(ddl_case) VALUES (42);
40 DROP FUNCTION myfunc_int;
41 INSERT INTO tt_1(ddl_case) VALUES (41);
42 LOAD INDEX INTO CACHE nt_1 IGNORE LEAVES;
43 INSERT INTO tt_1(ddl_case) VALUES (40);
44 LOAD INDEX INTO CACHE tt_1, tt_2 IGNORE LEAVES;
45 INSERT INTO tt_1(ddl_case) VALUES (39);
46 ANALYZE TABLE nt_1;
47 INSERT INTO tt_1(ddl_case) VALUES (38);
48 CHECK TABLE nt_1;
49 INSERT INTO tt_1(ddl_case) VALUES (37);
50 OPTIMIZE TABLE nt_1;
51 INSERT INTO tt_1(ddl_case) VALUES (36);
52 REPAIR TABLE nt_1;
53 INSERT INTO tt_1(ddl_case) VALUES (35);
54 LOCK TABLES tt_1 WRITE;
55 INSERT INTO tt_1(ddl_case) VALUES (34);
56 UNLOCK TABLES;
57 INSERT INTO tt_1(ddl_case) VALUES (33);
58 CREATE USER 'user'@'localhost';
59 INSERT INTO tt_1(ddl_case) VALUES (32);
60 GRANT ALL ON *.* TO 'user'@'localhost';
61 INSERT INTO tt_1(ddl_case) VALUES (31);
62 SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpass');
63 INSERT INTO tt_1(ddl_case) VALUES (30);
64 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';
65 INSERT INTO tt_1(ddl_case) VALUES (29);
66 RENAME USER 'user'@'localhost' TO 'user_new'@'localhost';
67 INSERT INTO tt_1(ddl_case) VALUES (28);
68 DROP USER 'user_new'@'localhost';
69 INSERT INTO tt_1(ddl_case) VALUES (27);
70 CREATE EVENT evt ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO SELECT * FROM tt_1;
71 INSERT INTO tt_1(ddl_case) VALUES (26);
72 ALTER EVENT evt COMMENT 'evt';
73 INSERT INTO tt_1(ddl_case) VALUES (25);
74 DROP EVENT evt;
75 INSERT INTO tt_1(ddl_case) VALUES (24);
76 CREATE TRIGGER tr AFTER INSERT ON tt_1 FOR EACH ROW UPDATE tt_2 SET ddl_case = ddl_case WHERE ddl_case= NEW.ddl_case;
77 INSERT INTO tt_1(ddl_case) VALUES (23);
78 DROP TRIGGER tr;
79 INSERT INTO tt_1(ddl_case) VALUES (22);
80 CREATE FUNCTION fc () RETURNS VARCHAR(64) RETURN "fc";
81 INSERT INTO tt_1(ddl_case) VALUES (21);
82 ALTER FUNCTION fc COMMENT 'fc';
83 INSERT INTO tt_1(ddl_case) VALUES (20);
84 DROP FUNCTION fc;
85 INSERT INTO tt_1(ddl_case) VALUES (19);
86 CREATE PROCEDURE pc () UPDATE tt_2 SET ddl_case = ddl_case WHERE ddl_case= NEW.ddl_case;
87 INSERT INTO tt_1(ddl_case) VALUES (18);
88 ALTER PROCEDURE pc COMMENT 'pc';
89 INSERT INTO tt_1(ddl_case) VALUES (17);
90 DROP PROCEDURE pc;
91 INSERT INTO tt_1(ddl_case) VALUES (16);
92 CREATE VIEW v AS SELECT * FROM tt_1;
93 INSERT INTO tt_1(ddl_case) VALUES (15);
94 ALTER VIEW v AS SELECT * FROM tt_1;
95 INSERT INTO tt_1(ddl_case) VALUES (14);
96 DROP VIEW v;
97 INSERT INTO tt_1(ddl_case) VALUES (13);
98 CREATE INDEX ix ON tt_1(ddl_case);
99 INSERT INTO tt_1(ddl_case) VALUES (12);
100 DROP INDEX ix ON tt_1;
101 INSERT INTO tt_1(ddl_case) VALUES (11);
102 CREATE TEMPORARY TABLE tt_xx (a int);
103 INSERT INTO tt_1(ddl_case) VALUES (10);
104 ALTER TABLE tt_xx ADD COLUMN (b int);
105 INSERT INTO tt_1(ddl_case) VALUES (9);
106 ALTER TABLE tt_xx RENAME new_tt_xx;
107 INSERT INTO tt_1(ddl_case) VALUES (8);
108 DROP TEMPORARY TABLE IF EXISTS new_tt_xx;
109 INSERT INTO tt_1(ddl_case) VALUES (7);
110 CREATE TABLE tt_xx (a int);
111 INSERT INTO tt_1(ddl_case) VALUES (6);
112 ALTER TABLE tt_xx ADD COLUMN (b int);
113 INSERT INTO tt_1(ddl_case) VALUES (5);
114 RENAME TABLE tt_xx TO new_tt_xx;
115 INSERT INTO tt_1(ddl_case) VALUES (4);
116 TRUNCATE TABLE new_tt_xx;
117 INSERT INTO tt_1(ddl_case) VALUES (3);
118 DROP TABLE IF EXISTS tt_xx, new_tt_xx;
119 INSERT INTO tt_1(ddl_case) VALUES (2);
120 CREATE DATABASE db;
121 INSERT INTO tt_1(ddl_case) VALUES (1);
122 DROP DATABASE IF EXISTS db;
123
124 source include/show_binlog_events.inc;
125
126 SET AUTOCOMMIT= 1;
127
128 --echo ###################################################################################
129 --echo # CHECK CONSISTENCY
130 --echo ###################################################################################
131 --sync_slave_with_master
132
133 --let $diff_tables= master:tt_1,slave:tt_1
134 --source include/diff_tables.inc
135
136 --echo ###################################################################################
137 --echo # CLEAN
138 --echo ###################################################################################
139 connection master;
140
141 DROP TABLE tt_1;
142 DROP TABLE tt_2;
143 DROP TABLE nt_1;
144
145 sync_slave_with_master;