--disable_abort_on_error --disable_warnings DROP DATABASE IF EXISTS testdb; --enable_warnings CREATE DATABASE testdb; DELIMITER //; USE testdb// --disable_warnings DROP PROCEDURE IF EXISTS p1// --enable_warnings ##################################################################### # # # The procedure which could cause that the bug appears. # # 1. The procedure has to execute a "drop table if exists t1;". # # That means we will get a warning, if t1 does not exist. # # 2. There is no handler defined, which should handle this warning. # # 3. There is at least one SQL statement between the # # "drop table if exists t1;" and the end of the procedure. # # In our case it is an extreme simple SELECT. # # # ##################################################################### create procedure p1 () begin drop table if exists t1; select 1 as "my-col"; end;// # --disable_warnings DROP TABLE IF EXISTS t1// --enable_warnings # What is within our default database ? SHOW TABLES// # I expect and get: test_db with no tables ##################################################################### # # # There is no table t1 within the database testdb. Therefore the # # following procedure call will get a warning 1051 .... when # # executing "drop table if exists t1;" and the is no handler # # defined for such an event. # # # # I assume the execution of the SP under these conditions # # demolishes some important structures. # # # ##################################################################### call p1()// # expect: # 1. The warning 1051 ... But I do not get it. # 2. One result set with one row and value 1 # ##################################################################### # # # Here comes a very dangerous side effect of the bug. # # The session thinks that the default database is the database used # # during the connect and not "testdb" !! # # Any DROP TABLE without preceding database name would # # now hit into the wrong database. # # # ##################################################################### SHOW TABLES// # expect: test_db with no table # get: test <----- wrong default database !! ##################################################################### # # # Some variations which might be of interest. # # # ##################################################################### # The drop table if exists t1; is successful # Therefore we get no warning and no problem USE testdb// SHOW TABLES// # expect and get: test_db with no table CREATE TABLE t1 ( f1 BIGINT); call p1()// # expect and get: One result set with one row and value 1 SHOW TABLES// # expect and get: test_db with no table # It looks like the simple SQL statement after the DROP TABLE IF EXISTS # is necessary to cause the bad effect. USE testdb// --disable_warnings DROP PROCEDURE IF EXISTS p1// --enable_warnings create procedure p1 () begin drop table if exists t1; end;// --disable_warnings DROP TABLE IF EXISTS t1// --enable_warnings SHOW TABLES// # expect and get: test_db with no table call p1()// # expect and get: # 1. The warning 1051 ... # 2. One result set with one row and value 1 SHOW TABLES// # expect and get: test_db with no table # Will an additional handler for the warning help ? YES USE testdb// --disable_warnings DROP PROCEDURE IF EXISTS p1// --enable_warnings create procedure p1 () begin DECLARE CONTINUE HANDLER FOR 1051 begin end; drop table if exists t1; select 1 as "my-col"; end;// --disable_warnings DROP TABLE IF EXISTS t1// --enable_warnings SHOW TABLES// # expect and get: test_db with no table call p1()// # expect and get: One result set with one row and value 1 # The "noop" handler suppresses the warning. SHOW TABLES// # expect and get: test_db with no table # What will happen, if we get an error instead of the warning ? # DROP TABLE IF EXISTS t1; --> DROP TABLE t1; USE testdb// --disable_warnings DROP PROCEDURE IF EXISTS p1// --enable_warnings create procedure p1 () begin drop table t1; select 1 as "my-col"; end;// --disable_warnings DROP TABLE IF EXISTS t1// --enable_warnings SHOW TABLES// # expect and get: test_db with no table call p1()// # expect and get: Error 1051 ... and no result set SHOW TABLES// # expect and get: test_db with no table