Description:
Upon creation and execution of a stored procedure, I encountered the following errors:
0 3 16:18:25 CALL convert_discount_data() Error Code: 2013
Lost connection to MySQL server during query 30.531 sec
0 4 16:18:56 CALL convert_discount_data() Error Code: 2006
MySQL server has gone away
How to repeat:
DELIMITER $$
DROP PROCEDURE IF EXISTS convert_discount_data
$$
CREATE PROCEDURE `convert_discount_data`()
BEGIN
DECLARE done INT DEFAULT 0;
# Discount information.
DECLARE o_id, p_id, d_type, d_id, d_ver, d_key INT(11) DEFAULT 0;
DECLARE p_active, d_pct tinyint(1) DEFAULT FALSE;
DECLARE d_data, d_rate, o_name VARCHAR(255);
DECLARE d_value DOUBLE DEFAULT 0;
DECLARE d_usage INT DEFAULT 0;
# Tracking variables.
DECLARE prev_org, prev_type INT(11) DEFAULT 0;
DECLARE prev_data VARCHAR(255) DEFAULT '';
# Main Cursor for data collection.
DECLARE data_cursor CURSOR FOR SELECT ...;
# Exit Handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET SQL_SAFE_UPDATES = 0;
OPEN data_cursor;
REPEAT
FETCH data_cursor INTO o_id, d_type, d_data, d_rate, p_active;
IF done != 1
THEN
IF o_id IS NOT NULL
THEN
# Execute the updates based on cursor values...
END IF;
SET prev_org = o_id;
SET prev_type = d_type;
SET prev_data = d_data;
END IF;
UNTIL done END REPEAT;
CLOSE data_cursor;
# Display summary for visual verifcation.
SELECT ...;
END
$$
DELIMITER ;
CALL convert_discount_data();
DROP PROCEDURE IF EXISTS convert_discount_data;
Suggested fix:
Unknown... didn't happen in version 5.2.26. Just updated to 5.2.29 to get the fix for Bug#55833 and the problem started occurring.
Description: Upon creation and execution of a stored procedure, I encountered the following errors: 0 3 16:18:25 CALL convert_discount_data() Error Code: 2013 Lost connection to MySQL server during query 30.531 sec 0 4 16:18:56 CALL convert_discount_data() Error Code: 2006 MySQL server has gone away How to repeat: DELIMITER $$ DROP PROCEDURE IF EXISTS convert_discount_data $$ CREATE PROCEDURE `convert_discount_data`() BEGIN DECLARE done INT DEFAULT 0; # Discount information. DECLARE o_id, p_id, d_type, d_id, d_ver, d_key INT(11) DEFAULT 0; DECLARE p_active, d_pct tinyint(1) DEFAULT FALSE; DECLARE d_data, d_rate, o_name VARCHAR(255); DECLARE d_value DOUBLE DEFAULT 0; DECLARE d_usage INT DEFAULT 0; # Tracking variables. DECLARE prev_org, prev_type INT(11) DEFAULT 0; DECLARE prev_data VARCHAR(255) DEFAULT ''; # Main Cursor for data collection. DECLARE data_cursor CURSOR FOR SELECT ...; # Exit Handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET SQL_SAFE_UPDATES = 0; OPEN data_cursor; REPEAT FETCH data_cursor INTO o_id, d_type, d_data, d_rate, p_active; IF done != 1 THEN IF o_id IS NOT NULL THEN # Execute the updates based on cursor values... END IF; SET prev_org = o_id; SET prev_type = d_type; SET prev_data = d_data; END IF; UNTIL done END REPEAT; CLOSE data_cursor; # Display summary for visual verifcation. SELECT ...; END $$ DELIMITER ; CALL convert_discount_data(); DROP PROCEDURE IF EXISTS convert_discount_data; Suggested fix: Unknown... didn't happen in version 5.2.26. Just updated to 5.2.29 to get the fix for Bug#55833 and the problem started occurring.