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 the 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
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 the 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