MySQL Bugs: #14617: Executing procedure from other database which has error, results in crash

Bug #14617	Executing procedure from other database which has error, results in crash
Submitted:	3 Nov 2005 18:29	Modified:	25 Jan 2006 14:19
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.16-BK, 5.0.15	OS:	Linux (Linux, Windows)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
Hi,

Executing the provided SQL script will crash MySQL 5.0.15 when the stored procedure is called when no database is selected, or a database other than 'test' is used.

The procedure when executed in 'test' database (or where it is created), is running normally producing error 1222. When another query is put in the SP, then it all works ok. I tried other errors, like putting two same DECLARE statements in procedure, and that also doesn't crash..

Found it on MacOSX using 5.0.15, reproduced on Linux using MySQL 5.0.15 and current bk pull (5.0.16).

Regards,
Geert

How to repeat:
-- No current database, or use a database other than 'test'
USE world;

DROP PROCEDURE IF EXISTS test.telephone_dir;
DROP TABLE IF EXISTS test.employees;
CREATE TABLE test.employees ( name varchar(20), telephone varchar(20));

-- Following SP produces an error when executing it:
-- ERROR 1222 (21000) at line 23: The used SELECT statements have a
--    different number of columns
DELIMITER //
CREATE PROCEDURE test.telephone_dir (OUT param1 INT)
BEGIN
  SELECT name, telephone INTO param1 FROM employees;
END;
//
DELIMITER ;

-- Now call the procedure from the test database.
-- Note: we are still in 'world' database, or have no current database.
SET @c = 0;
CALL test.telephone_dir(@c);
SELECT @c; 

Suggested fix:
Wish I had one.

Verified also on Windows XP with 5.0.15 and Fedora Core 1 Linux with 5.0.16-BK (ChangeSet@1.1957.1.11, 2005-11-01 12:15:08-08:00, jimw@mysql.com), just as described:

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP PROCEDURE IF EXISTS test.telephone_dir;
Query OK, 0 rows affected, 1 warning (0,13 sec)

mysql> DROP TABLE IF EXISTS test.employees;
Query OK, 0 rows affected, 1 warning (0,07 sec)

mysql> vCREATE TABLE test.employees ( name varchar(20), telephone varchar(20));
Query OK, 0 rows affected (0,02 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE test.telephone_dir (OUT param1 INT)
    -> BEGIN
    ->   SELECT name, telephone INTO param1 FROM employees;
    -> END;
    -> //
Query OK, 0 rows affected (0,03 sec)

mysql> DELIMITER ;
mysql> SET @c = 0;
Query OK, 0 rows affected (0,05 sec)

mysql> CALL test.telephone_dir(@c);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
051104 10:32:35  mysqld restarted

This produced the following in the error log:

051104 10:23:07  InnoDB: Started; log sequence number 0 26172849
051104 10:23:07 [Note] /home/openxs/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.16'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x924e7a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb8dea83c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x814d6c5
0x64ef18
0x2
0x80fff7f
0x823b282
0x8165473
0x8167ea6
0x815f1b6
0x815ed71
0x815e1de
0x64879c
0x49527a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x927e040 = CALL test.telephone_dir(@c)
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
051104 10:32:35  mysqld restarted

This must have been fixed already, probably is a duplicate of Bug#13587:

kostja@dragonfly:~> mysql5 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-valgrind-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE world;
Database changed
mysql> 
mysql> DROP PROCEDURE IF EXISTS test.telephone_dir;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS test.employees;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test.employees ( name varchar(20), telephone varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> -- Following SP produces an error when executing it:
mysql> -- ERROR 1222 (21000) at line 23: The used SELECT statements have a
mysql> --    different number of columns
mysql> DELIMITER //
mysql> CREATE PROCEDURE test.telephone_dir (OUT param1 INT)
    -> BEGIN
    ->   SELECT name, telephone INTO param1 FROM employees;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> 
mysql> -- Now call the procedure from the test database.
mysql> -- Note: we are still in 'world' database, or have no current database.
mysql> SET @c = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL test.telephone_dir(@c);
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> SELECT @c; 
+------+
| @c   |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

mysql> CALL test.telephone_dir(@c);
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.19-valgrind-max-debug |
+---------------------------+
1 row in set (0.00 sec)

mysql>