Debian Codegraph @

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

raw
1
2 # 0. Setup replica with 2 sources where the channels have a privilege user
3 include/rpl_init.inc [topology=1->2,3->2]
4 Warnings:
5 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
6 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.
7 Warnings:
8 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
9 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.
10 [connection server_2]
11 GRANT REPLICATION_APPLIER,CREATE,DROP ON *.* TO 'u'@'localhost';
12 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM FOR CHANNEL 'channel_1';
13
14 # 1. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
15 # Create a table on the source (server 1)
16 # The replica applier user lacks privileges for CREATE/ALTER queries
17 # The replica applier fails while replicating.
18 [connection server_1]
19 SET @@global.sql_require_primary_key = 1;
20 CREATE TABLE t1 (a INT PRIMARY KEY);
21 [connection server_2]
22 START REPLICA;
23 include/wait_for_slave_sql_error.inc [errno=1227]
24
25 # 2. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = STREAM.
26 # Give the replica applier user SESSION_VARIABLES_ADMIN privileges
27 # The replica applier can now replicate CREATE/ALTER queries
28 STOP REPLICA;
29 GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'u'@'localhost';
30 START REPLICA;
31 [connection server_1]
32 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
33
34 # 3. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON.
35 # Revoke privileges.
36 # Create a table on the source (server 1) with no primary key
37 # The replica applier fails while replicating due to the lack of PK
38 STOP REPLICA;
39 REVOKE SESSION_VARIABLES_ADMIN ON *.* FROM 'u'@'localhost';
40 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON FOR CHANNEL 'channel_1';
41 [connection server_1]
42 CREATE TABLE t2 (a INT);
43 [connection server_2]
44 START REPLICA;
45 include/wait_for_slave_sql_error.inc [errno=3750 FOR CHANNEL 'channel_1']
46 [connection server_1]
47 DROP TABLE t2;
48 RESET MASTER;
49 [connection server_2]
50 STOP REPLICA;
51 RESET REPLICA;
52 RESET MASTER;
53
54 # 4. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON.
55 # Create a table on the source (server 1) with a primary key
56 # The replica applier is working properly
57 [connection server_1]
58 SET @@global.sql_require_primary_key = 0;
59 CREATE TABLE t3 (a INT PRIMARY KEY);
60 [connection server_2]
61 START REPLICA;
62 [connection server_1]
63 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
64
65 # 5. While REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF.
66 # Create a table on the source (server 1) with a primary key and without
67 # The replica applier applies both
68 STOP REPLICA;
69 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF FOR CHANNEL 'channel_1';
70 START REPLICA;
71 [connection server_1]
72 SET @@global.sql_require_primary_key = 1;
73 CREATE TABLE t4 (a INT PRIMARY KEY);
74 SET @@global.sql_require_primary_key = 0;
75 CREATE TABLE t5 (a INT);
76 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
77 include/assert.inc [The table t5 contains only 1 column (no PK)]
78
79 # 6. Test REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE.
80 # Test also that multiple channels with different behaviors can coexist
81 # Set the strategy for the channel from server 1 to GENERATE
82 # Set the strategy for the channel from server 3 to ON
83 # Add a new table without a primary key on server 1 and 3
84 # The channel from server 1 shall had a GIPK while the applier on the channel from server 3 fails
85 [connection server_2]
86 include/stop_slave.inc [FOR CHANNEL 'channel_1']
87 include/stop_slave.inc [FOR CHANNEL 'channel_3']
88 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE FOR CHANNEL 'channel_1';
89 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON FOR CHANNEL 'channel_3';
90 include/start_slave.inc [FOR CHANNEL 'channel_1']
91 include/start_slave.inc [FOR CHANNEL 'channel_3']
92 [connection server_1]
93 SET @@global.sql_require_primary_key = 0;
94 CREATE TABLE t6 (a INT);
95 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
96 include/assert.inc [The table contains 2 columns]
97 include/assert.inc [The table contains a generated invisilble primary key]
98 [connection server_3]
99 CREATE TABLE t7 (a INT);
100 [connection server_2]
101 include/wait_for_slave_sql_error.inc [errno=3750 FOR CHANNEL 'channel_3']
102
103 # 7. Cleanup
104 [connection server_1]
105 DROP TABLE t1;
106 DROP TABLE t3;
107 DROP TABLE t4;
108 DROP TABLE t5;
109 DROP TABLE t6;
110 SET @@global.sql_require_primary_key = SQL_REQUIRE_PRIMARY_KEY;
111 [connection server_3]
112 DROP TABLE t7;
113 [connection server_2]
114 CALL mtr.add_suppression(".*Access denied; you need .at least one of. the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege.*");
115 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.*");
116 CALL mtr.add_suppression(".*Unable to create or change a table without a primary key*");
117 include/stop_slave.inc [FOR CHANNEL 'channel_1']
118 include/stop_slave.inc [FOR CHANNEL 'channel_3']
119 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = PRIMARY_KEY_CHECK_VALUE FOR CHANNEL 'channel_1';
120 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = PRIMARY_KEY_CHECK_VALUE FOR CHANNEL 'channel_3';
121 GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'u'@'localhost';
122 include/start_slave.inc [FOR CHANNEL 'channel_1']
123 include/start_slave.inc [FOR CHANNEL 'channel_3']
124 [connection server_1]
125 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_1']
126 [connection server_3]
127 include/sync_slave_sql_with_master.inc [FOR CHANNEL 'channel_3']
128 include/rpl_end.inc
129 RESET REPLICA ALL FOR CHANNEL 'channel_1';
130 RESET REPLICA ALL FOR CHANNEL 'channel_3';