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
|
include/only_with_option.inc [GLOBAL.binlog_row_image = 'full']
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
# 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 tables t1, t2 and t3 with no primary keys
[connection master]
SET SESSION sql_generate_invisible_primary_key = ON;
include/disable_binlog.inc
CREATE TABLE t1(f1 INT, f2 INT);
CREATE TABLE t2(f1 INT, f2 INT);
CREATE TABLE t3(f1 INT, f2 INT);
include/restore_binlog.inc
SET SESSION sql_generate_invisible_primary_key = OFF;
##################################################
# 2. Create the same tables on the replica
# Table t1 has the same number of columns in the replica
# Table t2 has less columns in the replica
# Table t3 has more columns in the replica
[connection slave]
CREATE TABLE t1(f1 INT, f2 INT);
CREATE TABLE t2(f1 INT);
CREATE TABLE t3(f1 INT, f2 INT, f3 INT);
##################################################
# 3. Insert some values on the tables
# Check they are replicated correctly
[connection master]
INSERT INTO t1 VALUES (11, 11);
INSERT INTO t1 VALUES (12, 12);
INSERT INTO t2 VALUES (21, 21);
INSERT INTO t2 VALUES (21, 22);
INSERT INTO t2 VALUES (21, 23);
INSERT INTO t3 VALUES (31, 31);
INSERT INTO t3 VALUES (32, 32);
include/sync_slave_sql_with_master.inc
[connection slave]
include/assert.inc [The table t1 contains the 2 inserted rows]
include/assert.inc [The table t2 contains the 3 inserted rows]
include/assert.inc [The table t3 contains the 2 inserted rows]
##################################################
# 4. Update and delete rows in all tables
# Check changes are replicated correctly
[connection master]
UPDATE t1 SET t1.f2 = 13 WHERE t1.f1=12;
DELETE FROM t1 WHERE t1.f1=11;
DELETE FROM t2 WHERE t2.f2=21;
UPDATE t2 SET t2.f2 = 24 WHERE t2.f2=23;
UPDATE t3 SET t3.f2 = 33 WHERE t3.f1=31;
DELETE FROM t3 WHERE t3.f1=32;
include/sync_slave_sql_with_master.inc
[connection slave]
include/assert.inc [The table t1 contains 1 row]
include/assert.inc [The table t1 was updated]
include/assert.inc [The table t2 contains 2 rows with value 21]
include/assert.inc [The table t3 contains 1 row]
include/assert.inc [The table t3 was updated]
[connection master]
UPDATE t2 SET t2.f1 = 22 WHERE t2.f2=22;
include/sync_slave_sql_with_master.inc
include/assert.inc [The table t2 contains 1 rows with value 21]
include/assert.inc [The table t2 contains 1 rows with value 22]
# Phase 2: Tests with a GIPK only on the replica
##################################################
# 5. Enable the generation of GIPKs on replication
[connection slave]
include/stop_slave_sql.inc
CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;
include/start_slave_sql.inc
##################################################
# 6. Create some tables without primary keys on the source
# On the replica, drop a column on t5 and add an extra column on t6
[connection master]
CREATE TABLE t4(f1 INT, f2 INT);
CREATE TABLE t5(f1 INT, f2 INT);
CREATE TABLE t6(f1 INT, f2 INT);
include/sync_slave_sql_with_master.inc
ALTER TABLE t5 DROP COLUMN f2;
ALTER TABLE t6 ADD COLUMN f3 INT AFTER f2;
##################################################
# 7. Insert some values on the tables
# Check they are replicated correctly
[connection master]
INSERT INTO t4 VALUES (41, 41);
INSERT INTO t4 VALUES (42, 42);
INSERT INTO t5 VALUES (51, 51);
INSERT INTO t5 VALUES (51, 52);
INSERT INTO t5 VALUES (51, 53);
INSERT INTO t6 VALUES (61, 61);
INSERT INTO t6 VALUES (62, 62);
include/sync_slave_sql_with_master.inc
[connection slave]
include/assert.inc [The table t4 contains the 2 inserted rows]
include/assert.inc [The table t5 contains the 3 inserted rows]
include/assert.inc [The table t6 contains the 2 inserted rows]
##################################################
# 8. Update and delete rows in all tables
# Check changes are replicated correctly
[connection master]
UPDATE t4 SET t4.f2 = 43 WHERE t4.f1=42;
DELETE FROM t4 WHERE t4.f1=41;
DELETE FROM t5 WHERE t5.f2=51;
UPDATE t5 SET t5.f2 = 54 WHERE t5.f2=53;
UPDATE t6 SET t6.f2 = 63 WHERE t6.f1=61;
DELETE FROM t6 WHERE t6.f1=62;
include/sync_slave_sql_with_master.inc
[connection slave]
include/assert.inc [The table t4 contains 1 row]
include/assert.inc [The table t4 was updated]
include/assert.inc [The table t5 contains 2 rows with value 51]
include/assert.inc [The table t6 contains 1 row]
include/assert.inc [The table t6 was updated]
[connection master]
UPDATE t5 SET t5.f1 = 52 WHERE t5.f2=52;
include/sync_slave_sql_with_master.inc
include/assert.inc [The table t5 contains 1 rows with value 51]
include/assert.inc [The table t5 contains 1 rows with value 52]
##################################################
# 9. Cleanup
include/stop_slave_sql.inc
CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = PRIMARY_KEY_CHECK_VALUE;
include/start_slave_sql.inc
[connection master]
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
DROP TABLE t6;
include/rpl_end.inc
|
