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: | |
Category: | MySQL Server: Stored Routines | Severity: | S1 (Critical) |
Version: | 5.1.47 | OS: | Any |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[9 Jun 2010 17:38]
Todd Farmer
[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)