391 lines
11 KiB
Plaintext
391 lines
11 KiB
Plaintext
#
|
|
# WL#1697: Multisource replication
|
|
#
|
|
# This is the basic test required in for multisource replication
|
|
# The aim of this file is to test the basic usecases of msr.
|
|
# 0. Create two masters and a slave and setup a multisource replication
|
|
# between them.
|
|
# 1. create a different databases on each master and test if they are replicated
|
|
# to the slave.
|
|
# 2. create a different table on each master and test if they are replicated to
|
|
# the to the slave.
|
|
# 3. Create a table with the same name on both masters and update non conflicting
|
|
# data on that table. Test if the replication is done properly.
|
|
# 4. Check if updates happen on different master such that the resulting
|
|
# data on slave is conflicting, check that one of the channels the slave
|
|
# SQL thread is stopped.
|
|
#
|
|
#
|
|
# Note: Out of convention, server 2 is always made a slave for multisource testing.
|
|
#
|
|
|
|
#Skip on group replication runs
|
|
--source include/not_group_replication_plugin.inc
|
|
# Test requires master-info-repository=TABLE, relay-log-info-repository=TABLE
|
|
--source include/have_slave_repository_type_table.inc
|
|
|
|
--echo #
|
|
--echo # set up masters server_1 and server_3 with server_2 being a slave.
|
|
--echo #.
|
|
--let $rpl_topology= 1->2,3->2
|
|
--let $rpl_multi_source= 1
|
|
--source include/rpl_init.inc
|
|
|
|
--echo #
|
|
--echo # Test case 1: 1.a) create a database and table db1.t1 on server_1
|
|
--echo # and insert values in the table.
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
CREATE DATABASE db1;
|
|
CREATE TABLE db1.t1 ( a int);
|
|
--let $i= 10
|
|
while($i)
|
|
{
|
|
eval INSERT INTO db1.t1 VALUES ($i);
|
|
--dec $i
|
|
}
|
|
SHOW DATABASES;
|
|
|
|
--echo #
|
|
--echo # 1.b create database and table db3.t1 on server_3
|
|
--echo # and insert values in db3.t1
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
CREATE DATABASE db3;
|
|
CREATE TABLE db3.t1 (a int);
|
|
--let $i= 10
|
|
while($i)
|
|
{
|
|
eval INSERT INTO db3.t1 VALUES (3*$i);
|
|
--dec $i
|
|
}
|
|
SHOW DATABASES;
|
|
|
|
--echo #
|
|
--echo # 1.c sync the slave (server_2) with both masters (server_1 and server_3)
|
|
--echo #
|
|
#sync the slave with server_1
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
# sync the slave with server_3
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_3'
|
|
--let $sync_slave_connection=server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo #
|
|
--echo # 1.d Check that slave has replicated from both masters.
|
|
--echo # Slave should have databases db1 and db3 and tables.
|
|
SHOW DATABASES;
|
|
--let $assert_text= Slave should have 10 rows in db1.t1
|
|
--let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM db1.t1`
|
|
--source include/assert.inc
|
|
--let $assert_text= Slave should have 10 rows in db3.t1
|
|
--let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM db3.t1`
|
|
--source include/assert.inc
|
|
|
|
--echo #
|
|
--echo # Stop slave (on server_2) for test case 2.
|
|
--echo #
|
|
--source include/rpl_stop_slaves.inc
|
|
|
|
--echo #
|
|
--echo # Test case 2: 2.a) Test that different tables in the same database
|
|
--echo # are replicated to the slave
|
|
|
|
#create dbcommon.t11 on server_1
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
CREATE DATABASE dbcommon;
|
|
|
|
### Make sure that this is the first create database to reach
|
|
### the slave.
|
|
--let $rpl_channel_name= 'channel_1'
|
|
|
|
--let $rpl_connection_name= server_2
|
|
--source include/rpl_connection.inc
|
|
|
|
--source include/start_slave.inc
|
|
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection=server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--source include/stop_slave.inc
|
|
|
|
--let $rpl_channel_name=
|
|
|
|
#sync the slave with server_1
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
CREATE TABLE dbcommon.t11(a int);
|
|
|
|
--let $i= 10
|
|
while($i)
|
|
{
|
|
eval INSERT INTO dbcommon.t11 values (11*$i);
|
|
--dec $i
|
|
}
|
|
|
|
#create dbcommon.t11 on server_2
|
|
# IF NOT EXISTS added to avoid data conflicts
|
|
#
|
|
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
|
|
CREATE DATABASE IF NOT EXISTS dbcommon;
|
|
CREATE TABLE dbcommon.t33(a int);
|
|
--let $i= 10
|
|
while ($i)
|
|
{
|
|
eval INSERT INTO dbcommon.t33 values(33*$i);
|
|
--dec $i
|
|
}
|
|
--echo #
|
|
--echo # 2.b Start replication from the masters
|
|
--echo #
|
|
--let $rpl_connection_name= server_2
|
|
--source include/rpl_connection.inc
|
|
|
|
--disable_warnings
|
|
--source include/rpl_start_slaves.inc
|
|
--enable_warnings
|
|
|
|
--echo #
|
|
--echo # 2.c sync the slave with both masters
|
|
--echo #
|
|
#sync the slave with server_1
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection=server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
# sync the slave with server_3
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_3'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo #
|
|
--echo # 2.d Now check the data
|
|
--echo #
|
|
SHOW DATABASES;
|
|
USE dbcommon;
|
|
SHOW TABLES;
|
|
--let $assert_text= Slave should have 10 rows in dbcommon.t11
|
|
--let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM dbcommon.t11`
|
|
--source include/assert.inc
|
|
--let $assert_text= Slave should have 10 rows in dbcommon.t33
|
|
--let $assert_cond= `SELECT COUNT(*) = 10 `rows` FROM dbcommon.t33`
|
|
--source include/assert.inc
|
|
|
|
--echo #
|
|
--echo # Test case 3: 3.a) Update nonconflicting data on the same table
|
|
--echo # and see if they are replicated to the slave.
|
|
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
CREATE TABLE dbcommon.t101(a int, PRIMARY KEY (a));
|
|
|
|
### Make sure that this is the first create table to reach
|
|
### the slave.
|
|
|
|
#sync the slave with server_1
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection=server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--let $rpl_channel_name=
|
|
|
|
--echo #
|
|
--echo # Stop slave (on server_2) for test case 3.
|
|
--echo #
|
|
--source include/rpl_stop_slaves.inc
|
|
|
|
#sync the slave with server_1
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $i= 10
|
|
while($i)
|
|
{
|
|
eval INSERT INTO dbcommon.t101 values (101*$i);
|
|
--dec $i
|
|
}
|
|
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
CREATE TABLE IF NOT EXISTS dbcommon.t101(a int, PRIMARY KEY (a));
|
|
--let $i= 10
|
|
while ($i)
|
|
{
|
|
eval INSERT INTO dbcommon.t101 values(33*$i);
|
|
--dec $i
|
|
}
|
|
|
|
--echo #
|
|
--echo # 3.b Start replication from the masters
|
|
--echo #
|
|
|
|
--let $rpl_connection_name= server_2
|
|
--source include/rpl_connection.inc
|
|
|
|
--disable_warnings
|
|
--source include/rpl_start_slaves.inc
|
|
--enable_warnings
|
|
|
|
--echo #
|
|
--echo # 3.c sync slave server with all the masters.
|
|
--echo #
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
# sync the slave with server_3
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_3'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo #
|
|
--echo # check that data has been replicated to the slave.
|
|
--echo #
|
|
USE dbcommon;
|
|
SHOW TABLES;
|
|
--let $assert_text= Slave should have 20 rows in dbcommon.t101
|
|
--let $assert_cond= `SELECT COUNT(*) = 20 `rows` FROM dbcommon.t101`
|
|
--source include/assert.inc
|
|
|
|
|
|
--echo #
|
|
--echo # Test case 4: Update tables on both masters with conflicting data
|
|
--echo # In this case, slave shall stop on a SQL thread.
|
|
|
|
--echo # 4.a) Do an update on server_1 and sync with the slave.
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
INSERT INTO dbcommon.t101 values(1729);
|
|
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo # 4.b) Do a conflicting update on server_3 and check for conflicting error
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
INSERT INTO dbcommon.t101 values (1729);
|
|
|
|
# connection to be shifted to slave
|
|
--let $rpl_connection_name= server_2
|
|
--source include/rpl_connection.inc
|
|
|
|
#suppress warnings and errors due to conflicts in MTS and non MTS mode
|
|
call mtr.add_suppression("Slave SQL.*:.* 'Duplicate entry '1729' for key 'PRIMARY'' on query.*");
|
|
call mtr.add_suppression("Slave SQL.*: Could not execute Write_rows event on table dbcommon.t101; Duplicate entry '1729' for key 'PRIMARY'*");
|
|
call mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
|
|
call mtr.add_suppression("Slave SQL.*:.* Duplicate entry '1729' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY.*");
|
|
|
|
# sync the slave with server_3
|
|
--let $rpl_channel_name= 'channel_3'
|
|
--let $slave_sql_errno= 1062
|
|
--source include/wait_for_slave_sql_error.inc
|
|
|
|
#check if the error is present in pfs table
|
|
let $sss_value= query_get_value(SHOW SLAVE STATUS FOR CHANNEL "channel_3", Last_SQL_Errno, 1);
|
|
# Post Bug#20001173 fix, in STS mode check performance_schema.replication_applier_status_by_worker
|
|
# table for applier status
|
|
let $worker_count= `select @@global.slave_parallel_workers`;
|
|
if ($worker_count == 0)
|
|
{
|
|
let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_worker where channel_name = "channel_3", Last_Error_Number, 1);
|
|
}
|
|
# In MTS mode check performance_schema.replication_applier_status_by_coordinator
|
|
# table for applier status
|
|
if ($worker_count != 0)
|
|
{
|
|
let $ps_value= query_get_value(select Last_Error_Number from performance_schema.replication_applier_status_by_coordinator where channel_name = "channel_3", Last_Error_Number, 1);
|
|
}
|
|
|
|
let $assert_text= Value returned by SSS and PS table for Last_Error_Number should be same.;
|
|
let $assert_cond= "$sss_value" = "$ps_value";
|
|
--source include/assert.inc
|
|
|
|
# since conflict has occurred remove the entry from the slave
|
|
# and start slave again
|
|
DELETE FROM dbcommon.t101 where a=1729;
|
|
|
|
--disable_warnings
|
|
--let $rpl_channel_name= 'channel_3'
|
|
--source include/start_slave.inc
|
|
--enable_warnings
|
|
|
|
#
|
|
# Cleanup; This involves deleting/dropping and manual syncing
|
|
#
|
|
|
|
# sync the channels before anything else, so that previous
|
|
# events from different channels do not have the chance of
|
|
# being overtaken by the next statements and thus cause
|
|
# random failures
|
|
|
|
--echo # channel 1
|
|
--let $rpl_connection_name= server_1
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo # channel 3
|
|
--let $rpl_connection_name= server_3
|
|
--source include/rpl_connection.inc
|
|
|
|
--let $rpl_channel_name= 'channel_3'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
# now do the clean up
|
|
|
|
--echo connection_server_1
|
|
--connection server_1
|
|
DROP DATABASE db1;
|
|
DROP DATABASE dbcommon;
|
|
--let $rpl_channel_name= 'channel_1'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo connection_server_3
|
|
--connection server_3
|
|
DROP DATABASE db3;
|
|
DROP DATABASE IF EXISTS dbcommon;
|
|
--let $rpl_channel_name= 'channel_3'
|
|
--let $sync_slave_connection= server_2
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--let $rpl_skip_sync= 1
|
|
--source include/rpl_end.inc
|