173 lines
4.6 KiB
Plaintext
173 lines
4.6 KiB
Plaintext
|
|
# ==== 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;
|
|
|
|
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`;
|
|
copy_file $MYSQLD_DATADIR/$binlog_file $MYSQLD_DATADIR/binlog.saved35583;
|
|
# Reset GTIDs
|
|
RESET MASTER;
|
|
exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.saved35583 | $MYSQL;
|
|
select * from foo;
|
|
|
|
# clean up
|
|
drop table foo;
|
|
-- remove_file $MYSQLD_DATADIR/binlog.saved35583
|
|
|
|
#################################################################
|
|
# 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
|
|
if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`)
|
|
{
|
|
-- 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
|
|
if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`)
|
|
{
|
|
-- let $wait_binlog_event= DROP
|
|
-- source include/wait_for_binlog_event.inc
|
|
}
|
|
|
|
-- eval DROP DATABASE $dbname
|
|
FLUSH LOGS;
|
|
-- let $MYSQLD_DATADIR= `select @@datadir`
|
|
copy_file $MYSQLD_DATADIR/binlog.000001 $MYSQLD_DATADIR/binlog.saved51226;
|
|
# Reset GTIDs
|
|
RESET MASTER;
|
|
# assertion: assert that when replaying the binary log will succeed,
|
|
# instead of failing with "Table 'XXX.YYY' doesn't exist"
|
|
-- exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.saved51226 | $MYSQL
|
|
-- remove_file $MYSQLD_DATADIR/binlog.saved51226
|