Edit File - File Manager

Edit File: drop_temp_table.test

--disable_warnings
DROP DATABASE IF EXISTS `drop-temp+table-test`;
--enable_warnings

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connection con1;
RESET MASTER;
CREATE DATABASE `drop-temp+table-test`;
USE `drop-temp+table-test`;
CREATE TEMPORARY TABLE shortn1 (a INT);
CREATE TEMPORARY TABLE `table:name` (a INT);
CREATE TEMPORARY TABLE shortn2 (a INT);

##############################################################################
# BUG#46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior
# in ROW mode 
# 
# In RBR, 'DROP TEMPORARY TABLE ...' statement should never be binlogged no
# matter if the tables exist or not. In contrast, both in SBR and MBR, the
# statement should be always binlogged no matter if the tables exist or not.
##############################################################################
CREATE TEMPORARY TABLE tmp(c1 int);
CREATE TEMPORARY TABLE tmp1(c1 int);
CREATE TEMPORARY TABLE tmp2(c1 int);
CREATE TEMPORARY TABLE tmp3(c1 int);
CREATE TABLE t(c1 int);

DROP TEMPORARY TABLE IF EXISTS tmp;

--disable_warnings
# Before fixing BUG#46572, 'DROP TEMPORARY TABLE IF EXISTS...' statement was
# binlogged when the table did not exist in RBR.
DROP TEMPORARY TABLE IF EXISTS tmp;

# In RBR, 'DROP TEMPORARY TABLE ...' statement is never binlogged no matter if
# the tables exist or not.
DROP TEMPORARY TABLE IF EXISTS tmp, tmp1;
DROP TEMPORARY TABLE tmp3;

#In RBR, tmp2 will NOT be binlogged, because it is a temporary table.
DROP TABLE IF EXISTS tmp2, t;

#In RBR, tmp2 will be binlogged, because it does not exist and master do not know
# whether it is a temporary table or not.
DROP TABLE IF EXISTS tmp2, t;
--enable_warnings

SELECT GET_LOCK("a",10);

#
# BUG48216 Replication fails on all slaves after upgrade to 5.0.86 on master
#
# When the session is closed, any temporary tables of the session are dropped
# and are binlogged. But it will be binlogged with a wrong database name when
# the length of the database name('drop-temp-table-test') is greater than the
# current database name('test').
#
USE test;
disconnect con1;

connection con2;
# We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
# guarantee that logging of the terminated con1 has been done yet.
# To be sure that logging has been done, we use a user lock.
SELECT GET_LOCK("a",10);
let $VERSION=`SELECT VERSION()`;
source include/show_binlog_events.inc;
DROP DATABASE `drop-temp+table-test`;

#
# Bug #54842: DROP TEMPORARY TABLE not binlogged after manual switching binlog format to ROW
#
# Sanity test. Checking that implicit DROP event is logged.
#
# After BUG#52616, the switch to ROW mode becomes effective even
# if there are open temporary tables. As such the implicit drop
# for temporary tables on session closing must be logged.
#

RESET MASTER;

CREATE TABLE t1 ( i text );

--connect(con1,localhost,root,,)
CREATE TEMPORARY TABLE ttmp1 ( i text );
SET @@session.binlog_format=ROW;
INSERT INTO t1 VALUES ('1');
SELECT @@session.binlog_format;
--disconnect con1

-- connection default
--let $wait_binlog_event= DROP
--source include/wait_for_binlog_event.inc
-- source include/show_binlog_events.inc
RESET MASTER;

DROP TABLE t1;

# End of 4.1 tests

Back to File Manager