| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.4-beta-nt | OS: | Windows (Windows 2000 service pack 4) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[14 Jun 2005 10:34]
MySQL Verification Team
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
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