Bug #11308 Lost connection to MySQL server during query
Submitted: 14 Jun 2005 8:02 Modified: 11 Jul 2005 6:24
Reporter: Rahul Chaudhari Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.4-beta-nt OS:Microsoft Windows (Windows 2000 service pack 4)
Assigned to: Konstantin Osipov CPU Architecture:Any

[14 Jun 2005 8:02] Rahul Chaudhari
Description:
Hi All,

       i'm getting the following error message..

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

    when i'm  calling procedure inside the procedure.

my inside procedure inserts one reocrd into table and gives out parameter as table name. and the main procedure creates the table with the inserted table name.

 issue here is 

i'm using prepare stmt in the main procedure and executing the procedure.

procedure executes properly but when i'm calling procedure as...

call Genrate_Table(@o_TableName);
it throws the error lost connection....

but when i'm selecting the parameter as 
select (@o_TableName);

it show the result also when i'm checking the table as

show tables;

the tale is created successfuly;

then why these connection close error is throwing..

How to repeat:
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `snox`.`Genrate_Table`$$
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE PROCEDURE `snox`.`Genrate_Table`
    -> (
    ->  OUT     o_Table_Name            VARCHAR(30),
    ->  OUT             o_OutputStatus          INTEGER,
    ->  OUT             o_OutputMessage         VARCHAR(100)
    -> )
    -> BEGIN
    ->  DECLARE v_Table_Name            VARCHAR(30);
    ->  DECLARE v_Tbl_Name                      VARCHAR(30);
    ->  DECLARE v_TName                     VARCHAR(30);
    ->  DECLARE v_TINT                      INTEGER;
    ->  DECLARE v_TNint                     INTEGER;
    ->  DECLARE o_TableName                     VARCHAR(30);
    ->  DECLARE v_INTableName           VARCHAR(30);
    ->
    ->  DECLARE V_No_Data                   INTEGER DEFAULT 1;
    ->  DECLARE No_Data_Found   CONDITION FOR 1329;
    ->  DECLARE CONTINUE HANDLER FOR No_Data_Found SET v_No_Data  = -1;
    ->
    -> #        SET o_OutputMessage     = 'LEFT FROM PROC';
    ->  BEGIN
    ->          CALL Gen_Table_Name(v_INTableName);
    ->          SET v_Table_Name = v_INTableName;
    ->  END;
    ->
    ->  set @s = concat('CREATE TABLE ', upper(v_Table_Name), '(USER_ID VARCHAR(20),USER_IP VARCHAR(20), Login_Time DATE,
    '>                                                                                                                    Logout_Time DATE, Session_Id VARCHAR(20), Server_Ip VARCHAR(20))');
    ->
    ->  SET o_Table_Name        = v_Table_Name;
    ->  SET     o_OutputStatus  = 0;
    ->  SET o_OutputMessage = 'SUCCESS';
    ->
    ->  prepare stmt from @s;
    ->  execute stmt;
    ->  deallocate prepare stmt;
    -> END $$
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER ;

mysql> call Genrate_Table (@o_Table_Name,@o_OutputStatus,@o_OutputMessage);
ERROR 2013 (HY000): Lost connection to MySQL server during query
[14 Jun 2005 10:34] Victoria Reznichenko
Hello,

Thank you for the report, but we need the procedure Gen_Table_Name too, to reproduce the problem.
[14 Jun 2005 10:53] Rahul Chaudhari
DELIMITER $$

