MySQL Bugs: #4163: Connection lost with MySQL server

Bug #4163	Connection lost with MySQL server
Submitted:	16 Jun 2004 12:27	Modified:	18 Jul 2004 17:16
Reporter:	marouane dehbi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	alpha 5.0.0	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
The problem we encounter is the following:
We have developped a stored procedure and we want to test it through the mySQL client. To do so, we connect the client to the mySQL server and we call the procedure PSP_S_SOFTLIST. This stored procedure erases all the lines in a table before inserting a certain number of lines in it, corresponding to our wanted result.
When this procedure is executed the first time after a connection, it works, but when we execute it a second time and even though we are still connected to the server it fails and the server crash. The error message we get is "Lost connection to mySQL server during query".

To sum up, the procedure can only be executed once after a connection to the server when some lines are inserted in our result table and it can be executed as many times as we want without crashing the server when no lines are inserted in our result table.

All our tables are of type INNODB. The system variable "wait_timeout = 28800".

We provide you the inputs of our procedure that is to say the tables we use and also the procedure itself.

TABLE 1:

# [admintmsi] Query Window
# Connection: admintmsi
# Host: tms-srv
# Saved: 2004-06-16 13:02:12
# 
# Query:
# SELECT *
# FROM `tbl_tpesoft`
# 
'TPESOFTNAME','TPESOFTDATE','TPEID','TPESOFTCRC','TPESOFTTYPE','TPESOFTSIZE','TPESOFTFILECOUNTER','TPESOFTFILELENGTH'
'0650211','2004-06-15 15:49:17','1','[NULL]','[NULL]','[NULL]','[NULL]','[NULL]'

TABLE 2:
# [admintmsi] Query Window
# Connection: admintmsi
# Host: tms-srv
# Saved: 2004-06-16 13:03:22
# 
# Query:
# SELECT *
# FROM `tbl_catalog`
# 
'SOFTNAME','TPEPRODUCTNAME','SEGSOFTNAME','SEGCRC','SEGCOMPRESSED','SEGIDNAME','SEGLOADADDRESS','SEGTYPE','SEGSOFTSIZE','SEGDATASIZE','SEGFILESIZE','SEGFILELENGTH'
'0650207','M30','30650207.bin','C312','N','30650207.ADF','0','0','65536','57344','0','119468'
'8080207','M30','38080207.bin','B001','N','38080207.ADF','0','0','151552','20480','0','160052'
'8130107','M30','38080207.bin','B001','N','38080207.ADF','0','0','151552','20480','0','160052'
'8130110','M30','38130110.sgn','FA64','Y','38130110.adf','0','0','164832','65536','0','113256'

TABLE 3:
# [admintmsi] Query Window
# Connection: admintmsi
# Host: tms-srv
# Saved: 2004-06-16 13:02:59
# 
# Query:
# SELECT *
# FROM `tbl_software`
# 
'SOFTNAME','SOFTTYPNUM','SOFTSUBSTITUTE','SOFTDIFENABLE','SOFTSUBSTNAME','SOFTSHORTCOMMENT','SOFTCOMMENT'
'0650207','225','N','Y','[NULL]','Module EMV',' CB EMV'
'8080207','225','N','Y','[NULL]',' CB EMV',' CB EMV'
'8130107','229','N','Y','[NULL]','DerniŠre version 813','DerniŠre version du logiciel 8130110'
'8130110','305','N','Y','[NULL]','CBEMV V5.2','CBEMV V5.2'
'<Term Prm>','0','G','N','','',''
'[None]','0','U','N','','',''

THE PROCEDURE:

USE ADMINTMSI;

DROP PROCEDURE  IF EXISTS  PSP_S_SOFTLIST;

Delimiter  //  

CREATE PROCEDURE PSP_S_SOFTLIST(IN l_levelid   NUMERIC,
                                IN s_productname text)
BEGIN	
                  set AUTOCOMMIT=0;
                  delete from ADMINTMSI.TBLTMP_SOFTLIST;
                  commit;
                  INSERT INTO ADMINTMSI.TBLTMP_SOFTLIST
                        (            priority1,
                                     priority2,
                                     softname,
                                     softtype,
                                     loadmode,
                                     loadaddress,
                                     segidname,
                                     segsoftname,
                                     compressed
                        )	

                    SELECT tpesoftpriority, 1, soft.softname,
                           soft.softtypnum, loadmode, cat.segloadaddress,
                           cat.segidname, cat.segsoftname,
                           cat.segcompressed
                      FROM ADMINTMSI.TBL_TPE_SOFT ts, ADMINTMSI.TBL_SOFTWARE soft, ADMINTMSI.TBL_CATALOG cat
                     WHERE tpeid = l_levelid
                       AND ((    ts.softname = soft.softname
                             AND cat.softname = ts.softname
                             AND cat.tpeproductname = s_productname
                             AND soft.softdifenable = 'Y')
                            OR (    SUBSTRING(ts.softname,length(ts.softname) , 1) = '#'
                                AND soft.softname =
                                       (
					SELECT MAX (TBL_SOFTWARE.softname)
                                          FROM ADMINTMSI.TBL_SOFTWARE, ADMINTMSI.TBL_CATALOG
                                         WHERE SUBSTRING (TBL_SOFTWARE.softname, 1, length(TBL_SOFTWARE.softname) - 4) =
                                                  SUBSTRING (ts.softname, 1, length(ts.softname) - 1)
							   AND TBL_SOFTWARE.SOFTNAME = TBL_CATALOG.SOFTNAME
						       AND TBL_CATALOG.tpeproductname = s_productname
                                           AND softdifenable = 'Y')
                                AND cat.softname = soft.softname
                                AND cat.tpeproductname = s_productname
                                AND soft.softdifenable = 'Y'
                                )
                           );

commit;	

END

//

Delimiter ;

How to repeat:
To repeat the bug you have to connect to the server and then execute the procedure twice.

Suggested fix:
None

Could you please provide a test case script (file attached) with your tables
structure with insert commands. If you want you can upload the file at:

ftp://ftp.mysql.com/pub/mysql/upload

and let me know here when done.

Thanks in advance.

Table structure and data examples

Attachment: dump.txt (text/plain), 110.58 KiB.

You can find the table structure and data concerning the bug we reported in  the enclosed file that we generated with MySQLDump.
Thank you for your help.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".