MySQL Bugs: #12949: Creation of stored procedure referencing non existent table crashes server

Bug #12949	Creation of stored procedure referencing non existent table crashes server
Submitted:	2 Sep 2005 13:15	Modified:	2 Sep 2005 13:58
Reporter:	[ name withheld ] (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.11-beta-nt-max	OS:	Windows (Windows Xp sp2)
Assigned to:		CPU Architecture:	Any

Description:
Creation of a stored procedure referencing a non existent table crashes the server.

How to repeat:
C:\Documents and Settings\myuser>mysql -uroot -ppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-nt-max

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

mysql> use test;
Database changed
mysql> delimiter $$
mysql> drop procedure if exists `test`.`a`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure procedure1 /* name */
    -> (in parameter1 integer) /* parameters */
    -> begin /* start of block */
    -> declare variable1 char(10); /* variables */
    -> if parameter1 = 17 then /* start of if */
    -> set variable1 = 'birds'; /* assignment */
    -> else
    -> set variable1 = 'beasts'; /* assignment */
    -> end if; /* end of if */
    -> insert into table1 values (variable1); /* statement */
    -> end $$
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

The server crashes upon procedure creation.

Probably the crash happens due to the fact that the stored proc contains an insert into a non existent table.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-nt

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

mysql> delimiter $$
mysql> drop procedure if exists `test`.`a`$$
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> create procedure procedure1 /* name */
    -> (in parameter1 integer) /* parameters */
    -> begin /* start of block */
    -> declare variable1 char(10); /* variables */
    -> if parameter1 = 17 then /* start of if */
    -> set variable1 = 'birds'; /* assignment */
    -> else
    -> set variable1 = 'beasts'; /* assignment */
    -> end if; /* end of if */
    -> insert into table1 values (variable1); /* statement */
    -> end $$
Query OK, 0 rows affected (0.05 sec)

mysql> select version();
    -> $$
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.02 sec)

Most probably related to the bug:

http://bugs.mysql.com/bug.php?id=12820

see workaround there.

Ok, but I can't find the script in my installation.

Ok, I installed 5.0.11 on top of 5.0.9, after your advice I downloaded the zip distribution for mysql 5.0.11, got the script and run it with MANY errors (see below) but I still get that crash.
My config file (my.ini) has some advanced options set that may cause the problem:

[mysqld]
#ssl related section
ssl-ca=C:/CA/CAcert.pem
ssl-cert=C:/CA/temp/vnc_server/server.pem
ssl-key=C:/CA/temp/vnc_server/server.key

#packet size useful for executing large scripts
max_allowed_packet=32M

# The SQL mode used
sql-mode=TRADITIONAL

# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

What follows is the log of the script execution:

C:\Documents and Settings\myuser\Desktop\mysql-5.0.11-beta-win32\scripts>mysql
 -uroot -pnt300jk --force mysql < c:/mysql_fix_privilege_tables.sql
ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
@hadGrantPriv:=1
1
1
1
1
ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type'
ERROR 1060 (42S21) at line 70: Duplicate column name 'Routine_type'
ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 100: Duplicate column name 'type'
@hadShowDbPriv:=1
1
1
1
1
ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 137: Duplicate column name 'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 140: Duplicate column name 'Create_tmp_table_priv'
ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor'
@hadCreateViewPriv:=1
1
1
1
1
ERROR 1060 (42S21) at line 252: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 253: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 254: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 259: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 260: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 261: Duplicate column name 'Show_view_priv'
@hadCreateRoutinePriv:=1
1
1
1
1
ERROR 1060 (42S21) at line 282: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 283: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 284: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 289: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 290: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 291: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 293: Duplicate column name 'Execute_priv'
ERROR 1060 (42S21) at line 294: Duplicate column name 'Execute_priv'
ERROR 1060 (42S21) at line 306: Duplicate column name 'max_user_connections'
@hadCreateUserPriv:=1
1
1
1
1
ERROR 1060 (42S21) at line 315: Duplicate column name 'Create_user_priv'
ERROR 1064 (42000) at line 472: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use
 near 'CHARACTER SET utf8' at line 42
ERROR 1067 (42000) at line 516: Invalid default value for 'modified'
ERROR 1067 (42000) at line 518: Invalid default value for 'modified'