DROP PROCEDURE IF EXISTS `usermgm`.`Gen_Table_Name`$$
CREATE PROCEDURE `usermgm`.`Gen_Table_Name`(
	OUT 	o_TableName  			VARCHAR(30)
)
BEGIN
	DECLARE	v_Tbl_Name		 	VARCHAR(30);
	DECLARE v_TName			 	VARCHAR(30);
	DECLARE v_TINT			 	INTEGER;
	DECLARE v_TNint			 	INTEGER;
	
	DECLARE V_No_Data		    INTEGER DEFAULT 1;	
	
	DECLARE No_Data_Found   CONDITION FOR 1329;
	
	DECLARE CONTINUE HANDLER FOR No_Data_Found SET v_No_Data  = -1;
	
	BEGIN
		SELECT Tbl_Name	INTO v_Tbl_Name	FROM stblname 
		  WHERE	Tbl_Id IN (SELECT MAX(Tbl_Id) FROM Stblname);
	
	    IF V_NO_DATA = -1 THEN	
	  	   SET V_No_Data = 1;
		   SET v_TINT = 123456789;
		ELSE
		   SET v_TINT = SUBSTR(v_Tbl_Name,8,30);
		END IF;

		SET v_TNint = v_TINT + 1;
		SET v_TName = Concat(Upper('SN_TEMP'),v_TNint);

		INSERT INTO stblname(Tbl_Name)
		  VALUES(Upper(v_TName));

		SET o_TableName = v_TName;
	END;    	
END$$

DELIMITER ;
[14 Jun 2005 10:56] Rahul Chaudhari
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `snox`.`Gen_Table_Name`$$
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE PROCEDURE `snox`.`Gen_Table_Name`(
    ->  OUT     o_TableName                     VARCHAR(30)
    -> )
    -> BEGIN
    ->  DECLARE v_Tbl_Name                      VARCHAR(30);
    ->  DECLARE v_TName                         VARCHAR(30);
    ->  DECLARE v_TINT                          INTEGER;
    ->  DECLARE v_TNint                         INTEGER;
    ->
    ->  DECLARE V_No_Data                   INTEGER DEFAULT 1;
    ->
    ->  DECLARE No_Data_Found   CONDITION FOR 1329;
    ->
    ->  DECLARE CONTINUE HANDLER FOR No_Data_Found SET v_No_Data  = -1;
    ->
    ->  BEGIN
    ->          SELECT Tbl_Name INTO v_Tbl_Name FROM stblname
    ->            WHERE Tbl_Id IN (SELECT MAX(Tbl_Id) FROM Stblname);
    ->
    ->      IF V_NO_DATA = -1 THEN
    ->             SET V_No_Data = 1;
    ->             SET v_TINT = 123456789;
    ->          ELSE
    ->             SET v_TINT = SUBSTR(v_Tbl_Name,8,30);
    ->          END IF;
    ->
    ->          SET v_TNint = v_TINT + 1;
    ->          SET v_TName = Concat(Upper('SN_TEMP'),v_TNint);
    ->
    ->          INSERT INTO stblname(Tbl_Name)
    ->            VALUES(Upper(v_TName));
    ->
    ->          SET o_TableName = v_TName;
    ->  END;
    -> END$$
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER ;
mysql>
[15 Jun 2005 8:16] Vasily Kishkin
Could you please provide a definition of table stblname ?
[15 Jun 2005 8:48] Rahul Chaudhari
mysql> delimiter ;

mysql> drop table if exists stblname ;
Query OK, 0 rows affected (0.16 sec)

mysql> create table stblname(tbl_id integer, Tbl_Name varchar(30));
Query OK, 0 rows affected (0.06 sec)

mysql>
[15 Jun 2005 9:17] Vasily Kishkin
Thanks for bug report ! I was able to reproduce it.
Tested on Win 2000 Sp4, MySQL server 5.0.7 beta.
I attached sql file with queries.
[15 Jun 2005 9:18] Vasily Kishkin
query file

Attachment: test.sql (text/plain), 2.47 KiB.

[11 Jul 2005 6:24] Konstantin Osipov
Prepare commands were disabled in stored procedures in 5.0.
Dynamic SQL is scheduled to be added in a future release.

kostja@dragonfly:~> mysql5 test < test.sql
ERROR 1314 (0A000) at line 43: PREPARE is not allowed in stored procedures
kostja@dragonfly:~> mysql5 test < test.sql
ERROR 1314 (0A000) at line 43: PREPARE is not allowed in stored procedures
kostja@dragonfly:~> mysql5 test < test.sql
ERROR 1314 (0A000) at line 43: PREPARE is not allowed in stored procedures
kostja@dragonfly:~> mysql5 test < test.sql
ERROR 1314 (0A000) at line 43: PREPARE is not allowed in stored procedures