| 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'; |