MySQL Bugs: #15801: show procedure status - Error 2013 (HY000) lost connection to MySQL server

Bug #15801	show procedure status - Error 2013 (HY000) lost connection to MySQL server
Submitted:	16 Dec 2005 4:08	Modified:	17 Dec 2005 16:34
Reporter:	Peter Larb	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL 5.0.16-max-log	OS:	UNIX
Assigned to:		CPU Architecture:	Any

Description:
Refer Bug #4344

mysql> show procedure status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: Research

ERROR 2013 (HY000): Lost connection to MySQL server during query

Another screen shot:

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: Research

+--------------------+
| Database           |
+--------------------+
| information_schema |
| Research           |
| Research_New       |
| mysql              |
| research_new_dbo   |
| test               |
+--------------------+
6 rows in set (0.01 sec)

1. If I run "show procedure status" from the command line I expect to see all of my stored procedures.

All I get is an error:

Error 2013 (HY000) Lost connection to MySQL server during query

2. I can drop a previously created stored procedure entitled "test". And this returns me a message of "Query OK", 0 rows affected"

3. I can run a select qiuery, which returns me the correct data. "Select * from Documents" - excellent

So the only problem I have is with stored proecures.

And the problem is accross ALL of my databases.

If I go back to Navicat and try to view my stored procedures I get the above connection error.

Thankyou

Peter

How to repeat:
I think the issue was created by me using Navicat as I always work in Navicat.

Hi! was not able to reproduce this bug. Could you please check mysql error log if it contains any info about mysql errors?

mysql> show procedure status;
Empty set (0.02 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.16-max |
+------------+
1 row in set (0.00 sec)

Hi !

I also have often a "lost connection" message since MySLQ 5.0.15

My "./configure" command is :

CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/server/mysql --without-geometry --enable-thread-safe-client --enable-assembler --enable-local-infile --without-debug --without-docs --without-bench --with-extra-charsets=none --
without-isam --without-innodb --without-extra-tools --with-embedded-server --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-
charset=latin1 --with-collation=latin1_swedish_ci --with-character-set=latin1 --without-ndb-debug

The last MySQL version who worked fine is 5.0.13rc.

Versions 5.0.15 and 5.0.16 have the same problem.

I'm primarly using MySQL with PHP 5.1.1 and the problem appears only on unoptimised websites where HTML code and MySQL queries are mixed. On one site with all queries done at first and HTML code generated after, it always works fine even with the last MySQL versions.

Do you need more informations ?

Good evening Aleksey

I think the key to this problem is to actually try and create a stored proc without any "select" statement for example.

As I am still learning the MySQL syntax I was making some errors in Navicat and the next minute I had this problem which now doesn't allow me to connect to the database at all.

I will look into the logs as soon as I can

Thankyou

Peter

I was unable to repeat the behavior reported:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-debug

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

mysql> delimiter //
mysql> create procedure sp1()
    -> begin
    -> end//
Query OK, 0 rows affected (0.06 sec)

mysql> show procedure status//
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db   | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| test | sp1          | PROCEDURE | root@localhost | 2005-12-16 13:29:24 | 2005-12-16 13:29:24 | DEFINER       |         |
| test | sudoku_solve | PROCEDURE | root@localhost | 2005-12-14 11:05:11 | 2005-12-14 11:05:11 | DEFINER       |         |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.03 sec)

Good evening

I have copied an email sent to me by Navicat whereby they were able to repeat the error that I have reported.

Thankyou Peter

see below:

Hi Peter,

I have contacted our sales department and they will proceed refund for your purchase. We will send further email to inform you once it's done. 

The following case may be MySQL server's bug. Actually, MySQL server's stored procedure is a new function and may be not very stable.

If you create an "empty" stored procedure, it will result in "lost connection to mysql server...." error. The solution is to issue "Drop Procedure ``;" to remove the "empty" stored procedure. 

Strange Case Example : 
mysql> create procedure ``(OUT a INT) set a:=1;
Query OK, 0 rows affected (0.01 sec)

mysql> show procedure status;
Error: Lost connection to MySQL server during query

The table mysql.proc can work properly even after such problem. We think you may try to browse that table and drop all the shown procedure manually by command that may also solve the problem.

Anyway, although you insist to have refund of our product, we also hope you can continue your work. In future, you may post the problem in MySQL's forum and MySQL engineers may provide answers to your questions.

best regards,

Ken 

Ticket Details

see bug #47032