diff options
| -rw-r--r-- | .bzrignore | 19 | ||||
| -rw-r--r-- | BitKeeper/etc/logging_ok | 1 | ||||
| -rw-r--r-- | myisam/mi_key.c | 6 | ||||
| -rw-r--r-- | mysql-test/include/rpl_stmt_seq.inc | 180 | ||||
| -rw-r--r-- | mysql-test/r/ctype_utf8.result | 11 | ||||
| -rw-r--r-- | mysql-test/r/innodb.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/innodb_cache.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/rpl_ddl.result | 1662 | ||||
| -rw-r--r-- | mysql-test/t/ctype_utf8.test | 14 | ||||
| -rw-r--r-- | mysql-test/t/innodb.test | 1 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ddl.test | 344 | ||||
| -rw-r--r-- | scripts/mysqlhotcopy.sh | 20 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 32 |
13 files changed, 2281 insertions, 21 deletions
diff --git a/.bzrignore b/.bzrignore index b344b511251..ae028ae9426 100644 --- a/.bzrignore +++ b/.bzrignore @@ -102,6 +102,7 @@ Makefile.in Makefile.in' PENDING/* TAGS +VC++Files/client/mysql_amd64.dsp ac_available_languages_fragment acinclude.m4 aclocal.m4 @@ -274,6 +275,8 @@ client/mysqlmanager-pwgen client/mysqlmanagerc client/mysqlshow client/mysqltest +client/mysqltestmanager-pwgen +client/mysqltestmanagerc client/mysys_priv.h client/select_test client/ssl_test @@ -284,10 +287,12 @@ cmd-line-utils/libedit/common.h cmd-line-utils/libedit/makelist comon.h config.cache +config.guess config.h config.h.in config.log config.status +config.sub configure configure.lineno core @@ -355,6 +360,7 @@ innobase/ib_config.h.in innobase/stamp-h1 insert_test install +install-sh isam/isamchk isam/isamlog isam/pack_isam @@ -519,7 +525,9 @@ linked_libmysqldex_sources linked_server_sources linked_tools_sources locked +ltmain.sh man/*.1 +missing mit-pthreads/config.flags mit-pthreads/include/bits mit-pthreads/include/pthread/machdep.h @@ -936,6 +944,7 @@ scripts/mysqld_multi scripts/mysqld_safe scripts/mysqldumpslow scripts/mysqlhotcopy +scripts/mysqlhotcopy.sh.rej scripts/safe_mysqld select_test server-tools/instance-manager/client.c @@ -1100,18 +1109,10 @@ tmp/* tools/my_vsnprintf.c tools/mysqlmanager tools/mysqlmngd +tools/mysqltestmanager tools/mysys_priv.h vi.h vio/test-ssl vio/test-sslclient vio/test-sslserver vio/viotest-ssl -VC++Files/client/mysql_amd64.dsp -client/mysqltestmanager-pwgen -client/mysqltestmanagerc -tools/mysqltestmanager -config.guess -config.sub -install-sh -ltmain.sh -missing diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok index eaf79d29cee..f1e98699ab0 100644 --- a/BitKeeper/etc/logging_ok +++ b/BitKeeper/etc/logging_ok @@ -52,6 +52,7 @@ dlenev@build.mysql.com dlenev@jabberwock.localdomain dlenev@mysql.com ejonore@mc03.ndb.mysql.com +elliot@mysql.com evgen@moonbone.(none) evgen@moonbone.local gbichot@bk-internal.mysql.com diff --git a/myisam/mi_key.c b/myisam/mi_key.c index 9fb673483ea..eec1d710045 100644 --- a/myisam/mi_key.c +++ b/myisam/mi_key.c @@ -107,7 +107,8 @@ uint _mi_make_key(register MI_INFO *info, uint keynr, uchar *key, } if (keyseg->flag & HA_SPACE_PACK) { - end=pos+length; + FIX_LENGTH(cs, pos, length, char_length); + end= pos + char_length; if (type != HA_KEYTYPE_NUM) { while (end > pos && end[-1] == ' ') @@ -118,8 +119,7 @@ uint _mi_make_key(register MI_INFO *info, uint keynr, uchar *key, while (pos < end && pos[0] == ' ') pos++; } - length=(uint) (end-pos); - FIX_LENGTH(cs, pos, length, char_length); + char_length= (uint) (end - pos); store_key_length_inc(key,char_length); memcpy((byte*) key,(byte*) pos,(size_t) char_length); key+=char_length; diff --git a/mysql-test/include/rpl_stmt_seq.inc b/mysql-test/include/rpl_stmt_seq.inc new file mode 100644 index 00000000000..6f207e66156 --- /dev/null +++ b/mysql-test/include/rpl_stmt_seq.inc @@ -0,0 +1,180 @@ +# include/rpl_stmt_seq.inc +# +# Please be very careful when editing this routine, because the handling of +# the $variables is extreme sensitive. +# + +######## The typical test sequence +# 1. INSERT without commit +# check table content of master and slave +# 2. EXECUTE the statement +# check table content of master and slave +# 3. ROLLBACK +# check table content of master and slave +# 4. flush the logs + +let $VERSION=`select version()`; + +--disable_query_log +# SELECT '######## new test sequence ########' as ""; +eval SELECT CONCAT('######## ','$my_stmt',' ########') as ""; +--enable_query_log + + +############################################################### +# Predict the number of the current log +############################################################### +# Disable the logging of the log number computation. +--disable_query_log +# $_log_num_n should contain the number of the current binlog in numeric style. +# If this routine is called for the first time, $_log_num will not initialized +# and contain the value '' instead of '1'. So we will correct it here. +# +eval set @aux= IF('$_log_num_n' = '', '1', '$_log_num_n'); +let $_log_num_n= `SELECT @aux`; +eval set @aux= LPAD('$_log_num_n',6,'0'); +# SELECT @aux AS "@aux is"; +# +# $_log_num_s should contain the number of the current binlog in string style. +let $_log_num_s= `select @aux`; +# eval SELECT '$log_num' ; +--enable_query_log + +############################################################### +# INSERT +############################################################### +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +# Maybe it would be smarter to use a table with autoincrement column. +let $MAX= `SELECT MAX(f1) FROM t1` ; +eval INSERT INTO t1 SET f1= $MAX + 1; +# results before DDL(to be tested) +SELECT MAX(f1) FROM t1; +--replace_result $VERSION VERSION +--replace_column 2 # 5 # +eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; +sync_slave_with_master; + +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +# results before DDL(to be tested) +SELECT MAX(f1) FROM t1; +--replace_result $VERSION VERSION +--replace_column 2 # 5 # +eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; + +############################################################### +# command to be tested +############################################################### +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +eval $my_stmt; +# Devaluate $my_stmt, to detect script bugs +let $my_stmt= ERROR: YOU FORGOT TO FILL IN THE STATEMENT; +# results after DDL(to be tested) +SELECT MAX(f1) FROM t1; +--replace_result $VERSION VERSION +--replace_column 2 # 5 # +eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; +sync_slave_with_master; + +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +# results after DDL(to be tested) +SELECT MAX(f1) FROM t1; +--replace_result $VERSION VERSION +--replace_column 2 # 5 # +eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; + +############################################################### +# ROLLBACK +############################################################### +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +ROLLBACK; +# results after final ROLLBACK +SELECT MAX(f1) FROM t1; +# Try to detect if the DDL command caused that the INSERT is committed +# $MAX holds the highest/last value just before the insert of MAX + 1 +--disable_query_log +eval SELECT CONCAT(CONCAT('TEST-INFO: MASTER: The INSERT is ', + IF(MAX(f1) = $MAX + 1, 'committed', 'not committed')), + IF((MAX(f1) = $MAX + 1) XOR NOT $my_master_commit, + ' (Succeeded)', + ' (Failed)')) AS "" + FROM mysqltest1.t1; +--enable_query_log +--replace_result $VERSION VERSION +--replace_column 2 # 5 # +eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; +sync_slave_with_master; + +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +# results after final ROLLBACK +SELECT MAX(f1) FROM t1; +--disable_query_log +eval SELECT CONCAT(CONCAT('TEST-INFO: SLAVE: The INSERT is ', + IF(MAX(f1) = $MAX + 1, 'committed', 'not committed')), + IF((MAX(f1) = $MAX + 1) XOR NOT $my_slave_commit, + ' (Succeeded)', + ' (Failed)')) AS "" + FROM mysqltest1.t1; +--enable_query_log +--replace_result $VERSION VERSION +--replace_column 2 # 5 # +eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; + +############################################################### +# Manipulate binlog +############################################################### +#let $manipulate= 0; +let $manipulate= 1; +while ($manipulate) +{ +#### Manipulate the binary logs, +# so that the output of SHOW BINLOG EVENTS IN <current log> +# contains only commands of the current test sequence. +# - flush the master and the slave log +# ---> both start to write into new logs with incremented number +# - increment $_log_num_n +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +flush logs; +# sleep 1; +# eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; +sync_slave_with_master; + +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +# the final content of the binary log +flush logs; +# The next sleep is urgent needed. +# Without this sleep the slaves crashes often, when the SHOW BINLOG +# is executed. :-( +# sleep 1; +# eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; +inc $_log_num_n; +let $manipulate= 0; +} + +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 4be9139f20b..95142caf36c 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -891,3 +891,14 @@ string create table t1 (a varchar(255)) default character set utf8; insert into t1 values (1.0); drop table t1; +create table t1 ( +id int not null, +city varchar(20) not null, +key (city(7),id) +) character set=utf8; +insert into t1 values (1,'Durban North'); +insert into t1 values (2,'Durban'); +select * from t1 where city = 'Durban'; +id city +2 Durban +drop table t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 31480e32c16..f206eb585e3 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -909,7 +909,6 @@ set autocommit=0; create table t1 (a int not null) engine= innodb; insert into t1 values(1),(2); truncate table t1; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction commit; truncate table t1; truncate table t1; @@ -1638,14 +1637,16 @@ t2 CREATE TABLE `t2` ( drop table t2, t1; show status like "binlog_cache_use"; Variable_name Value -Binlog_cache_use 153 +Binlog_cache_use 24 +Binlog_cache_use 25 show status like "binlog_cache_disk_use"; Variable_name Value Binlog_cache_disk_use 0 create table t1 (a int) engine=innodb; show status like "binlog_cache_use"; Variable_name Value -Binlog_cache_use 154 +Binlog_cache_use 25 +Binlog_cache_use 26 show status like "binlog_cache_disk_use"; Variable_name Value Binlog_cache_disk_use 1 @@ -1654,7 +1655,8 @@ delete from t1; commit; show status like "binlog_cache_use"; Variable_name Value -Binlog_cache_use 155 +Binlog_cache_use 26 +Binlog_cache_use 27 show status like "binlog_cache_disk_use"; Variable_name Value Binlog_cache_disk_use 1 diff --git a/mysql-test/r/innodb_cache.result b/mysql-test/r/innodb_cache.result index ec43cbe10b2..5e8611655a2 100644 --- a/mysql-test/r/innodb_cache.result +++ b/mysql-test/r/innodb_cache.result @@ -24,7 +24,7 @@ a 3 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 0 +Qcache_queries_in_cache 1 drop table t1; commit; create table t1 (a int not null) engine=innodb; diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result new file mode 100644 index 00000000000..9e3f76fe0bb --- /dev/null +++ b/mysql-test/r/rpl_ddl.result @@ -0,0 +1,1662 @@ +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; +SET AUTOCOMMIT = 1; +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +DROP DATABASE IF EXISTS mysqltest3; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB"; +INSERT INTO mysqltest1.t1 SET f1= 0; +CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE INDEX my_idx6 ON mysqltest1.t6(f1); +CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB"; +INSERT INTO mysqltest1.t7 SET f1= 0; +CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT); +SET AUTOCOMMIT = 0; +use mysqltest1; + +-------- switch to slave -------- +SET AUTOCOMMIT = 0; +use mysqltest1; + +-------- switch to master ------- + +######## COMMIT ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 0 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 +SHOW BINLOG EVENTS IN 'master-bin.000001'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000001 # Start 1 # Server ver: VERSION, Binlog ver: 3 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest1 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest2 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest3 +master-bin.000001 # Query 1 # CREATE DATABASE mysqltest1 +master-bin.000001 # Query 1 # CREATE DATABASE mysqltest2 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t1 SET f1= 0 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE INDEX my_idx6 ON mysqltest1.t6(f1) +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t7 SET f1= 0 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 +SHOW BINLOG EVENTS IN 'slave-bin.000001'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000001 # Start 2 # Server ver: VERSION, Binlog ver: 3 +slave-bin.000001 # Query 2 # use `test`; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest1 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest2 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest3 +slave-bin.000001 # Query 1 # CREATE DATABASE mysqltest1 +slave-bin.000001 # Query 1 # CREATE DATABASE mysqltest2 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t1 SET f1= 0 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE INDEX my_idx6 ON mysqltest1.t6(f1) +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t7 SET f1= 0 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) + +-------- switch to master ------- +COMMIT; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 +SHOW BINLOG EVENTS IN 'master-bin.000001'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000001 # Start 1 # Server ver: VERSION, Binlog ver: 3 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest1 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest2 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest3 +master-bin.000001 # Query 1 # CREATE DATABASE mysqltest1 +master-bin.000001 # Query 1 # CREATE DATABASE mysqltest2 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t1 SET f1= 0 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE INDEX my_idx6 ON mysqltest1.t6(f1) +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t7 SET f1= 0 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) +master-bin.000001 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000001 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 0 + 1 +master-bin.000001 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 +SHOW BINLOG EVENTS IN 'slave-bin.000001'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000001 # Start 2 # Server ver: VERSION, Binlog ver: 3 +slave-bin.000001 # Query 2 # use `test`; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest1 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest2 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest3 +slave-bin.000001 # Query 1 # CREATE DATABASE mysqltest1 +slave-bin.000001 # Query 1 # CREATE DATABASE mysqltest2 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t1 SET f1= 0 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE INDEX my_idx6 ON mysqltest1.t6(f1) +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t7 SET f1= 0 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) +slave-bin.000001 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 0 + 1 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000001'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000001 # Start 1 # Server ver: VERSION, Binlog ver: 3 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest1 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest2 +master-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest3 +master-bin.000001 # Query 1 # CREATE DATABASE mysqltest1 +master-bin.000001 # Query 1 # CREATE DATABASE mysqltest2 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t1 SET f1= 0 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE INDEX my_idx6 ON mysqltest1.t6(f1) +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t7 SET f1= 0 +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB" +master-bin.000001 # Query 1 # use `test`; CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) +master-bin.000001 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000001 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 0 + 1 +master-bin.000001 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000001'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000001 # Start 2 # Server ver: VERSION, Binlog ver: 3 +slave-bin.000001 # Query 2 # use `test`; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest1 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest2 +slave-bin.000001 # Query 1 # DROP DATABASE IF EXISTS mysqltest3 +slave-bin.000001 # Query 1 # CREATE DATABASE mysqltest1 +slave-bin.000001 # Query 1 # CREATE DATABASE mysqltest2 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t1 SET f1= 0 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE INDEX my_idx6 ON mysqltest1.t6(f1) +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; INSERT INTO mysqltest1.t7 SET f1= 0 +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB" +slave-bin.000001 # Query 1 # use `test`; CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) +slave-bin.000001 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 0 + 1 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- + +######## ROLLBACK ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 1 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +2 +SHOW BINLOG EVENTS IN 'master-bin.000002'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 +SHOW BINLOG EVENTS IN 'slave-bin.000002'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 +SHOW BINLOG EVENTS IN 'master-bin.000002'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 +SHOW BINLOG EVENTS IN 'slave-bin.000002'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000002'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000002'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- + +######## SET AUTOCOMMIT=1 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 1 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +2 +SHOW BINLOG EVENTS IN 'master-bin.000003'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 +SHOW BINLOG EVENTS IN 'slave-bin.000003'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +SET AUTOCOMMIT=1; +SELECT MAX(f1) FROM t1; +MAX(f1) +2 +SHOW BINLOG EVENTS IN 'master-bin.000003'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000003 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000003 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 1 + 1 +master-bin.000003 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +2 +SHOW BINLOG EVENTS IN 'slave-bin.000003'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000003 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 1 + 1 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +2 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000003'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000003 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000003 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 1 + 1 +master-bin.000003 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +2 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000003'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000003 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 1 + 1 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SET AUTOCOMMIT=0; + +######## START TRANSACTION ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 2 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +3 +SHOW BINLOG EVENTS IN 'master-bin.000004'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +2 +SHOW BINLOG EVENTS IN 'slave-bin.000004'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +START TRANSACTION; +SELECT MAX(f1) FROM t1; +MAX(f1) +3 +SHOW BINLOG EVENTS IN 'master-bin.000004'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000004 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000004 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 2 + 1 +master-bin.000004 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +3 +SHOW BINLOG EVENTS IN 'slave-bin.000004'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000004 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 2 + 1 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +3 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000004'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000004 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000004 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 2 + 1 +master-bin.000004 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +3 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000004'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000004 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 2 + 1 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- + +######## BEGIN ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 3 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +4 +SHOW BINLOG EVENTS IN 'master-bin.000005'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +3 +SHOW BINLOG EVENTS IN 'slave-bin.000005'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +BEGIN; +SELECT MAX(f1) FROM t1; +MAX(f1) +4 +SHOW BINLOG EVENTS IN 'master-bin.000005'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000005 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000005 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 3 + 1 +master-bin.000005 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +4 +SHOW BINLOG EVENTS IN 'slave-bin.000005'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000005 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 3 + 1 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +4 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000005'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000005 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000005 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 3 + 1 +master-bin.000005 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +4 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000005'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000005 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 3 + 1 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- + +######## DROP TABLE mysqltest1.t2 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 4 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +5 +SHOW BINLOG EVENTS IN 'master-bin.000006'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +4 +SHOW BINLOG EVENTS IN 'slave-bin.000006'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +DROP TABLE mysqltest1.t2; +SELECT MAX(f1) FROM t1; +MAX(f1) +5 +SHOW BINLOG EVENTS IN 'master-bin.000006'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000006 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000006 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 4 + 1 +master-bin.000006 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000006 # Query 1 # use `mysqltest1`; DROP TABLE mysqltest1.t2 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +5 +SHOW BINLOG EVENTS IN 'slave-bin.000006'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000006 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 4 + 1 +slave-bin.000006 # Query 1 # use `mysqltest1`; DROP TABLE mysqltest1.t2 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +5 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000006'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000006 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000006 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 4 + 1 +master-bin.000006 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000006 # Query 1 # use `mysqltest1`; DROP TABLE mysqltest1.t2 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +5 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000006'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000006 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 4 + 1 +slave-bin.000006 # Query 1 # use `mysqltest1`; DROP TABLE mysqltest1.t2 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SHOW TABLES LIKE 't2'; +Tables_in_mysqltest1 (t2) + +-------- switch to slave -------- +SHOW TABLES LIKE 't2'; +Tables_in_mysqltest1 (t2) + +-------- switch to master ------- + +######## DROP TEMPORARY TABLE mysqltest1.t23 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 5 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +6 +SHOW BINLOG EVENTS IN 'master-bin.000007'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +5 +SHOW BINLOG EVENTS IN 'slave-bin.000007'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +DROP TEMPORARY TABLE mysqltest1.t23; +SELECT MAX(f1) FROM t1; +MAX(f1) +6 +SHOW BINLOG EVENTS IN 'master-bin.000007'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +5 +SHOW BINLOG EVENTS IN 'slave-bin.000007'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +SELECT MAX(f1) FROM t1; +MAX(f1) +5 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000007'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000007 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000007 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 5 + 1 +master-bin.000007 # Query 1 # use `mysqltest1`; DROP TEMPORARY TABLE mysqltest1.t23 +master-bin.000007 # Query 1 # use `mysqltest1`; ROLLBACK + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +6 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000007'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000007 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 5 + 1 +slave-bin.000007 # Query 1 # use `mysqltest1`; DROP TEMPORARY TABLE mysqltest1.t23 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SHOW TABLES LIKE 't23'; +Tables_in_mysqltest1 (t23) + +-------- switch to slave -------- +SHOW TABLES LIKE 't23'; +Tables_in_mysqltest1 (t23) + +-------- switch to master ------- + +######## RENAME TABLE mysqltest1.t3 to mysqltest1.t20 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 5 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +6 +SHOW BINLOG EVENTS IN 'master-bin.000008'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +6 +SHOW BINLOG EVENTS IN 'slave-bin.000008'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +RENAME TABLE mysqltest1.t3 to mysqltest1.t20; +SELECT MAX(f1) FROM t1; +MAX(f1) +6 +SHOW BINLOG EVENTS IN 'master-bin.000008'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000008 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000008 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 5 + 1 +master-bin.000008 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000008 # Query 1 # use `mysqltest1`; RENAME TABLE mysqltest1.t3 to mysqltest1.t20 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +6 +SHOW BINLOG EVENTS IN 'slave-bin.000008'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000008 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 5 + 1 +slave-bin.000008 # Query 1 # use `mysqltest1`; RENAME TABLE mysqltest1.t3 to mysqltest1.t20 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +6 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000008'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000008 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000008 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 5 + 1 +master-bin.000008 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000008 # Query 1 # use `mysqltest1`; RENAME TABLE mysqltest1.t3 to mysqltest1.t20 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +6 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000008'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000008 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 5 + 1 +slave-bin.000008 # Query 1 # use `mysqltest1`; RENAME TABLE mysqltest1.t3 to mysqltest1.t20 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SHOW TABLES LIKE 't20'; +Tables_in_mysqltest1 (t20) +t20 + +-------- switch to slave -------- +SHOW TABLES LIKE 't20'; +Tables_in_mysqltest1 (t20) +t20 + +-------- switch to master ------- + +######## ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 6 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +7 +SHOW BINLOG EVENTS IN 'master-bin.000009'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +6 +SHOW BINLOG EVENTS IN 'slave-bin.000009'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT; +SELECT MAX(f1) FROM t1; +MAX(f1) +7 +SHOW BINLOG EVENTS IN 'master-bin.000009'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000009 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000009 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 6 + 1 +master-bin.000009 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000009 # Query 1 # use `mysqltest1`; ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +7 +SHOW BINLOG EVENTS IN 'slave-bin.000009'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000009 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 6 + 1 +slave-bin.000009 # Query 1 # use `mysqltest1`; ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +7 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000009'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000009 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000009 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 6 + 1 +master-bin.000009 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000009 # Query 1 # use `mysqltest1`; ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +7 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000009'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000009 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 6 + 1 +slave-bin.000009 # Query 1 # use `mysqltest1`; ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +describe mysqltest1.t4; +Field Type Null Key Default Extra +f1 bigint(20) YES NULL +f2 bigint(20) YES NULL + +-------- switch to slave -------- +describe mysqltest1.t4; +Field Type Null Key Default Extra +f1 bigint(20) YES NULL +f2 bigint(20) YES NULL + +-------- switch to master ------- + +######## CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB" ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 7 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +8 +SHOW BINLOG EVENTS IN 'master-bin.000010'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +7 +SHOW BINLOG EVENTS IN 'slave-bin.000010'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB"; +SELECT MAX(f1) FROM t1; +MAX(f1) +8 +SHOW BINLOG EVENTS IN 'master-bin.000010'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000010 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000010 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 7 + 1 +master-bin.000010 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000010 # Query 1 # use `mysqltest1`; CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB" + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +8 +SHOW BINLOG EVENTS IN 'slave-bin.000010'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000010 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 7 + 1 +slave-bin.000010 # Query 1 # use `mysqltest1`; CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB" + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +8 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000010'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000010 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000010 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 7 + 1 +master-bin.000010 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000010 # Query 1 # use `mysqltest1`; CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB" + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +8 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000010'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000010 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 7 + 1 +slave-bin.000010 # Query 1 # use `mysqltest1`; CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB" + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- + +######## CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 8 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +9 +SHOW BINLOG EVENTS IN 'master-bin.000011'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +8 +SHOW BINLOG EVENTS IN 'slave-bin.000011'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT); +SELECT MAX(f1) FROM t1; +MAX(f1) +9 +SHOW BINLOG EVENTS IN 'master-bin.000011'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +8 +SHOW BINLOG EVENTS IN 'slave-bin.000011'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +SELECT MAX(f1) FROM t1; +MAX(f1) +8 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000011'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000011 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000011 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 8 + 1 +master-bin.000011 # Query 1 # use `mysqltest1`; CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) +master-bin.000011 # Query 1 # use `mysqltest1`; ROLLBACK + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +9 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000011'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000011 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 8 + 1 +slave-bin.000011 # Query 1 # use `mysqltest1`; CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- + +######## TRUNCATE TABLE mysqltest1.t7 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 8 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +9 +SHOW BINLOG EVENTS IN 'master-bin.000012'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +9 +SHOW BINLOG EVENTS IN 'slave-bin.000012'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +TRUNCATE TABLE mysqltest1.t7; +SELECT MAX(f1) FROM t1; +MAX(f1) +9 +SHOW BINLOG EVENTS IN 'master-bin.000012'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000012 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000012 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 8 + 1 +master-bin.000012 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000012 # Query 1 # use `mysqltest1`; TRUNCATE TABLE mysqltest1.t7 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +9 +SHOW BINLOG EVENTS IN 'slave-bin.000012'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000012 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 8 + 1 +slave-bin.000012 # Query 1 # use `mysqltest1`; TRUNCATE TABLE mysqltest1.t7 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +9 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000012'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000012 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000012 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 8 + 1 +master-bin.000012 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000012 # Query 1 # use `mysqltest1`; TRUNCATE TABLE mysqltest1.t7 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +9 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000012'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000012 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 8 + 1 +slave-bin.000012 # Query 1 # use `mysqltest1`; TRUNCATE TABLE mysqltest1.t7 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SELECT * FROM mysqltest1.t7; +f1 + +-------- switch to slave -------- +SELECT * FROM mysqltest1.t7; +f1 + +-------- switch to master ------- + +######## LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 9 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'master-bin.000013'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +9 +SHOW BINLOG EVENTS IN 'slave-bin.000013'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'master-bin.000013'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000013 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000013 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 9 + 1 +master-bin.000013 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'slave-bin.000013'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000013 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 9 + 1 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +10 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000013'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000013 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000013 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 9 + 1 +master-bin.000013 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000013'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000013 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 9 + 1 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +UNLOCK TABLES; + +######## UNLOCK TABLES ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 10 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +11 +SHOW BINLOG EVENTS IN 'master-bin.000014'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'slave-bin.000014'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +UNLOCK TABLES; +SELECT MAX(f1) FROM t1; +MAX(f1) +11 +SHOW BINLOG EVENTS IN 'master-bin.000014'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'slave-bin.000014'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +10 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000014'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 + +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000014'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +LOCK TABLES mysqltest1.t1 READ; + +######## UNLOCK TABLES ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 10 + 1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'master-bin.000015'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'slave-bin.000015'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +UNLOCK TABLES; +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'master-bin.000015'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'slave-bin.000015'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +10 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000015'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 + +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000015'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; + +######## UNLOCK TABLES ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 10 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +11 +SHOW BINLOG EVENTS IN 'master-bin.000016'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +10 +SHOW BINLOG EVENTS IN 'slave-bin.000016'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +UNLOCK TABLES; +SELECT MAX(f1) FROM t1; +MAX(f1) +11 +SHOW BINLOG EVENTS IN 'master-bin.000016'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000016 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000016 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 10 + 1 +master-bin.000016 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +11 +SHOW BINLOG EVENTS IN 'slave-bin.000016'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000016 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 10 + 1 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +11 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000016'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000016 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000016 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 10 + 1 +master-bin.000016 # Query 1 # use `mysqltest1`; COMMIT + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +11 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000016'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000016 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 10 + 1 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- + +######## DROP INDEX my_idx6 ON mysqltest1.t6 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 11 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +12 +SHOW BINLOG EVENTS IN 'master-bin.000017'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +11 +SHOW BINLOG EVENTS IN 'slave-bin.000017'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +DROP INDEX my_idx6 ON mysqltest1.t6; +SELECT MAX(f1) FROM t1; +MAX(f1) +12 +SHOW BINLOG EVENTS IN 'master-bin.000017'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000017 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000017 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 11 + 1 +master-bin.000017 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000017 # Query 1 # use `mysqltest1`; DROP INDEX my_idx6 ON mysqltest1.t6 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +12 +SHOW BINLOG EVENTS IN 'slave-bin.000017'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000017 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 11 + 1 +slave-bin.000017 # Query 1 # use `mysqltest1`; DROP INDEX my_idx6 ON mysqltest1.t6 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +12 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000017'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000017 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000017 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 11 + 1 +master-bin.000017 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000017 # Query 1 # use `mysqltest1`; DROP INDEX my_idx6 ON mysqltest1.t6 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +12 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000017'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000017 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 11 + 1 +slave-bin.000017 # Query 1 # use `mysqltest1`; DROP INDEX my_idx6 ON mysqltest1.t6 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SHOW INDEX FROM mysqltest1.t6; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment + +-------- switch to slave -------- +SHOW INDEX FROM mysqltest1.t6; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment + +-------- switch to master ------- + +######## CREATE INDEX my_idx5 ON mysqltest1.t5(f1) ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 12 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +13 +SHOW BINLOG EVENTS IN 'master-bin.000018'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +12 +SHOW BINLOG EVENTS IN 'slave-bin.000018'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +CREATE INDEX my_idx5 ON mysqltest1.t5(f1); +SELECT MAX(f1) FROM t1; +MAX(f1) +13 +SHOW BINLOG EVENTS IN 'master-bin.000018'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000018 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000018 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 12 + 1 +master-bin.000018 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000018 # Query 1 # use `mysqltest1`; CREATE INDEX my_idx5 ON mysqltest1.t5(f1) + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +13 +SHOW BINLOG EVENTS IN 'slave-bin.000018'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000018 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 12 + 1 +slave-bin.000018 # Query 1 # use `mysqltest1`; CREATE INDEX my_idx5 ON mysqltest1.t5(f1) + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +13 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000018'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000018 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000018 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 12 + 1 +master-bin.000018 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000018 # Query 1 # use `mysqltest1`; CREATE INDEX my_idx5 ON mysqltest1.t5(f1) + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +13 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000018'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000018 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 12 + 1 +slave-bin.000018 # Query 1 # use `mysqltest1`; CREATE INDEX my_idx5 ON mysqltest1.t5(f1) + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SHOW INDEX FROM mysqltest1.t5; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t5 1 my_idx5 1 f1 A 0 NULL NULL YES BTREE + +-------- switch to slave -------- +SHOW INDEX FROM mysqltest1.t5; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t5 1 my_idx5 1 f1 A NULL NULL NULL YES BTREE + +-------- switch to master ------- + +######## DROP DATABASE mysqltest2 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 13 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +14 +SHOW BINLOG EVENTS IN 'master-bin.000019'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +13 +SHOW BINLOG EVENTS IN 'slave-bin.000019'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +DROP DATABASE mysqltest2; +SELECT MAX(f1) FROM t1; +MAX(f1) +14 +SHOW BINLOG EVENTS IN 'master-bin.000019'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000019 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000019 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 13 + 1 +master-bin.000019 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000019 # Query 1 # DROP DATABASE mysqltest2 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +14 +SHOW BINLOG EVENTS IN 'slave-bin.000019'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000019 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 13 + 1 +slave-bin.000019 # Query 1 # DROP DATABASE mysqltest2 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +14 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000019'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000019 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000019 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 13 + 1 +master-bin.000019 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000019 # Query 1 # DROP DATABASE mysqltest2 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +14 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000019'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000019 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 13 + 1 +slave-bin.000019 # Query 1 # DROP DATABASE mysqltest2 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SHOW DATABASES LIKE "mysqltest2"; +Database (mysqltest2) + +-------- switch to slave -------- +SHOW DATABASES LIKE "mysqltest2"; +Database (mysqltest2) + +-------- switch to master ------- + +######## CREATE DATABASE mysqltest3 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 14 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) +15 +SHOW BINLOG EVENTS IN 'master-bin.000020'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +14 +SHOW BINLOG EVENTS IN 'slave-bin.000020'; +Log_name Pos Event_type Server_id Orig_log_pos Info + +-------- switch to master ------- +CREATE DATABASE mysqltest3; +SELECT MAX(f1) FROM t1; +MAX(f1) +15 +SHOW BINLOG EVENTS IN 'master-bin.000020'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000020 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000020 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 14 + 1 +master-bin.000020 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000020 # Query 1 # CREATE DATABASE mysqltest3 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +15 +SHOW BINLOG EVENTS IN 'slave-bin.000020'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000020 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 14 + 1 +slave-bin.000020 # Query 1 # CREATE DATABASE mysqltest3 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +15 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'master-bin.000020'; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.000020 # Query 1 # use `mysqltest1`; BEGIN +master-bin.000020 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 14 + 1 +master-bin.000020 # Query 1 # use `mysqltest1`; COMMIT +master-bin.000020 # Query 1 # CREATE DATABASE mysqltest3 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +15 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +SHOW BINLOG EVENTS IN 'slave-bin.000020'; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.000020 # Query 1 # use `mysqltest1`; INSERT INTO t1 SET f1= 14 + 1 +slave-bin.000020 # Query 1 # CREATE DATABASE mysqltest3 + +-------- switch to master ------- +flush logs; + +-------- switch to slave -------- +flush logs; + +-------- switch to master ------- +SHOW DATABASES LIKE "mysqltest3"; +Database (mysqltest3) +mysqltest3 + +-------- switch to slave -------- +SHOW DATABASES LIKE "mysqltest3"; +Database (mysqltest3) +mysqltest3 + +-------- switch to master ------- +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +DROP DATABASE IF EXISTS mysqltest3; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index bac6e60c302..a281558e5a1 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -731,3 +731,17 @@ select ifnull(NULL, _utf8'string'); create table t1 (a varchar(255)) default character set utf8; insert into t1 values (1.0); drop table t1; + +# +# Bug#10253 compound index length and utf8 char set +# produces invalid query results +# +create table t1 ( + id int not null, + city varchar(20) not null, + key (city(7),id) +) character set=utf8; +insert into t1 values (1,'Durban North'); +insert into t1 values (2,'Durban'); +select * from t1 where city = 'Durban'; +drop table t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 7b27d589ec3..06e73d2d32c 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -596,7 +596,6 @@ show tables from mysqltest; set autocommit=0; create table t1 (a int not null) engine= innodb; insert into t1 values(1),(2); ---error 1192 truncate table t1; commit; truncate table t1; diff --git a/mysql-test/t/rpl_ddl.test b/mysql-test/t/rpl_ddl.test new file mode 100644 index 00000000000..d9b09d397b5 --- /dev/null +++ b/mysql-test/t/rpl_ddl.test @@ -0,0 +1,344 @@ +######################## rpl_ddl.test ######################## +# # +# DDL statements (sometimes with implicit COMMIT) executed # +# by the master and it's propagation into the slave # +# # +############################################################## + +# +# NOTE, PLEASE BE CAREFUL, WHEN MODIFYING THE TESTS !! +# +# 1. !All! objects to be dropped, renamed, altered ... must be created +# in AUTOCOMMIT= 1 mode before AUTOCOMMIT is set to 0 and the test +# sequences start. +# +# 2. Never use a test object, which was direct or indirect affected by a +# preceeding test sequence again. +# Except table d1.t1 where ONLY DML is allowed. +# +# If one preceeding test sequence hits a (sometimes not good visible, +# because the sql error code of the statement might be 0) bug +# and these rules are ignored, a following test sequence might earn ugly +# effects like failing 'sync_slave_with_master', crashes of the slave or +# abort of the test case etc.. +# +# 3. The assignment of the DDL command to be tested to $my_stmt can +# be a bit difficult. "'" must be avoided, because the test +# routine "include/rpl_stmt_seq.inc" performs a +# eval SELECT CONCAT('######## ','$my_stmt',' ########') as ""; +# + +--source include/have_innodb.inc +--source include/master-slave.inc + +############################################################### +# Some preparations +############################################################### +SET AUTOCOMMIT = 1; +# +# 1. DROP all objects, which probably already exist, but must be created here +# +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +DROP DATABASE IF EXISTS mysqltest3; +--enable_warnings +# +# 2. CREATE all objects needed +# working database is mysqltest1 +# working (transactional!) is mysqltest1.t1 +# +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE= "InnoDB"; +INSERT INTO mysqltest1.t1 SET f1= 0; +CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE INDEX my_idx6 ON mysqltest1.t6(f1); +CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE= "InnoDB"; +INSERT INTO mysqltest1.t7 SET f1= 0; +CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT); + +# +# 3. master sessions: never do AUTOCOMMIT +# slave sessions: never do AUTOCOMMIT +# +SET AUTOCOMMIT = 0; +use mysqltest1; +sync_slave_with_master; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SET AUTOCOMMIT = 0; +use mysqltest1; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + + +# We don't want to abort the whole test if one statement sent +# to the server gets an error, because the following test +# sequences are nearly independend of the previous statements. +--disable_abort_on_error + +############################################################### +# Banal case: (explicit) COMMIT and ROLLBACK +# Just for checking if the test sequence is usable +############################################################### + +let $my_stmt= COMMIT; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc + +let $my_stmt= ROLLBACK; +let $my_master_commit= false; +let $my_slave_commit= false; +--source include/rpl_stmt_seq.inc + +############################################################### +# Cases with commands very similar to COMMIT +############################################################### + +let $my_stmt= SET AUTOCOMMIT=1; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SET AUTOCOMMIT=0; + +let $my_stmt= START TRANSACTION; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc + +let $my_stmt= BEGIN; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc + +############################################################### +# Cases with (BASE) TABLES and (UPDATABLE) VIEWs +############################################################### + +let $my_stmt= DROP TABLE mysqltest1.t2; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SHOW TABLES LIKE 't2'; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW TABLES LIKE 't2'; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +# Note: Since this test is executed with a skip-innodb slave, the +# slave incorrectly commits the insert. One can *not* have InnoDB on +# master and MyISAM on slave and expect that a transactional rollback +# after a CREATE TEMPORARY TABLE should work correctly on the slave. +# For this to work properly the handler on the slave must be able to +# handle transactions (e.g. InnoDB or NDB). +let $my_stmt= DROP TEMPORARY TABLE mysqltest1.t23; +let $my_master_commit= false; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SHOW TABLES LIKE 't23'; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW TABLES LIKE 't23'; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +let $my_stmt= RENAME TABLE mysqltest1.t3 to mysqltest1.t20; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SHOW TABLES LIKE 't20'; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW TABLES LIKE 't20'; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +let $my_stmt= ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +describe mysqltest1.t4; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +describe mysqltest1.t4; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB"; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc + +# Note: Since this test is executed with a skip-innodb slave, the +# slave incorrectly commits the insert. One can *not* have InnoDB on +# master and MyISAM on slave and expect that a transactional rollback +# after a CREATE TEMPORARY TABLE should work correctly on the slave. +# For this to work properly the handler on the slave must be able to +# handle transactions (e.g. InnoDB or NDB). +let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT); +let $my_master_commit= false; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc + +let $my_stmt= TRUNCATE TABLE mysqltest1.t7; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SELECT * FROM mysqltest1.t7; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SELECT * FROM mysqltest1.t7; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +############################################################### +# Cases with LOCK/UNLOCK +############################################################### + +# MySQL insists in locking mysqltest1.t1, because rpl_stmt_seq performs an +# INSERT into this table. +let $my_stmt= LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +UNLOCK TABLES; + +# No prior locking +let $my_stmt= UNLOCK TABLES; +let $my_master_commit= false; +let $my_slave_commit= false; +--source include/rpl_stmt_seq.inc + +# With prior read locking +LOCK TABLES mysqltest1.t1 READ; +let $my_stmt= UNLOCK TABLES; +let $my_master_commit= false; +let $my_slave_commit= false; +--source include/rpl_stmt_seq.inc + +# With prior write locking +LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; +let $my_stmt= UNLOCK TABLES; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc + +############################################################### +# Cases with INDEXES +############################################################### + +let $my_stmt= DROP INDEX my_idx6 ON mysqltest1.t6; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SHOW INDEX FROM mysqltest1.t6; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW INDEX FROM mysqltest1.t6; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +let $my_stmt= CREATE INDEX my_idx5 ON mysqltest1.t5(f1); +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SHOW INDEX FROM mysqltest1.t5; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW INDEX FROM mysqltest1.t5; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +############################################################### +# Cases with DATABASE +############################################################### + +let $my_stmt= DROP DATABASE mysqltest2; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SHOW DATABASES LIKE "mysqltest2"; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW DATABASES LIKE "mysqltest2"; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +let $my_stmt= CREATE DATABASE mysqltest3; +let $my_master_commit= true; +let $my_slave_commit= true; +--source include/rpl_stmt_seq.inc +SHOW DATABASES LIKE "mysqltest3"; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW DATABASES LIKE "mysqltest3"; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +############################################################### +# Cleanup +############################################################### +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +DROP DATABASE IF EXISTS mysqltest3; +--enable_warnings diff --git a/scripts/mysqlhotcopy.sh b/scripts/mysqlhotcopy.sh index 632174dc41a..1c5cd6a4faf 100644 --- a/scripts/mysqlhotcopy.sh +++ b/scripts/mysqlhotcopy.sh @@ -746,9 +746,15 @@ sub record_log_pos { my ($file,$position) = get_row( $dbh, "show master status" ); die "master status is undefined" if !defined $file || !defined $position; - my ($master_host, undef, undef, undef, $log_file, $log_pos ) - = get_row( $dbh, "show slave status" ); - + my $row_hash = get_row_hash( $dbh, "show slave status" ); + my ($master_host, $log_file, $log_pos ); + if ( $dbh->{mysql_serverinfo} =~ /^3\.23/ ) { + ($master_host, $log_file, $log_pos ) + = @{$row_hash}{ qw / Master_Host Log_File Pos / }; + } else { + ($master_host, $log_file, $log_pos ) + = @{$row_hash}{ qw / Master_Host Master_Log_File Read_Master_Log_Pos / }; + } my $hostname = hostname(); $dbh->do( qq{ replace into $table_name @@ -773,6 +779,14 @@ sub get_row { return $sth->fetchrow_array(); } +sub get_row_hash { + my ( $dbh, $sql ) = @_; + + my $sth = $dbh->prepare($sql); + $sth->execute; + return $sth->fetchrow_hashref(); +} + sub scan_raid_dir { my ( $r_db_files, $data_dir, @raid_dir ) = @_; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index b534e876638..3ecc1b907fc 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2740,6 +2740,20 @@ mysql_execute_command(THD *thd) case SQLCOM_CREATE_TABLE: { + /* If CREATE TABLE of non-temporary table, do implicit commit */ + if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE)) + { + if (end_active_trans(thd)) + { + res= -1; + break; + } + } + else + { + /* So that CREATE TEMPORARY TABLE gets to binlog at commit/rollback */ + thd->options|= OPTION_STATUS_NO_TRANS_UPDATE; + } DBUG_ASSERT(first_table == all_tables && first_table != 0); bool link_to_local; // Skip first table, which is the table we are creating @@ -3232,6 +3246,11 @@ end_with_restore_list: break; } case SQLCOM_TRUNCATE: + if (end_active_trans(thd)) + { + res= -1; + break; + } DBUG_ASSERT(first_table == all_tables && first_table != 0); if (check_one_table_access(thd, DELETE_ACL, all_tables)) goto error; @@ -3324,6 +3343,9 @@ end_with_restore_list: */ if (thd->slave_thread) lex->drop_if_exists= 1; + + /* So that DROP TEMPORARY TABLE gets to binlog at commit/rollback */ + thd->options|= OPTION_STATUS_NO_TRANS_UPDATE; } res= mysql_rm_table(thd, first_table, lex->drop_if_exists, lex->drop_temporary); @@ -3462,6 +3484,11 @@ end_with_restore_list: break; case SQLCOM_CREATE_DB: { + if (end_active_trans(thd)) + { + res= -1; + break; + } char *alias; if (!(alias=thd->strdup(lex->name)) || check_db_name(lex->name)) { @@ -3492,6 +3519,11 @@ end_with_restore_list: } case SQLCOM_DROP_DB: { + if (end_active_trans(thd)) + { + res= -1; + break; + } if (check_db_name(lex->name)) { my_error(ER_WRONG_DB_NAME, MYF(0), lex->name); |