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
|
##############################################################
# 1. In the replica enable, in the session and globally:
# - sql_generate_invisible_primary_key
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]
[connection slave]
include/save_sysvars.inc [ "GLOBAL.sql_generate_invisible_primary_key" ]
SET GLOBAL sql_generate_invisible_primary_key = ON;
SET SESSION sql_generate_invisible_primary_key = ON;
##############################################################
# 2. Create a table without a primary key on the source
# The table should not have a primary key when replicated
[connection master]
CREATE TABLE t1(a int);
[connection slave]
include/start_slave.inc
[connection master]
include/sync_slave_sql_with_master.inc
include/assert.inc [The table only contains a column]
include/assert.inc [The table does not contain a generated invisilble primary key]
##############################################################
# 3. Create a table without a primary key on the replica
# The table should have a generated primary key
CREATE TABLE t2(a int);
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`a` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
include/assert.inc [The table contains 2 columns]
include/assert.inc [The table contains a generated invisilble primary key]
##############################################################
# 4. Enable the creation of GIPKs in the replica by setting
# REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE
include/stop_slave_sql.inc
CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;
include/start_slave_sql.inc
##############################################################
# 5. Create a table without a primary key on the source
# The table should have a generated primary key on the replica
# This still applies if sql_generate_invisible_primary_key = OFF
[connection master]
CREATE TABLE t3(a int);
include/sync_slave_sql_with_master.inc
include/assert.inc [The table only contains an extra column]
include/assert.inc [The table contains a generated invisilble primary key]
SET GLOBAL sql_generate_invisible_primary_key = OFF;
include/restart_slave.inc
[connection master]
CREATE TABLE t4(a int);
include/sync_slave_sql_with_master.inc
include/assert.inc [The table only contains an extra column]
include/assert.inc [The table contains a generated invisilble primary key]
##############################################################
# 6. Test error scenarios where source changes conflict with the replica schema
# Case 1: A column is renamed to have the same name as the replica GIPK
# Case 2: A PK is added in the source for a table that as a GIPK in the replica
[connection master]
ALTER TABLE t4 RENAME COLUMN a TO my_row_id;
[connection slave]
include/wait_for_slave_sql_error.inc [errno=1060]
[connection master]
RESET MASTER;
CREATE TABLE t5(a int);
ALTER TABLE t5 ADD PRIMARY KEY `k` (a);
[connection slave]
include/stop_slave.inc
RESET MASTER;
include/rpl_reset_slave.inc
START REPLICA;
include/wait_for_slave_sql_error.inc [errno=1068]
[connection master]
RESET MASTER;
CREATE TABLE t6 (my_row_id TEXT);
[connection slave]
include/stop_slave.inc
RESET MASTER;
include/rpl_reset_slave.inc
START REPLICA;
include/wait_for_slave_sql_error.inc [errno=4108]
##############################################################
# 7. Cleanup
[connection master]
RESET MASTER;
[connection slave]
include/stop_slave.inc
RESET MASTER;
include/rpl_reset_slave.inc
CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = PRIMARY_KEY_CHECK_VALUE;
include/start_slave.inc
include/restore_sysvars.inc
DROP TABLE t2;
include/suppress_messages.inc
# Connection 2 suppresses message <Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Duplicate column name 'my_row_id''>.
# Connection 2 suppresses message <Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Multiple primary key defined' on query.>.
# Connection 2 suppresses message <Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Failed to generate invisible primary key. Column 'my_row_id' already exists.'>.
# Connection 2 suppresses message <Replica SQL for channel .*: ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.>.
# Connection 2 suppresses message <Replica SQL for channel '': Error 'Duplicate column name 'my_row_id'' on query.>.
# Connection 2 suppresses message <Replica: Duplicate column name 'my_row_id' Error_code:>.
# Connection 2 suppresses message <Replica SQL for channel '': Error 'Multiple primary key defined' on query.>.
# Connection 2 suppresses message <Replica: Multiple primary key defined Error_code:>.
# Connection 2 suppresses message <Replica SQL for channel '': Error 'Failed to generate invisible primary key. Column 'my_row_id' already exists.'>.
# Connection 2 suppresses message <Replica: Failed to generate invisible primary key. Column 'my_row_id' already exists.>.
[connection master]
DROP TABLE t1;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
include/disable_binlog.inc
DROP TABLE t6;
include/restore_binlog.inc
include/rpl_end.inc
|
