Bug #54375 Error in stored procedure leaves connection in different default schema
Submitted: 9 Jun 2010 17:38 Modified: 15 Dec 2010 12:01
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.1.47 OS:Any
Assigned to: Paul Dubois
Triage: Triaged: D3 (Medium)

[9 Jun 2010 17:38] Todd Farmer
Description:
Executing chained, cross-schema stored procedures can leave the connection with a different default database if an error is encountered and thrown from a stored procedure in another schema:

mysql> CREATE TABLE t2.d (a INT PRIMARY KEY NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE PROCEDURE t1.t1sp() CALL t2.t2sp();
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE PROCEDURE t2.t2sp() INSERT INTO d VALUES (1), (1);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> USE t1;
Database changed
mysql> SELECT SCHEMA();
+----------+
| SCHEMA() |
+----------+
| t1       |
+----------+
1 row in set (0.00 sec)

mysql> CALL t1sp();
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> SELECT SCHEMA();
+----------+
| SCHEMA() |
+----------+
| t2       |
+----------+
1 row in set (0.00 sec)

How to repeat:
CREATE DATABASE IF NOT EXISTS t1;
CREATE DATABASE IF NOT EXISTS t2;
DROP TABLE IF EXISTS t2.d;
DROP PROCEDURE IF EXISTS t1.t1sp;
DROP PROCEDURE IF EXISTS t2.t2sp;

CREATE TABLE t2.d (a INT PRIMARY KEY NOT NULL);

CREATE PROCEDURE t1.t1sp() CALL t2.t2sp();

CREATE PROCEDURE t2.t2sp() INSERT INTO d VALUES (1), (1);

USE t1;
SELECT SCHEMA();
CALL t1sp();
SELECT SCHEMA();

Suggested fix:
Ensure the default database is never silently changed during course of execution (and failure) of stored procedures.
[18 Oct 2010 20:37] Konstantin Osipov
Bug#57505 was marked a duplicate of this bug.
[19 Oct 2010 13:02] Winfried Trümper
Report for Bug#57505 narrows the problem down. Yes, both reports refer to the same bug, but whether it is triggered or not depends on @@SQL_MODE. Hope that information helps in fixing the bug.

Try the "How to repeat" for this bug (#54375) with SET @@SQL_MODE = ''; It gives me t1 before and after, so no problem. It's not required to have a complicated case with two procedures or two statements. One failed statement under STRICT_ALL_TABLES in a stored program of another database switches the client connection to that database.

Have a "DO" in front of the offending command, the client continues and subsequent commands commands end up in the wrong database. Nice Halloween story!
[19 Oct 2010 13:03] Winfried Trümper
-- no problem, t1 before and after
SET @@SQL_MODE = '';
+----------+
| SCHEMA() |
+----------+
| t1       |
+----------+
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
+----------+
| SCHEMA() |
+----------+
| t1       |
+----------+
[19 Oct 2010 13:06] Winfried Trümper
-- one function call less than my original suggestion in Bug#57505
-- just to make it clear that it's not limited to very special cases
SET @@SQL_MODE = 'STRICT_ALL_TABLES';
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1 (col1 int NOT NULL PRIMARY KEY);
INSERT INTO tab1 VALUES (1);
DELIMITER ;;
CREATE FUNCTION fun1 (
	some_value int
)
RETURNS smallint
DETERMINISTIC
BEGIN
	INSERT INTO tab1 SET col1 = some_value;
        RETURN(LAST_INSERT_ID());
END;;
DELIMITER ;
DROP DATABASE IF EXISTS db2;
CREATE DATABASE db2;
USE db2;
SELECT DATABASE();
SELECT db1.fun1(1);
SELECT DATABASE();
[25 Oct 2010 17:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121852

3545 Dmitry Shulga	2010-10-26
      Fixed bug#54375 - Error in stored procedure leaves connection
      in different default schema.
     @ mysql-test/r/sp-bugs.result
        It was added result for test case for bug#54375.
     @ mysql-test/t/sp-bugs.test
        It was added test case for bug#54375.
     @ sql/sp_head.cc
        sp_head::execute modified: restore saved current db if
        connection is not broken.
[11 Nov 2010 4:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123517

3503 Dmitry Shulga	2010-11-11
      Fixed bug#54375 - Error in stored procedure leaves connection
      in different default schema.
      
      In strict mode, when data truncation or conversion happens,
      THD::killed is set to THD::KILL_BAD_DATA.
      
      This is abuse of KILL mechanism to guarantee that execution
      of statement is aborted.
      
      The stored procedures execution, on the other hand,
      upon detection that a connection was killed, would
      terminate immediately, without trying to restore the caller's
      context, in particular, restore the caller's current schema.
      
      The fix is, when terminating a stored procedure execution,
      to only bypass cleanup if the entire connection was killed,
      not in case of other forms of KILL.
     @ mysql-test/r/sp-bugs.result
        Added result for a test case for bug#54375.
     @ mysql-test/t/sp-bugs.test
        Added test case for bug#54375.
     @ sql/sp_head.cc
        sp_head::execute modified: restore saved current db if
        connection is not killed.
[11 Nov 2010 5:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123518

3118 Dmitry Shulga	2010-11-11 [merge]
      Auto-merge from mysql-5.1-bugteam for bug#54375.
[11 Nov 2010 5:26] Dmitry Shulga
Pushed to mysql-5.1-bugteam, mysql-5.5-bugteam, mysql-trunk-bugfixing.
[13 Nov 2010 16:09] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:39] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)
[14 Dec 2010 23:18] Paul Dubois
Noted in 5.1.54, 5.5.8 changelogs.

An error in a stored procedure could leave the session in a different 
default database.
[15 Dec 2010 5:50] Bugs System
Pushed into mysql-5.1 5.1.55 (revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (version source revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)
[16 Dec 2010 22:33] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)