346 lines
9.9 KiB
Plaintext
346 lines
9.9 KiB
Plaintext
#############################################################
|
|
# Author: Serge Kozlov <skozlov@mysql.com>
|
|
# Date: 07/01/2008
|
|
# Purpose: Testing possible affects of some system dynamic
|
|
# variables to the replication.
|
|
# Scenario for each variable:
|
|
# 1) Set different values for master and slave
|
|
# 2) Create and replicate a data from master to slave
|
|
# 3) Check results on master and slave: changes on slave
|
|
# shouldn't be affected to replicated data.
|
|
#############################################################
|
|
--source include/not_group_replication_plugin.inc
|
|
--source include/have_myisam.inc
|
|
--source include/master-slave.inc
|
|
--echo
|
|
|
|
#
|
|
# AUTO_INCREMENT
|
|
#
|
|
--echo * auto_increment_increment, auto_increment_offset *
|
|
|
|
--connection master
|
|
SET @@global.auto_increment_increment=2;
|
|
SET @@session.auto_increment_increment=2;
|
|
SET @@global.auto_increment_offset=10;
|
|
SET @@session.auto_increment_offset=10;
|
|
|
|
--connection slave
|
|
SET @@global.auto_increment_increment=3;
|
|
SET @@session.auto_increment_increment=3;
|
|
SET @@global.auto_increment_offset=20;
|
|
SET @@session.auto_increment_offset=20;
|
|
|
|
--connection master
|
|
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
|
|
INSERT INTO t1 (b) VALUES ('master');
|
|
INSERT INTO t1 (b) VALUES ('master');
|
|
SELECT * FROM t1 ORDER BY a;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
|
|
INSERT INTO t1 (b) VALUES ('slave');
|
|
INSERT INTO t1 (b) VALUES ('slave');
|
|
INSERT INTO t2 (b) VALUES ('slave');
|
|
INSERT INTO t2 (b) VALUES ('slave');
|
|
SELECT * FROM t1 ORDER BY a;SELECT * FROM t2 ORDER BY a;
|
|
|
|
--connection master
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1,t2;
|
|
--enable_warnings
|
|
SET @@global.auto_increment_increment=1;
|
|
SET @@session.auto_increment_increment=1;
|
|
SET @@global.auto_increment_offset=1;
|
|
SET @@session.auto_increment_offset=1;
|
|
|
|
--connection slave
|
|
SET @@global.auto_increment_increment=1;
|
|
SET @@session.auto_increment_increment=1;
|
|
SET @@global.auto_increment_offset=1;
|
|
SET @@session.auto_increment_offset=1;
|
|
|
|
--connection slave
|
|
SET auto_increment_increment=1;
|
|
SET auto_increment_offset=1;
|
|
--echo
|
|
|
|
#
|
|
# CHARACTER_SET_DATABASE, COLLATION_SERVER
|
|
#
|
|
--echo * character_set_database, collation_server *
|
|
|
|
--echo [On Master]
|
|
--connection master
|
|
SET @restore_master_character_set_database=@@global.character_set_database;
|
|
SET @restore_master_collation_server=@@global.collation_server;
|
|
SET @@global.character_set_database=latin1;
|
|
SET @@session.character_set_database=latin1;
|
|
SET @@global.collation_server=latin1_german1_ci;
|
|
SET @@session.collation_server=latin1_german1_ci;
|
|
|
|
--echo [On Slave]
|
|
--connection slave
|
|
SET @restore_slave_character_set_database=@@global.character_set_database;
|
|
SET @restore_slave_collation_server=@@global.collation_server;
|
|
SET @@global.character_set_database=utf8;
|
|
SET @@session.character_set_database=utf8;
|
|
SET @@global.collation_server=utf8_bin;
|
|
SET @@session.collation_server=utf8_bin;
|
|
|
|
--echo [On Master]
|
|
--connection master
|
|
CREATE SCHEMA s1;
|
|
SHOW CREATE SCHEMA s1;
|
|
CREATE TABLE s1.t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
|
|
SHOW CREATE TABLE s1.t1;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
|
|
--echo [On Slave]
|
|
CREATE SCHEMA s2;
|
|
SHOW CREATE SCHEMA s2;
|
|
CREATE TABLE s1.t2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
|
|
CREATE TABLE s2.t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
|
|
SHOW CREATE TABLE s1.t1;
|
|
SHOW CREATE TABLE s1.t2;
|
|
SHOW CREATE TABLE s2.t1;
|
|
DROP TABLE s2.t1;
|
|
DROP SCHEMA s2;
|
|
|
|
SET @@global.collation_server=latin1_swedish_ci;
|
|
SET @@session.collation_server=latin1_swedish_ci;
|
|
|
|
--echo [On Master]
|
|
--connection master
|
|
SET @@global.collation_server=latin1_swedish_ci;
|
|
SET @@session.collation_server=latin1_swedish_ci;
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS s1.t1,s1.t2;
|
|
DROP SCHEMA s1;
|
|
--enable_warnings
|
|
--echo
|
|
|
|
#
|
|
# DEFAULT_WEEK_FORMAT
|
|
#
|
|
--echo * default_week_format *
|
|
|
|
--connection master
|
|
SET @@global.default_week_format=0;
|
|
SET @@session.default_week_format=0;
|
|
|
|
--connection slave
|
|
SET @@global.default_week_format=1;
|
|
SET @@session.default_week_format=1;
|
|
|
|
--connection master
|
|
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10), c INT) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1, 'master ', WEEK('2008-01-07'));
|
|
SELECT * FROM t1 ORDER BY a;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
INSERT INTO t1 VALUES (2, 'slave ', WEEK('2008-01-07'));
|
|
SELECT * FROM t1 ORDER BY a;
|
|
|
|
--connection master
|
|
DROP TABLE t1;
|
|
|
|
--connection slave
|
|
SET @@global.default_week_format=0;
|
|
SET @@session.default_week_format=0;
|
|
--echo
|
|
|
|
#
|
|
# LOCAL_INFILE
|
|
#
|
|
--echo * local_infile *
|
|
|
|
--connection slave
|
|
SET @old_local_infile= @@global.local_infile;
|
|
SET @@global.local_infile=0;
|
|
|
|
--connection master
|
|
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(20), c CHAR(254)) ENGINE=MyISAM;
|
|
--copy_files_wildcard ./std_data/ $MYSQLTEST_VARDIR/tmp/ words.dat
|
|
--copy_files_wildcard ./std_data/ $MYSQLTEST_VARDIR/tmp/ words2.dat
|
|
--replace_regex /\'.+\'/'FILE'/
|
|
--eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/tmp/words.dat' INTO TABLE t1 (b)
|
|
SELECT COUNT(*) FROM t1;
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--replace_regex /\'.+\'/'FILE2'/
|
|
--error 1148
|
|
--eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/tmp/words2.dat' INTO TABLE t1 (b)
|
|
SELECT COUNT(*) FROM t1;
|
|
|
|
SET @@global.local_infile=1;
|
|
|
|
--connection master
|
|
DROP TABLE t1;
|
|
--echo
|
|
|
|
#
|
|
# MAX_HEAP_TABLE_SIZE
|
|
#
|
|
--echo * max_heap_table_size *
|
|
|
|
--let $heap_size= 16384
|
|
--let $record_size= 512
|
|
--replace_numeric_round 0
|
|
--let $row_count = `SELECT $heap_size / $record_size`
|
|
--inc $row_count
|
|
|
|
--connection slave
|
|
SET @restore_slave_max_heap_table_size=@@global.max_heap_table_size;
|
|
--replace_result $heap_size HEAP_SIZE
|
|
--eval SET @@global.max_heap_table_size=$heap_size
|
|
--replace_result $heap_size HEAP_SIZE
|
|
--eval SET @@session.max_heap_table_size=$heap_size
|
|
|
|
--connection master
|
|
--replace_result $record_size RECORD_SIZE
|
|
--eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), c VARCHAR($record_size)) ENGINE=MEMORY
|
|
--let $counter=$row_count
|
|
--disable_query_log
|
|
while ($counter) {
|
|
--eval INSERT INTO t1 (b,c) VALUES ('master', REPEAT('A', $record_size));
|
|
dec $counter;
|
|
}
|
|
--enable_query_log
|
|
--replace_result $row_count ROW_COUNT
|
|
--eval SELECT COUNT(*)=$row_count FROM t1
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--replace_result $row_count ROW_COUNT
|
|
--eval SELECT COUNT(*)=$row_count FROM t1 WHERE b='master' GROUP BY b ORDER BY b
|
|
# Truncate the table so that the slaves max_heap_table_size value takes effect on
|
|
# the replicted table
|
|
TRUNCATE TABLE t1;
|
|
--let $counter=$row_count
|
|
--disable_query_log
|
|
while ($counter) {
|
|
--error 0,1114
|
|
--eval INSERT INTO t1 (b,c) VALUES ('slave', REPEAT('A', $record_size))
|
|
dec $counter;
|
|
}
|
|
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), c CHAR(254)) ENGINE=MEMORY;
|
|
--let $counter=$row_count
|
|
--disable_query_log
|
|
while ($counter) {
|
|
--error 0,1114
|
|
INSERT INTO t2 (b,c) VALUES ('slave', REPEAT('A', 254));
|
|
dec $counter;
|
|
}
|
|
--enable_query_log
|
|
# We don't know how many memory used and can't check exact values so need to check following
|
|
# conditions
|
|
--replace_result $row_count ROW_COUNT
|
|
--eval SELECT COUNT(*)<$row_count AND COUNT(*)>0 FROM t1 WHERE b='slave' GROUP BY b ORDER BY b
|
|
--replace_result $row_count ROW_COUNT
|
|
--eval SELECT COUNT(*)<$row_count AND COUNT(*)>0 FROM t2 WHERE b='slave' GROUP BY b ORDER BY b
|
|
|
|
--connection master
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1,t2;
|
|
--enable_warnings
|
|
--echo
|
|
|
|
#
|
|
# STORAGE_ENGINE
|
|
#
|
|
--echo * storage_engine *
|
|
|
|
--connection master
|
|
SET @restore_master_storage_engine=@@global.default_storage_engine;
|
|
SET @@global.default_storage_engine=XENGINE;
|
|
SET @@session.default_storage_engine=XENGINE;
|
|
|
|
--connection slave
|
|
SET @restore_slave_storage_engine=@@global.default_storage_engine;
|
|
SET @@global.default_storage_engine=Memory;
|
|
SET @@session.default_storage_engine=Memory;
|
|
|
|
--connection master
|
|
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10));
|
|
CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=XENGINE;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
CREATE TABLE t3 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10));
|
|
|
|
--connection master
|
|
SHOW CREATE TABLE t1;
|
|
SHOW CREATE TABLE t2;
|
|
|
|
--connection slave
|
|
SHOW CREATE TABLE t1;
|
|
SHOW CREATE TABLE t2;
|
|
SHOW CREATE TABLE t3;
|
|
|
|
SET @@global.default_storage_engine=XENGINE;
|
|
SET @@session.default_storage_engine=XENGINE;
|
|
|
|
--connection master
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1,t2,t3;
|
|
--enable_warnings
|
|
--echo
|
|
|
|
#
|
|
# SQL_MODE
|
|
#
|
|
--echo * sql_mode *
|
|
|
|
--connection master
|
|
SET @@global.sql_mode=ANSI;
|
|
SET @@session.sql_mode=ANSI;
|
|
|
|
--connection slave
|
|
SET @@global.sql_mode=TRADITIONAL;
|
|
SET @@session.sql_mode=TRADITIONAL;
|
|
|
|
--connection master
|
|
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10), c DATE);
|
|
INSERT INTO t1 VALUES (1, 'master', '0000-00-00');
|
|
SELECT * FROM t1 ORDER BY a;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--error 1292
|
|
INSERT INTO t1 VALUES (1, 'slave', '0000-00-00');
|
|
SELECT * FROM t1 ORDER BY a;
|
|
SET @@global.sql_mode=DEFAULT;
|
|
SET @@session.sql_mode=DEFAULT;
|
|
|
|
--connection master
|
|
SET @@global.sql_mode=DEFAULT;
|
|
SET @@session.sql_mode=DEFAULT;
|
|
DROP TABLE t1;
|
|
--echo
|
|
|
|
|
|
# Clean up
|
|
--echo *** clean up ***
|
|
--connection master
|
|
SET @@global.character_set_database=@restore_master_character_set_database;
|
|
SET @@global.collation_server=@restore_master_collation_server;
|
|
SET @@global.default_storage_engine=@restore_master_storage_engine;
|
|
--source include/sync_slave_sql_with_master.inc
|
|
SET @@global.character_set_database=@restore_slave_character_set_database;
|
|
SET @@global.collation_server=@restore_slave_collation_server;
|
|
SET @@global.max_heap_table_size=@restore_slave_max_heap_table_size;
|
|
SET @@global.default_storage_engine=@restore_slave_storage_engine;
|
|
SET @@global.local_infile= @old_local_infile;
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/words.dat
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/words2.dat
|
|
|
|
# Put at the end since the test otherwise emptied the table.
|
|
|
|
--echo
|
|
call mtr.add_suppression("The table 't[12]' is full");
|
|
|
|
# End of 5.1 test
|
|
--source include/rpl_end.inc
|
|
|
|
--connection master
|
|
--source suite/xengine/include/check_xengine_log_error.inc
|