#At file:///home/ksm/sun/repo/mysql-6.0-rpl-WL3788/
2682 Serge Kozlov 2008-09-01
WL#3788: Test Write TABLE_TYPE, SQL_MODE etc to the binlog.
Testing of the replication if master and slave have different values of
following system dynamic variables:
AUTO_INCREMENT_(OFFSET|INCREMENT)
CHARACTER_SET_DATABASE
COLLATION_SERVER
DEFAULT_WEEK_FORMAT
LOCAL_INFILE
MAX_HEAP_TABLE_SIZE
STORAGE_ENGINE
SQL_MODE
added:
mysql-test/suite/rpl/r/rpl_spec_variables.result
mysql-test/suite/rpl/t/rpl_spec_variables-slave.opt
mysql-test/suite/rpl/t/rpl_spec_variables.test
=== added file 'mysql-test/suite/rpl/r/rpl_spec_variables.result'
--- a/mysql-test/suite/rpl/r/rpl_spec_variables.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_spec_variables.result 2008-09-01 12:10:04 +0000
@@ -0,0 +1,207 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+
+* auto_increment_increment, auto_increment_offset *
+SET @@global.auto_increment_increment=2;
+SET @@session.auto_increment_increment=2;
+SET @@global.auto_increment_offset=10;
+SET @@session.auto_increment_offset=10;
+SET @@global.auto_increment_increment=3;
+SET @@session.auto_increment_increment=3;
+SET @@global.auto_increment_offset=20;
+SET @@session.auto_increment_offset=20;
+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;
+a b
+2 master
+4 master
+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;
+a b
+2 master
+4 master
+7 slave
+10 slave
+SELECT * FROM t2 ORDER BY a;
+a b
+1 slave
+4 slave
+DROP TABLE IF EXISTS t1,t2;
+SET @@global.auto_increment_increment=1;
+SET @@session.auto_increment_increment=1;
+SET @@global.auto_increment_offset=1;
+SET @@session.auto_increment_offset=1;
+SET @@global.auto_increment_increment=1;
+SET @@session.auto_increment_increment=1;
+SET @@global.auto_increment_offset=1;
+SET @@session.auto_increment_offset=1;
+SET auto_increment_increment=1;
+SET auto_increment_offset=1;
+
+* character_set_database, 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;
+SET @@global.character_set_database=utf8;
+SET @@session.character_set_database=utf8;
+SET @@global.collation_server=utf8_bin;
+SET @@session.collation_server=utf8_bin;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) COLLATE latin1_german1_ci DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
+CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) COLLATE latin1_german1_ci DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) COLLATE utf8_bin DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
+SET @@global.collation_server=latin1_swedish_ci;
+SET @@session.collation_server=latin1_swedish_ci;
+SET @@global.collation_server=latin1_swedish_ci;
+SET @@session.collation_server=latin1_swedish_ci;
+DROP TABLE IF EXISTS t1,t2;
+
+* default_week_format *
+SET @@global.default_week_format=0;
+SET @@session.default_week_format=0;
+SET @@global.default_week_format=1;
+SET @@session.default_week_format=1;
+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;
+a b c
+1 master 1
+INSERT INTO t1 VALUES (2, 'slave ', WEEK('2008-01-07'));
+SELECT * FROM t1 ORDER BY a;
+a b c
+1 master 1
+2 slave 2
+DROP TABLE t1;
+SET @@global.default_week_format=0;
+SET @@session.default_week_format=0;
+
+* local_infile *
+SET @@global.local_infile=0;
+CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(20), c CHAR(254)) ENGINE=MyISAM;
+LOAD DATA LOCAL INFILE 'FILE' INTO TABLE t1 (b);
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+70
+LOAD DATA LOCAL INFILE 'FILE2' INTO TABLE t1 (b);
+ERROR 42000: The used command is not allowed with this MySQL version
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+70
+SET @@global.local_infile=1;
+DROP TABLE t1;
+
+* max_heap_table_size *
+SET @@global.max_heap_table_size=16384;
+SET @@session.max_heap_table_size=16384;
+CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), c CHAR(254)) ENGINE=MEMORY;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+2000
+SELECT b,COUNT(*) FROM t1 GROUP BY b ORDER BY b;
+b COUNT(*)
+master 2000
+slave 1740
+SELECT b,COUNT(*) FROM t2 GROUP BY b ORDER BY b;
+b COUNT(*)
+slave 50
+DROP TABLE IF EXISTS t1,t2;
+
+* storage_engine *
+SET @@global.storage_engine=InnoDB;
+SET @@session.storage_engine=InnoDB;
+SET @@global.storage_engine=Memory;
+SET @@session.storage_engine=Memory;
+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=InnoDB;
+CREATE TABLE t3 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10));
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` int(11) NOT NULL,
+ `b` varchar(10) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+SET @@global.storage_engine=InnoDB;
+SET @@session.storage_engine=InnoDB;
+DROP TABLE IF EXISTS t1,t2,t3;
+
+* sql_mode *
+SET @@global.sql_mode=ANSI;
+SET @@session.sql_mode=ANSI;
+SET @@global.sql_mode=TRADITIONAL;
+SET @@session.sql_mode=TRADITIONAL;
+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;
+a b c
+1 master 0000-00-00
+INSERT INTO t1 VALUES (1, 'slave', '0000-00-00');
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'c' at row 1
+SELECT * FROM t1 ORDER BY a;
+a b c
+1 master 0000-00-00
+SET @@global.sql_mode='';
+SET @@session.sql_mode='';
+SET @@global.sql_mode='';
+SET @@session.sql_mode='';
+DROP TABLE t1;
+
+*** clean up ***
=== added file 'mysql-test/suite/rpl/t/rpl_spec_variables-slave.opt'
--- a/mysql-test/suite/rpl/t/rpl_spec_variables-slave.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_spec_variables-slave.opt 2008-09-01 12:10:04 +0000
@@ -0,0 +1 @@
+--innodb
=== added file 'mysql-test/suite/rpl/t/rpl_spec_variables.test'
--- a/mysql-test/suite/rpl/t/rpl_spec_variables.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_spec_variables.test 2008-09-01 12:10:04 +0000
@@ -0,0 +1,284 @@
+#############################################################
+# Author: Serge Kozlov <skozlov@stripped>
+# 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/have_innodb.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;
+
+--sync_slave_with_master
+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 *
+
+--connection master
+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;
+
+--connection slave
+SET @@global.character_set_database=utf8;
+SET @@session.character_set_database=utf8;
+SET @@global.collation_server=utf8_bin;
+SET @@session.collation_server=utf8_bin;
+
+--connection master
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
+SHOW CREATE TABLE t1;
+
+--sync_slave_with_master
+CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+SET @@global.collation_server=latin1_swedish_ci;
+SET @@session.collation_server=latin1_swedish_ci;
+
+--connection master
+SET @@global.collation_server=latin1_swedish_ci;
+SET @@session.collation_server=latin1_swedish_ci;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--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;
+
+--sync_slave_with_master
+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 @@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_file ./std_data/words.dat $MYSQLTEST_VARDIR/tmp/words.dat
+--copy_file ./std_data/words2.dat $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;
+--sync_slave_with_master
+--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 *
+
+--connection slave
+SET @@global.max_heap_table_size=16384;
+SET @@session.max_heap_table_size=16384;
+
+--connection master
+CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), c CHAR(254)) ENGINE=MEMORY;
+let $counter=2000;
+--disable_query_log
+while ($counter) {
+ INSERT INTO t1 (b,c) VALUES ('master', REPEAT('A', 254));
+ dec $counter;
+}
+--enable_query_log
+SELECT COUNT(*) FROM t1;
+
+--sync_slave_with_master
+let $counter=2000;
+--disable_query_log
+while ($counter) {
+ --error 0,1114
+ INSERT INTO t1 (b,c) VALUES ('slave', REPEAT('A', 254));
+ dec $counter;
+}
+CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), c CHAR(254)) ENGINE=MEMORY;
+let $counter=2000;
+--disable_query_log
+while ($counter) {
+ --error 0,1114
+ INSERT INTO t2 (b,c) VALUES ('slave', REPEAT('A', 254));
+ dec $counter;
+}
+--enable_query_log
+SELECT b,COUNT(*) FROM t1 GROUP BY b ORDER BY b;
+SELECT b,COUNT(*) FROM t2 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 @@global.storage_engine=InnoDB;
+SET @@session.storage_engine=InnoDB;
+
+--connection slave
+SET @@global.storage_engine=Memory;
+SET @@session.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=InnoDB;
+
+--sync_slave_with_master
+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.storage_engine=InnoDB;
+SET @@session.storage_engine=InnoDB;
+
+--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;
+
+--sync_slave_with_master
+--error 1292
+INSERT INTO t1 VALUES (1, 'slave', '0000-00-00');
+SELECT * FROM t1 ORDER BY a;
+SET @@global.sql_mode='';
+SET @@session.sql_mode='';
+
+--connection master
+SET @@global.sql_mode='';
+SET @@session.sql_mode='';
+DROP TABLE t1;
+--echo
+
+
+# Clean up
+--echo *** clean up ***
+--connection master
+--sync_slave_with_master
+
+# End of 5.1 test
| Thread |
|---|
| • bzr commit into mysql-6.0 branch (skozlov:2682) WL#3788 | Serge Kozlov | 1 Sep |
