Debian Codegraph @

mysql-test/suite/rpl/r/rpl_generated_invisible_primary_key_on_replica.result

raw
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