MySQL Bugs: #14569: editing a stored procedure kills mysqld-nt

Bug #14569	editing a stored procedure kills mysqld-nt
Submitted:	2 Nov 2005 10:36	Modified:	20 Nov 2005 3:43
Reporter:	Hari Kumar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.15	OS:	Windows (Windows)
Assigned to:	Sergey Vlasenko	CPU Architecture:	Any

Description:
Create a stored procedure.
write the line 'Select * from <dbname>' between begin and end.

-- no table name is given here as required. At the first isntance mysql does not parse the query and try whether it is right or not.  The sp is created.  

Try editing the sp. Windows kills when mysqld-nt tries dropping the proc and recreating it.

How to repeat:
as above

Thank you for a bug report. The problem is not with MySQL Adminsitrator, but with the server (5.0.15-nt). I had connected with MySQL Administrator 1.1.4 to local MySQL server 5.0.15, on XP, as root, and moved to the test schema (to try to create the procedure, as you described). I've got a Windows dialog box about killing mysqld-nt.exe immediately.

In the other window, in mysql command line client, at the same time I've got the following resutls:

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+-----------------------+
| Id | User | Host           | db   | Command | Time | State | Info     |
+----+------+----------------+------+---------+------+-------+-----------------------+
|  1 | root | localhost:3351 | NULL | Sleep   |   24 |       | NULL     |
|  3 | root | localhost:3353 | NULL | Query   |    0 | NULL  | show processlist     |
|  6 | root | localhost:3356 | test | Query   |   22 | NULL  | SHOW PROCEDURE STATUS |
+----+------+----------------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

Then I click the button on the Windows dialog box (not send anything to Microsoft...) and:

mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
ERROR: 
Can't connect to the server

And then I've got a list of tables, had a change to create a procedure in MySQL Admin, and, upon trying to execute it, I've got the same error message, this time in MySQL Administrator...

The simplest way to repeat the bug is the following:

C:\Documents and Settings\openxs>mysql -uroot -p -P3307
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt

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

mysql> use test;
Database changed
mysql> show procedure status;
ERROR 2013 (HY000): Lost connection to MySQL server during query.

There may be something incorrect in our schemas, so while I am checking on fresh installation, would you, please, describe the steps you perfromed to install MySQL server.

I installed the server from the windows binaries available at dev.mysql.com.   The database on which i tried to create the sp is one that I copied from the older installation. (ibdata.. file)

Verified just as you described initially. The steps to repeat are the following:

1. Install fresh copy of MySQL server version 5.0.15 (the default installation directory should NOT contain data from any previous installations).

2. Connect using mysql client (test database is empty):

C:\Documents and Settings\openxs>mysql -uroot -p -P3307
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.15-nt

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

mysql> use test;
Database changed
mysql> show procedure status;
Empty set (0.08 sec)

mysql> show tables like '%';
Empty set (0.00 sec)

3. Then connect using MySQAL Administrator 1.1.4, choose Catalogs / test from the left pane, then choose Stored Procedures tab and click Create Stored Proc.

4. Enter sp1 as procedure name, and input the following as the only statement in the procedure:

select * from test;

and click Execute SQL button. Yes, there is no such a table in the test database, but first time it "works".

5. Then (you noted the error and try to fix it) click on the Edit Stored Proc button and add a database name ("test." before the table name), then click Execute SQL.

At this moment you are getting a crash, a dialog box saying that mysqld-nt.exe should be stopped.

6. Now in the mysql command line session already opened:

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------------------------------------------------------+
| Id | User | Host           | db   | Command | Time | State | Info                                                |
+----+------+----------------+------+---------+------+-------+------------------------------------------------------------------+
|  3 | root | localhost:3974 | test | Query   |    0 | NULL  | show processlist                                                |
|  4 | root | localhost:4163 |      | Query   |   15 | NULL  | CREATE PROCEDURE
`sp1`()
BEGIN
  select * from test.test;
END |
+----+------+----------------+------+---------+------+-------+------------------------------------------------------------------+
2 rows in set (0.01 sec)

7. Confirm stopping the mysqld-nt.exe.

8. Try once more in mysql client to confirm the crash:

mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
ERROR: 
Can't connect to the server

mysql> exit
Bye

After service restart:

C:\Documents and Settings\openxs>mysql -uroot -p -P3307
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt

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

mysql> use test;
Database changed
mysql> show procedure status;
Empty set (0.01 sec)

I was not able yet to repeat this bug using simpler set of steps. But I'll keep trying.

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/internals/32183

Remaining part of the problem in mySQL Administrator application.
When editing sp it does not pass current db to server, so if when editing
it is specified create procedure sp_name()... (default case for editing)
server does not know for which db sp is created. 

For the case of initial creation default value in MySQl Administrator is "create procedure db_name.sp_name()..."

After fix in mysqld, current version of MySQL Admin will return No DB specified error.

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/internals/32190

Withdraw last comment. Problem is indeed in mysqld and only there.

Patch is available in 5.0.17

Noted in 5.0.17 changelog.