Edit File - File Manager

Edit File: binlog_tmp_table.test

# ==== Purpose ====
#
# Test if statements used temporary tables are binlogged correctly
#
# ==== Method ====
#
# Use two connections, use temporary tables on both of them, and by
# switching connections between statements, the test can check if the
# statements are logged with the correct thread id.
# 
# The statements current tested include:
#   CREATE TEMPORARY TABLE
#   CREATE TEMPORARY TABLE LIKE
#   INSERT
#   REPLACE
#   UPDATE
#   INSERT SELECT
#   TRUNCATE
#
# Note: When adding new query statements, please add them between the
# two 'flush logs'. And aslo please make sure the connection is
# switched between each statement.
#
# ==== Related bugs ====
#
# BUG#35583 mysqlbinlog replay fails with ERROR 1146 when temp tables are used
#
source include/have_log_bin.inc;
source include/have_binlog_format_mixed_or_statement.inc;

RESET MASTER;

--disable_query_log
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
--enable_query_log

connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,);

create table foo (a int);

flush logs;

connection master;
create temporary table tmp1_foo like foo;
connection master1;
create temporary table tmp2_foo (a int);

connection master;
insert into tmp1_foo values (1), (2), (3), (4);
connection master1;
replace into tmp2_foo values (1), (2), (3), (4);

connection master;
update tmp1_foo set a=2*a-1;
connection master1;
update tmp2_foo set a=2*a;

connection master;
delete from tmp1_foo where a < 5;
connection master1;
delete from tmp2_foo where a < 5;

--disable_warnings
connection master;
insert into foo select * from tmp1_foo;
connection master1;
insert into foo select * from tmp2_foo;
--enable_warnings

connection master;
truncate table tmp1_foo;
connection master1;
truncate table tmp2_foo;

let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);

flush logs;

connection default;
select * from foo;

# prepare for the replay
drop table foo;
create table foo (a int);

# replay from binary log
let $MYSQLD_DATADIR= `select @@datadir`;
exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file | $MYSQL;
select * from foo;

# clean up
drop table foo;

#################################################################
# BUG#51226
#################################################################

RESET MASTER;

-- let $dbname=b51226

connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connect (con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);

#
# action: on con1 create the database and the tmp table
# 
-- connection con1
-- eval create database $dbname
-- eval use $dbname
create temporary table t1(i int);

#
# action: on con1 create the tmp table
# 
-- connection con2
-- eval use $dbname
create temporary table t1(i int);

# action: at this point, the last event binlogged contains the
#         pseudo_thread_id from con2. So now we switch to con1, issue
#         a statement that fails and close the connection (which logs
#         implicitely a DROP TEMPORARY TABLE).  
#
#         Before the patch this would not log con1's pseudo_thread_id
#         because the failing statement would reset THD context
#         (unsetting the thread_specific_used flag, and consequently,
#         causing the DROP event to be logged without pseudo_thread_id
#         in its header).

-- connection con1
-- error 1050
create temporary table t1(i int);
-- disconnect con1

-- connection default
-- let $wait_binlog_event= DROP
-- source include/wait_for_binlog_event.inc

# action: insert in the t1. This would cause the the test to fail,
#         because when replaying the binlog the previous implicit drop
#         temp table would have been executed under the wrong
#         pseudo_thread_id, dropping the tmp table on con2.
-- connection con2
insert into t1 values(1);
-- disconnect con2

-- connection default
-- let $wait_binlog_event= DROP
-- source include/wait_for_binlog_event.inc

-- eval DROP DATABASE $dbname
FLUSH LOGS;

# assertion: assert that when replaying the binary log will succeed,
#            instead of failing with "Table 'XXX.YYY' doesn't exist"
-- let $MYSQLD_DATADIR= `select @@datadir`
-- exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL

Back to File Manager