| 1 |
|
| 2 |
############################################################## |
| 3 |
# 1. In the replica enable, in the session and globally: |
| 4 |
# - sql_generate_invisible_primary_key |
| 5 |
include/master-slave.inc |
| 6 |
Warnings: |
| 7 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 8 |
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. |
| 9 |
[connection master] |
| 10 |
[connection slave] |
| 11 |
include/save_sysvars.inc [ "GLOBAL.sql_generate_invisible_primary_key" ] |
| 12 |
SET GLOBAL sql_generate_invisible_primary_key = ON; |
| 13 |
SET SESSION sql_generate_invisible_primary_key = ON; |
| 14 |
|
| 15 |
############################################################## |
| 16 |
# 2. Create a table without a primary key on the source |
| 17 |
# The table should not have a primary key when replicated |
| 18 |
[connection master] |
| 19 |
CREATE TABLE t1(a int); |
| 20 |
[connection slave] |
| 21 |
include/start_slave.inc |
| 22 |
[connection master] |
| 23 |
include/sync_slave_sql_with_master.inc |
| 24 |
include/assert.inc [The table only contains a column] |
| 25 |
include/assert.inc [The table does not contain a generated invisilble primary key] |
| 26 |
|
| 27 |
############################################################## |
| 28 |
# 3. Create a table without a primary key on the replica |
| 29 |
# The table should have a generated primary key |
| 30 |
CREATE TABLE t2(a int); |
| 31 |
SHOW CREATE TABLE t2; |
| 32 |
Table Create Table |
| 33 |
t2 CREATE TABLE `t2` ( |
| 34 |
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, |
| 35 |
`a` int DEFAULT NULL, |
| 36 |
PRIMARY KEY (`my_row_id`) |
| 37 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 38 |
include/assert.inc [The table contains 2 columns] |
| 39 |
include/assert.inc [The table contains a generated invisilble primary key] |
| 40 |
|
| 41 |
############################################################## |
| 42 |
# 4. Enable the creation of GIPKs in the replica by setting |
| 43 |
# REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE |
| 44 |
include/stop_slave_sql.inc |
| 45 |
CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE; |
| 46 |
include/start_slave_sql.inc |
| 47 |
|
| 48 |
############################################################## |
| 49 |
# 5. Create a table without a primary key on the source |
| 50 |
# The table should have a generated primary key on the replica |
| 51 |
# This still applies if sql_generate_invisible_primary_key = OFF |
| 52 |
[connection master] |
| 53 |
CREATE TABLE t3(a int); |
| 54 |
include/sync_slave_sql_with_master.inc |
| 55 |
include/assert.inc [The table only contains an extra column] |
| 56 |
include/assert.inc [The table contains a generated invisilble primary key] |
| 57 |
SET GLOBAL sql_generate_invisible_primary_key = OFF; |
| 58 |
include/restart_slave.inc |
| 59 |
[connection master] |
| 60 |
CREATE TABLE t4(a int); |
| 61 |
include/sync_slave_sql_with_master.inc |
| 62 |
include/assert.inc [The table only contains an extra column] |
| 63 |
include/assert.inc [The table contains a generated invisilble primary key] |
| 64 |
|
| 65 |
############################################################## |
| 66 |
# 6. Test error scenarios where source changes conflict with the replica schema |
| 67 |
# Case 1: A column is renamed to have the same name as the replica GIPK |
| 68 |
# Case 2: A PK is added in the source for a table that as a GIPK in the replica |
| 69 |
[connection master] |
| 70 |
ALTER TABLE t4 RENAME COLUMN a TO my_row_id; |
| 71 |
[connection slave] |
| 72 |
include/wait_for_slave_sql_error.inc [errno=1060] |
| 73 |
[connection master] |
| 74 |
RESET MASTER; |
| 75 |
CREATE TABLE t5(a int); |
| 76 |
ALTER TABLE t5 ADD PRIMARY KEY `k` (a); |
| 77 |
[connection slave] |
| 78 |
include/stop_slave.inc |
| 79 |
RESET MASTER; |
| 80 |
include/rpl_reset_slave.inc |
| 81 |
START REPLICA; |
| 82 |
include/wait_for_slave_sql_error.inc [errno=1068] |
| 83 |
[connection master] |
| 84 |
RESET MASTER; |
| 85 |
CREATE TABLE t6 (my_row_id TEXT); |
| 86 |
[connection slave] |
| 87 |
include/stop_slave.inc |
| 88 |
RESET MASTER; |
| 89 |
include/rpl_reset_slave.inc |
| 90 |
START REPLICA; |
| 91 |
include/wait_for_slave_sql_error.inc [errno=4108] |
| 92 |
|
| 93 |
############################################################## |
| 94 |
# 7. Cleanup |
| 95 |
[connection master] |
| 96 |
RESET MASTER; |
| 97 |
[connection slave] |
| 98 |
include/stop_slave.inc |
| 99 |
RESET MASTER; |
| 100 |
include/rpl_reset_slave.inc |
| 101 |
CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = PRIMARY_KEY_CHECK_VALUE; |
| 102 |
include/start_slave.inc |
| 103 |
include/restore_sysvars.inc |
| 104 |
DROP TABLE t2; |
| 105 |
include/suppress_messages.inc |
| 106 |
# Connection 2 suppresses message <Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Duplicate column name 'my_row_id''>. |
| 107 |
# Connection 2 suppresses message <Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Multiple primary key defined' on query.>. |
| 108 |
# 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.'>. |
| 109 |
# Connection 2 suppresses message <Replica SQL for channel .*: ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.>. |
| 110 |
# Connection 2 suppresses message <Replica SQL for channel '': Error 'Duplicate column name 'my_row_id'' on query.>. |
| 111 |
# Connection 2 suppresses message <Replica: Duplicate column name 'my_row_id' Error_code:>. |
| 112 |
# Connection 2 suppresses message <Replica SQL for channel '': Error 'Multiple primary key defined' on query.>. |
| 113 |
# Connection 2 suppresses message <Replica: Multiple primary key defined Error_code:>. |
| 114 |
# Connection 2 suppresses message <Replica SQL for channel '': Error 'Failed to generate invisible primary key. Column 'my_row_id' already exists.'>. |
| 115 |
# Connection 2 suppresses message <Replica: Failed to generate invisible primary key. Column 'my_row_id' already exists.>. |
| 116 |
[connection master] |
| 117 |
DROP TABLE t1; |
| 118 |
DROP TABLE t3; |
| 119 |
DROP TABLE t4; |
| 120 |
DROP TABLE t5; |
| 121 |
include/disable_binlog.inc |
| 122 |
DROP TABLE t6; |
| 123 |
include/restore_binlog.inc |
| 124 |
include/rpl_end.inc |