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