MySQL Bugs: #57505: Client stuck to the database where a fatal error happens under STRICT_ALL

Bug #57505	Client stuck to the database where a fatal error happens under STRICT_ALL_TABLES
Submitted:	17 Oct 2010 20:35	Modified:	18 Oct 2010 20:37
Reporter:	Winfried Trümper	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	5.1.46-log, 5.1.51	OS:	Any (OpenSuSE 11.2 x86_64, Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
From database DB2 I'm calling a function in database DB1. That function fails fatally when executed a second time and leaves the client connected to DB1. The prompt hasn't changed, but DATABASE() and \s confirm the horror. Seems to be related to STRICT_ALL_TABLES, because no problem under SET @@SQL_MODE = '';

The following is the relevant part of the output, showing the change of database although no "use" or \u is involved. Just an error triggers the change.

+------------+
| DATABASE() |
+------------+
| db2        |
+------------+
+-------------+
| db1.fun1(1) |
+-------------+
|           0 |
+-------------+
+------------+              
| DATABASE() |              
+------------+              
| db2        |              
+------------+ 
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
--------------
mysql  Ver 14.14 Distrib 5.1.36, for suse-linux-gnu (x86_64) using readline 6.0

Connection id:          1178
Current database:       db1
Current user:           wt@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.36-log SUSE MySQL RPM
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysql/mysql.sock
Uptime:                 4 hours 53 min 12 sec

How to repeat:
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);
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();
SELECT db1.fun1(1);
SELECT DATABASE();
\s

Verified with 5.1.51 on Windows XP also:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.1.51-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET @@SQL_MODE = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)

mysql> DROP DATABASE IF EXISTS db1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

mysql> USE db1;
Database changed
mysql> DROP TABLE IF EXISTS tab1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE TABLE tab1 (col1 int NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.13 sec)

mysql> DELIMITER ;;
mysql> CREATE FUNCTION fun1 (
    ->  some_value int
    -> )
    -> RETURNS smallint
    -> DETERMINISTIC
    -> BEGIN
    ->  INSERT INTO tab1 SET col1 = some_value;
    ->         RETURN(LAST_INSERT_ID());
    -> END;;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> DROP DATABASE IF EXISTS db2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE db2;
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT db1.fun1(1);
+-------------+
| db1.fun1(1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.09 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db2        |
+------------+
1 row in set (0.00 sec)

mysql> SELECT db1.fun1(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.1.51, for Win32 (ia32)

Connection id:          36
Current database:       db1
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.51-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3310
Uptime:                 11 days 15 hours 27 min 20 sec

Threads: 1  Questions: 604  Slow queries: 0  Opens: 1515  Flush tables: 1  Open
tables: 2  Queries per second avg: 0.0
--------------

A duplicate of Bug#54375.