Debian Codegraph @

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

raw
1 # === Purpose ===
2 #
3 # This test validates that replication between tables that have only GIPKs
4 # on source or replica can also diverge in their functional indexes
5 #
6 # ==== Requirements ====
7 #
8 # R1.The presence of function indexes should not affect the replication of data
9 # even when GIPKs exist in tables and diverge from source to replica
10 #
11 # === Implementation ====
12 #
13 # Phase 1: Tests with a GIPK only on the source
14 #
15 # 1. Enable the generation of GIPKs in the source
16 # With the binary log temporarily disabled, create table t1, t2 and t3
17 # The same tables were created early on the replica with
18 # - t1 doesn't have a functional index in source but it has one in the replica
19 # - t2 has a functional index in the source but not in the replica
20 # - t3 has a functional index on source and replica but also an extra column
21 # 2. Add a row to each table
22 # Check the rows are correctly applied in the replica
23 #
24 # Phase 2: Tests with a GIPK only on the replica
25 #
26 # 3. Enable the generation of GIPKs in the replica
27 # Create tables t4, t5 and t6 on the source
28 # On the replica drop the index on t4 and add a index to t5
29 # On the replica add an index and an extra column to t6
30 # 4. Add a row to each table
31 # Check the rows are correctly applied in the replica
32 #
33 # 5. Cleanup
34 #
35 # === References ===
36 #
37 # Wl#14639: Row-based replication between tables that differ in the existence of a generated invisible primary key
38 # WL#15419: Make the replica_generate_invisible_primary_key option settable per channel
39 #
40
41 --source include/have_binlog_format_row.inc
42 --source include/master-slave.inc
43
44 --echo # Phase 1: Tests with a GIPK only on the source
45
46 --echo
47 --echo ##################################################
48 --echo # 1. Enable the generation of GIPKs in the source
49 --echo # With the binary log temporarily disabled, create table t1, t2 and t3
50 --echo # The same tables were created early on the replica with
51 --echo # - t1 doesn't have a functional index in source but it has one in the replica
52 --echo # - t2 has a functional index in the source but not in the replica
53 --echo # - t3 has a functional index on source and replica but also an extra column
54
55 --source include/rpl_connection_slave.inc
56
57 CREATE TABLE t1 (f1 BIGINT UNSIGNED, f2 TEXT, INDEX ((f1*1)));
58
59 CREATE TABLE t2 (f1 CHAR(25), f2 INT);
60
61 CREATE TABLE t3 (f1 INT, f2 CHAR(25), f3 BIGINT, INDEX ((f1+2)));
62
63 --source include/rpl_connection_master.inc
64
65 # Set at the session level, so no restore needed for MTR
66 SET SESSION sql_generate_invisible_primary_key = ON;
67
68 --source include/disable_binlog.inc
69 CREATE TABLE t1 (f1 BIGINT UNSIGNED, f2 TEXT);
70 CREATE TABLE t2 (f1 CHAR(25), f2 INT, INDEX ((f1+1)));
71 CREATE TABLE t3 (f1 INT, f2 CHAR(25), INDEX ((f1+2)));
72 --source include/restore_binlog.inc
73
74 SET SESSION sql_generate_invisible_primary_key = OFF;
75
76 --echo
77 --echo ##################################################
78 --echo # 2. Add a row to each table
79 --echo # Check the rows are correctly applied in the replica
80
81 INSERT INTO t1 VALUES (1, '1');
82
83 INSERT INTO t2 VALUES ('2', 2);
84
85 INSERT INTO t3 VALUES (3, '3');
86
87 --source include/sync_slave_sql_with_master.inc
88
89 --let $row_count= `SELECT COUNT(*) FROM t1 WHERE f1 = 1 AND f2 = '1';`
90 --let $assert_text= Table t1 contains the inserted values
91 --let $assert_cond= $row_count = 1
92 --source include/assert.inc
93
94 --let $row_count= `SELECT COUNT(*) FROM t2 WHERE f1 = '2' AND f2 = 2;`
95 --let $assert_text= Table t2 contains the inserted values
96 --let $assert_cond= $row_count = 1
97 --source include/assert.inc
98
99 --let $row_count= `SELECT COUNT(*) FROM t3 WHERE f1 = 3 AND f2 = '3';`
100 --let $assert_text= Table t3 contains the inserted values
101 --let $assert_cond= $row_count = 1
102 --source include/assert.inc
103
104 --echo # Phase 2: Tests with a GIPK only on the replica
105
106 --echo
107 --echo ##################################################
108 --echo # 3. Enable the generation of GIPKs in the replica
109 --echo # Create tables t4, t5 and t6 on the source
110 --echo # On the replica drop the index on t4 and add a index to t5
111 --echo # On the replica add an index and an extra column to t6
112
113 --source include/rpl_connection_slave.inc
114
115 --source include/stop_slave_sql.inc
116 # Store the default value
117 --let $_pk_key_check_value = `SELECT require_table_primary_key_check FROM performance_schema.replication_applier_configuration WHERE channel_name=""`
118 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;
119 --source include/start_slave_sql.inc
120
121 --source include/rpl_connection_master.inc
122
123 CREATE TABLE t4 (f1 INT UNSIGNED, INDEX idx1 ((f1*1)));
124
125 CREATE TABLE t5 (f1 VARCHAR(20), f2 TINYINT);
126
127 CREATE TABLE t6 (f1 BIGINT UNSIGNED);
128
129 --source include/sync_slave_sql_with_master.inc
130
131 DROP INDEX `idx1` ON t4;
132
133 ALTER TABLE t5 ADD INDEX ((f2 * 40) DESC);
134
135 ALTER TABLE t6 ADD INDEX ((f1 + 30) DESC);
136
137 ALTER TABLE t6 ADD f3 INT;
138
139 --echo
140 --echo ##################################################
141 --echo # 4. Add a row to each table
142 --echo # Check the rows are correctly applied in the replica
143
144 --source include/rpl_connection_master.inc
145
146 INSERT INTO t4 VALUES (40);
147
148 INSERT INTO t5 VALUES ('text', 5);
149
150 INSERT INTO t6 VALUES (6);
151
152 --source include/sync_slave_sql_with_master.inc
153
154 --let $row_count= `SELECT COUNT(*) FROM t4 WHERE my_row_id = 1 AND f1 = 40;`
155 --let $assert_text= Table t4 contains the inserted values
156 --let $assert_cond= $row_count = 1
157 --source include/assert.inc
158
159 --let $row_count= `SELECT COUNT(*) FROM t5 WHERE my_row_id = 1 AND f1 = 'text' AND f2 = 5;`
160 --let $assert_text= Table t5 contains the inserted values
161 --let $assert_cond= $row_count = 1
162 --source include/assert.inc
163
164 --let $row_count= `SELECT COUNT(*) FROM t6 WHERE my_row_id = 1 AND f1 = 6;`
165 --let $assert_text= Table t6 contains the inserted values
166 --let $assert_cond= $row_count = 1
167 --source include/assert.inc
168
169 --echo
170 --echo ##################################################
171 --echo # 5. Cleanup
172
173 --source include/rpl_connection_slave.inc
174
175 --source include/stop_slave_sql.inc
176 --replace_result $_pk_key_check_value PRIMARY_KEY_CHECK_VALUE
177 --eval CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = $_pk_key_check_value
178 --source include/start_slave_sql.inc
179
180 --source include/rpl_connection_master.inc
181
182 DROP TABLE t1;
183 DROP TABLE t2;
184 DROP TABLE t3;
185 DROP TABLE t4;
186 DROP TABLE t5;
187 DROP TABLE t6;
188
189 --source include/rpl_end.inc