MySQL Bugs: #29537: Replication failure on DROP PROCEDURE, DROP TEMPORARY TABLE

Bug #29537	Replication failure on DROP PROCEDURE, DROP TEMPORARY TABLE
Submitted:	4 Jul 2007 1:44	Modified:	23 Jul 2010 1:27
Reporter:	Timothy Smith	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.39	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
The original test case for bug #28356 exposed this problem.  In --ps-protocol, the replication slave attempts to drop a temporary table which was created on the master.  Normally such tables are skipped on the slave:

DROP TABLE t1, t1_slave; # t1 is normal table, t1_slave is TEMPORARY

The slave halts with this error:

070704  4:32:16 [ERROR] Slave SQL: Error 'Unknown table 't1_slave'' on query. De
fault database: 'test'. Query: 'DROP TABLE t1, t1_slave', Error_code: 1051

This problem shows up only when DROP PROCEDURE is used before it.  When the DROP PROCEDURE call is moved to after the DROP TABLE, there is no problem.

How to repeat:
rpl_trs.test:

source include/master-slave.inc;

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (col_a double default NULL);

DELIMITER |;

CREATE PROCEDURE test_replication_sp1()
BEGIN
INSERT INTO t1 VALUES (1);
END|
DELIMITER ;|

--sync_slave_with_master

connection master;
create temporary table t1_slave select * from t1 where 1=0;

connection master;
# Move this DROP PROCEDURE down a line, and it works properly
DROP PROCEDURE test_replication_sp1;
DROP TABLE t1, t1_slave;
--sync_slave_with_master

rpl_trs.result:

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;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (col_a double default NULL);
CREATE PROCEDURE test_replication_sp1()
BEGIN
INSERT INTO t1 VALUES (1);
END|
create temporary table t1_slave select * from t1 where 1=0;
DROP PROCEDURE test_replication_sp1;
DROP TABLE t1, t1_slave;

Suggested fix:
A workaround is to drop the table first, and then drop the procedure.  Or to not use ps-protocol.

Thank you for a problem report. Maybe I missed something, but with your .test and .result files I've got on SuSE Linux 9.3:

openxs@linux:~/dbs/5.1/mysql-test> ./mysql-test-run.pl --ps-protocol rpl_trs
Logging: ./mysql-test-run.pl --ps-protocol rpl_trs
070707 16:35:46 [Note] /home/openxs/dbs/5.1/libexec/mysqld: Shutdown complete

MySQL Version 5.1.21
Using binlog format 'mixed'
Using ndbcluster when necessary, mysqld supports it
Skipping SSL, mysqld not compiled with SSL
mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests will fail, use --skip-ndbcluster to skip testing it.
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9310
Using NDBCLUSTER_PORT_SLAVE = 9311
Using IM_PORT               = 9312
Using IM_MYSQLD1_PORT       = 9313
Using IM_MYSQLD2_PORT       = 9314
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
Installing Master Database
Installing Slave1 Database
=======================================================
Starting Tests in the 'main' suite

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

rpl_trs                        [ pass ]            191
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 0.191 seconds actually executing testcases

So, more ideas on how to repeat are welcomed.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Verifying as D2. 

Using the following test case I was able to more-or-less consistently repeat the bug. For some unknown reason, the bug was not 100% consistently repeatable.

Verified on 5.1.39 using a standard replication setup. Possible regression: error does *not* occur on 5.1.36 using same testcase.

On the master execute:
------------
DROP TABLE IF EXISTS t21;
DROP TABLE IF EXISTS t21_slave;
DROP PROCEDURE IF EXISTS test_replication_sp21;

CREATE TABLE t21 (ID INT);
CREATE TEMPORARY TABLE t21_slave SELECT * FROM t21 WHERE 1=0;

CREATE PROCEDURE test_replication_sp21()
BEGIN
INSERT INTO t21 VALUES (1); /* Generates an expected error */

DELIMITER |
CREATE PROCEDURE test_replication_sp21()
BEGIN
INSERT INTO t21 VALUES (1);
END|
DELIMITER ;

DROP PROCEDURE test_replication_sp21;
DROP TABLE t21, t21_slave;
------------

Expected result:
------------
mysql> SHOW SLAVE STATUS\G
[...]
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
[...]
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 't21_slave'' on query. Default database: 'roelt'. Query: 'DROP TABLE t21, t21_slave'
[...]
               Last_SQL_Errno: 1051
               Last_SQL_Error: Error 'Unknown table 't21_slave'' on query. Default database: 'roelt'. Query: 'DROP TABLE t21, t21_slave'
------------

Also note bug #30882

The bug is fixed in the patch of Bug#49132.

Duplicate with BUG#49132.