Debian Codegraph @

mysql-test/suite/rpl/t/rpl_generated_invisible_primary_key.test

raw
1 ####################################################################################
2 # WL#13784 - Create auto_increment PK on table creation when no PK is specified #
3 # #
4 # Test cases in this file verifies feature requirements, #
5 # a) In GIPK mode if primary key is generated for a non-PK table then #
6 # query string CREATE TABLE must be re-written to include generated #
7 # primary key definition in the binlog event to replicate table #
8 # correctly. #
9 # #
10 # b) DML operation on table for which primary key is generated at source only, #
11 # should be replicated correctly. #
12 # #
13 # Case 1: Verify replication of a table with generated invisible primary key. #
14 # Source: @sql_generate_invisible_primary_key = ON. #
15 # Create table t1 without explicit primary key and Insert rows. #
16 # Replica: Verify that table t1 has same table structure and column values.#
17 # #
18 # #
19 # Case 2: Verify replication of a table with generated invisible primary key when #
20 # system variable show_gipk_in_create_table_and_information_schema = OFF. #
21 # Source: @sql_generate_invisible_primary_key = ON. #
22 # @show_gipk_in_create_table_and_information_schema = OFF #
23 # Create table t1 without explicit primary key and Insert rows. #
24 # Replica: Verify that table t1 has same table structure and column values.#
25 # show_gipk_in_create_table_and_information_schema should not #
26 # query re-writing to log an event. #
27 # #
28 # #
29 # Case 3: Verify replication of a tables created using stored procedure and #
30 # prepared statement. #
31 # Source: @sql_generate_invisible_primary_key = ON. #
32 # Create table t1 without explicit primary key using stored #
33 # procedure and insert a row. #
34 # Create table t2 without explicit primary key using prepared #
35 # statement and insert a row. #
36 # Verify that table t1 and t2 has generated invisible primary key and same #
37 # column values in source and replica. #
38 # Source: DROP TABLE t1 and t2. #
39 # Create table t1 without explicit primary key using stored #
40 # procedure and insert a row. #
41 # Create table t2 without explicit primary key using prepared #
42 # statement and insert a row. #
43 # Verify that table t1 and t2 has generated invisible primary key and same #
44 # column values in source and replica. #
45 # #
46 # #
47 # Case 4: Verify replication of CREATE TABLE ... SELECT with an explicitly #
48 # specified primary key similar to generated invisible primary key. #
49 # Source: @sql_generate_invisible_primary_key = ON. #
50 # Create table t2 using CREATE TABLE ... SELECT with explicit #
51 # primary key having same attributes as generated invisible primary#
52 # key. #
53 # Replica: Verify that table t2 is created. #
54 # #
55 # #
56 # Case 5: Verify replication of CREATE TABLE ... SELECT using temporary source #
57 # table with GIPK. #
58 # Source: @sql_generate_invisible_primary_key=ON. #
59 # Create temporary table t3 without explicit primary key. #
60 # Create table t4 using CREATE TABLE ... SELECT my_row_id, t3.* #
61 # from t3 with explicit primary key without auto_increment #
62 # attributes #
63 # Replica: Verify that table t4 is replicated and has same column values #
64 # as table in source. #
65 ####################################################################################
66 --source include/not_have_privilege_checks_user.inc
67 --source include/master-slave.inc
68
69 CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format');
70
71 SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key;
72 SET SESSION sql_generate_invisible_primary_key = ON;
73
74 --echo # Case 1: Test case to verify replication of a table with generated
75 --echo # invisible primary key.
76 CREATE TABLE t1(f1 INT, f2 INT);
77 INSERT INTO t1 VALUES (1, 10), (2, 20);
78
79 --echo # Check that t1 exists and has generated invisible primary key definition in
80 --echo # source and replica.
81 --let $rpl_diff_statement= SHOW CREATE TABLE t1;
82 --source include/rpl_diff.inc
83
84 --echo # Check that t1 has same column values in source and replica.
85 --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1
86 --source include/rpl_diff.inc
87
88 --source include/rpl_connection_master.inc
89 DROP TABLE t1;
90 --source include/sync_slave_sql_with_master.inc
91
92
93 --echo # Case 2: Test case to verify replication of a table with generated
94 --echo # invisible primary key when susyem variable
95 --echo # show_gipk_in_create_table_and_information_schema = OFF.
96 --source include/rpl_connection_master.inc
97 SET @saved_session_show_gipk_in_create_table_and_information_schema =
98 @@session.show_gipk_in_create_table_and_information_schema;
99 SET SESSION show_gipk_in_create_table_and_information_schema = OFF;
100
101 CREATE TABLE t1(f1 INT, f2 INT);
102 INSERT INTO t1 VALUES (1, 10), (2, 20);
103
104 --echo # Check that t1 exists and has generated invisible primary key definition in
105 --echo # source and replica.
106 --let $rpl_diff_statement= SHOW CREATE TABLE t1;
107 --source include/rpl_diff.inc
108 --echo # Check that t1 has same column values in source and replica.
109 --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1
110 --source include/rpl_diff.inc
111
112 --source include/rpl_connection_master.inc
113 DROP TABLE t1;
114 SET SESSION show_gipk_in_create_table_and_information_schema =
115 @saved_session_show_gipk_in_create_table_and_information_schema;
116 --source include/sync_slave_sql_with_master.inc
117
118
119 --echo # Case 3: Test case to verify replication of a table created with
120 --echo # generated invisible primary key using stored procedure and
121 --echo # prepared statement.
122 --source include/rpl_connection_master.inc
123 DELIMITER $;
124 CREATE PROCEDURE p1() BEGIN
125 CREATE TABLE t1(f1 INT, f2 INT);
126 INSERT INTO t1 VALUES(1, 3);
127 end
128 $
129 DELIMITER ;$
130 PREPARE stmt1 FROM 'CREATE TABLE t2(f1 INT, f2 INT)';
131
132 CALL p1();
133 EXECUTE stmt1;
134 INSERT INTO t2 VALUES(8, 4);
135
136 --echo # Check that t1 exists and has generated invisible primary key definition
137 --echo # in source and replica.
138 --let $rpl_diff_statement= SHOW CREATE TABLE t1;
139 --source include/rpl_diff.inc
140 --echo # Check that t1 hase same column values in source and replica.
141 --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1
142 --source include/rpl_diff.inc
143
144 --echo # Check that t2 exists and has generated invisible primary key definition
145 --echo # in source and replica.
146 --let $rpl_diff_statement= SHOW CREATE TABLE t2;
147 --source include/rpl_diff.inc
148 --echo # Check that t2 hase same column values in source and replica.
149 --let $rpl_diff_statement= SELECT my_row_id, t2.* FROM t2
150 --source include/rpl_diff.inc
151
152 --source include/rpl_connection_master.inc
153 DROP TABLE t1, t2;
154 --source include/sync_slave_sql_with_master.inc
155 --source include/rpl_connection_master.inc
156 CALL p1();
157 EXECUTE stmt1;
158 INSERT INTO t2 VALUES(8, 4);
159
160 --echo # Check that t1 exists and has generated invisible primary key definition
161 --echo # in source and replica.
162 --let $rpl_diff_statement= SHOW CREATE TABLE t1;
163 --source include/rpl_diff.inc
164
165 --echo # Check that t1 hase same column values in source and replica.
166 --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1
167 --source include/rpl_diff.inc
168
169 --echo # Check that t2 exists and has generated invisible primary key definition
170 --echo # in source and replica.
171 --let $rpl_diff_statement= SHOW CREATE TABLE t2;
172 --source include/rpl_diff.inc
173
174 --echo # Check that t2 hase same column values in source and replica.
175 --let $rpl_diff_statement= SELECT my_row_id, t2.* FROM t2
176 --source include/rpl_diff.inc
177
178 --source include/rpl_connection_master.inc
179 DROP TABLE t1, t2;
180 DROP PROCEDURE p1;
181 DROP PREPARE stmt1;
182 --source include/sync_slave_sql_with_master.inc
183
184
185 --echo # Case 4: Verify replication of CREATE TABLE ... SELECT with explicitly
186 --echo # specified primary key similar to generated invisible primary key.
187 --echo # Table t2 is created as primary key is explicitly specified.
188 --source include/rpl_connection_master.inc
189 CREATE TABLE t1(f1 INT, f2 INT);
190 INSERT INTO t1 VALUES (1, 10), (2, 20);
191 # Following statement is marked as unsafe statement when BINLOG_FORMAT = STATEMENT.
192 # So when BINLOG_FORMAT = STATEMENT, additional warnings are reported. But no warnings
193 # reported in other BINLOG_FORMAT types. Hence disable printing warnings here so that
194 # test works with all BINLOG_FORMAT types.
195 --disable_warnings
196 CREATE TABLE t2 (my_row_id BIGINT UNSIGNED INVISIBLE AUTO_INCREMENT PRIMARY KEY)
197 AS SELECT t1.my_row_id, t1.* FROM t1;
198 --enable_warnings
199
200 --echo # check that t2 exists in source and replica.
201 --let $rpl_diff_statement= SHOW CREATE TABLE t2;
202 --source include/rpl_diff.inc
203
204
205 --echo # Case 5: Verify replication of CREATE TABLE ... SELECT using temporary source
206 --echo # table with GIPK.
207 CREATE TEMPORARY TABLE t3 (f1 INT);
208 INSERT INTO t3 VALUES (1),(3),(7),(8),(4);
209 # Following statement is marked as unsafe statement when BINLOG_FORMAT = STATEMENT.
210 # So when BINLOG_FORMAT = STATEMENT, additional warnings are reported. But no warnings
211 # reported in other BINLOG_FORMAT types. Hence disable printing warnings here so that
212 # test works with all BINLOG_FORMAT types.
213 --disable_warnings
214 CREATE TABLE t4(my_row_id BIGINT UNSIGNED INVISIBLE AUTO_INCREMENT PRIMARY KEY)
215 AS SELECT my_row_id, f1 FROM t3;
216 --enable_warnings
217
218 --echo # check that t4 exists in source and replica.
219 --source include/sync_slave_sql_with_master.inc
220 --let $rpl_diff_statement= SHOW CREATE TABLE t4
221 --source include/rpl_diff.inc
222 --let $rpl_diff_statement= SELECT * FROM t4;
223 --source include/rpl_diff.inc
224
225 --source include/rpl_connection_master.inc
226 DROP TABLE t1, t2, t3, t4;
227
228 SET SESSION sql_generate_invisible_primary_key =
229 @saved_session_sql_generate_invisible_primary_key;
230 --source include/rpl_end.inc