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
|
# === Purpose ===
#
# This test validate that replication works when the replica has a GIPK
# and the source does not.
#
# ==== Requirements ====
#
# R1. If the replica contains a GIPK, replication from a source with no GIPK
# should work seamlessly.
#
# === Implementation ====
#
# 1. Enable the generation of GIPKs in the replica
# 2. Create a table with no primary key in the source
# Check it was created with a GIPK in the replica
# 3. Add some rows to the table in the source
# Check they are present in the replica with the expected auto generated values
# 4. Update one of the row in the source and delete another
# Check the changes are correctly propagated
# 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
#
--let $rpl_skip_start_slave = 1
--source include/master-slave.inc
--echo
--echo ##################################################
--echo # 1. Enable the generation of GIPKs in the replica
--source include/rpl_connection_slave.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.inc
--echo
--echo #####################################################
--echo # 2. Create a table with no primary key in the source
--echo # Check it was created with a GIPK in the replica
--source include/rpl_connection_master.inc
CREATE TABLE t1(f1 INT, f2 INT);
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_slave.inc
--let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t1';`
--let $assert_text= The table contains 3 columns
--let $assert_cond= $column_count = 3
--source include/assert.inc
--let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t1' and column_name='my_row_id';`
--let $assert_text= The table contains a generated invisilble primary key
--let $assert_cond= $pk_count = 1
--source include/assert.inc
--echo
--echo #############################################
--echo # 3. Add some rows to the table in the source
--echo # Check they are present in the replica with the expected auto generated values
--let $sysvars_to_save = [ "GLOBAL.replica_parallel_workers" ]
--source include/save_sysvars.inc
# Make the applier deterministic by having only 1 thread
--source include/stop_slave_sql.inc
SET @@GLOBAL.replica_parallel_workers= 1;
--source include/start_slave_sql.inc
--source include/rpl_connection_master.inc
INSERT INTO t1 VALUES (1, 10);
INSERT INTO t1 VALUES (2, 20);
--source include/sync_slave_sql_with_master.inc
--let $row_count= `SELECT COUNT(*) FROM t1 WHERE my_row_id = 1 AND f1 = 1 AND f2 = 10;`
--let $assert_text= The table contains a row with generated key 1
--let $assert_cond= $row_count = 1
--source include/assert.inc
--let $row_count= `SELECT COUNT(*) FROM t1 WHERE my_row_id = 2 AND f1 = 2 AND f2 = 20;`
--let $assert_text= The table contains a row with generated key 2
--let $assert_cond= $row_count = 1
--source include/assert.inc
# Add a row on the replica to make the auto increment value jump to 4
INSERT INTO t1 (my_row_id,f1,f2) VALUES (3, 3, 30);
--source include/rpl_connection_master.inc
INSERT INTO t1 VALUES (4, 40);
--source include/sync_slave_sql_with_master.inc
--let $row_count= `SELECT COUNT(*) FROM t1 WHERE my_row_id = 3 AND f1 = 3 AND f2 = 30;`
--let $assert_text= The table contains a row with generated key 3
--let $assert_cond= $row_count = 1
--source include/assert.inc
--let $row_count= `SELECT COUNT(*) FROM t1 WHERE my_row_id = 4 AND f1 = 4 AND f2 = 40;`
--let $assert_text= The table contains a row with generated key 4
--let $assert_cond= $row_count = 1
--source include/assert.inc
--echo
--echo ###########################################################
--echo # 4. Update one of the row in the source and delete another
--echo # Check the changes are correctly propagated
--source include/rpl_connection_master.inc
UPDATE t1 SET t1.f2 = 60 WHERE t1.f1=2;
DELETE FROM t1 WHERE t1.f1=1;
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_slave.inc
--let $row_count= `SELECT COUNT(*) FROM t1;`
--let $assert_text= The table contains 3 row
--let $assert_cond= $row_count = 3
--source include/assert.inc
--let $row_count= `SELECT COUNT(*) FROM t1 WHERE t1.f2 = 60 ;`
--let $assert_text= The table was updated
--let $assert_cond= $row_count = 1
--source include/assert.inc
--echo
--echo ###########################################################
--echo # 5. Cleanup
--disable_warnings
--source include/restore_sysvars.inc
--enable_warnings
--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;
--source include/rpl_end.inc
|
