nativecode=2013 ** Lost connection to MySQL server during query
I think 2016 is Lost database connection
What I am doing is I have a PHP class object that calls a stored
procedure. I don't think I'm hitting any timeouts because it happens in
less than a second. So here is my stored procedure:
create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE)
BEGIN
DECLARE mfid INT UNSIGNED;
DECLARE pid INT UNSIGNED;
DECLARE foffset INT UNSIGNED;
DECLARE flength INT UNSIGNED;
DECLARE vid INT UNSIGNED;
DECLARE rfid INT UNSIGNED;
DECLARE tpid INT UNSIGNED;
DECLARE fdata BLOB;
DECLARE fdata_tmp BLOB;
DECLARE fdata_bigint BIGINT UNSIGNED;
DECLARE fdata_signed INT;
DECLARE fdata_unsigned INT UNSIGNED;
DECLARE fdata_float DOUBLE;
DECLARE data_type VARCHAR(20);
DECLARE byte_order VARCHAR(20);
DECLARE conv_param VARCHAR(255);
SELECT major_frame_desc_id, parent_id, frame_offset,
frame_length,
version_id, top_level_parent_id
FROM MajorFrameDescription
WHERE name=n
INTO mfid,pid,foffset,flength,vid,tpid;
SELECT attribute_value FROM MajorFrameAttributes
WHERE major_frame_desc_id=mfid AND
attribute_name="NormalizedType"
INTO data_type;
SELECT attribute_value FROM MajorFrameAttributes
WHERE major_frame_desc_id=mfid AND attribute_name="ConvParams"
INTO conv_param;
SELECT attribute_value FROM MajorFrameAttributes
WHERE major_frame_desc_id=mfid AND attribute_name="ByteOrder"
INTO byte_order;
SELECT MAX(raw_major_frame_id)
FROM RawMajorFrames
WHERE major_frame_desc_id=tpid
INTO rfid;
IF rfid >0 THEN
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
call toBigInt(fdata,fdata_bigint);
IF (foffset %8) >0 THEN
SET @mask_off=foffset%8;
call mask_data(fdata,@mask_off,fdata_bigint);
END IF;
IF (8-((flength+(foffset%8)) %8)) > 0 THEN
SELECT (fdata_bigint >>
(8-((flength+(foffset%8)) %8))) INTO
fdata_bigint;
END IF;
CASE data_type
WHEN "Float"
THEN
call
toFloat(fdata_bigint,fdata_float);
IF(!ISNULL(conv_param)) THEN
call
polyConv(fdata_float,conv_param,fdata_float);
END IF;
SET
@fdata_converted=fdata_float;
WHEN "Double"
THEN
call
toFloat(fdata_bigint,fdata_float);
IF(!ISNULL(conv_param)) THEN
call
polyConv(fdata_float,conv_param,fdata_float);
END IF;
SET
@fdata_converted=fdata_float;
WHEN "Signed"
THEN
call
toSigned(fdata_bigint,fdata_signed);
SET
@fdata_converted=fdata_signed;
WHEN "Unsigned"
THEN
SET
@fdata_converted=fdata_bigint;
ELSE
SET @fdata_converted=HEX(fdata);
END CASE;
call enumConv(fdata_bigint,mfid,@fdata_enum);
IF(!ISNULL(@fdata_enum)) THEN
SET @[EMAIL PROTECTED];
END IF;
SELECT
mfid AS major_frame_desc_id,
n AS name,
pid AS parent_id,
tpid AS top_level_parent_id,
rfid AS raw_major_frame_id,
foffset AS frame_offset,
flength AS frame_length,
vid AS version_id,
ptime AS payload_time,
HEX(fdata) AS raw_data,
@fdata_converted AS converted_data;
ELSE
SELECT rfid;
END IF;
END
Some procedures it uses are:
CREATE PROCEDURE toBigInt (IN fdata BLOB,OUT fdata_int BIGINT UNSIGNED)
BEGIN
SET @string_data=CONCAT('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toint BIGINT UNSIGNED);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_data,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toUnsigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT
UNSIGNED)
BEGIN
SET @string_data=CONCAT('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toint INT UNSIGNED);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_data,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toSigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT)
BEGIN
SET @string_data=CONCAT('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toint INT);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_data,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toFloat (IN fdata BIGINT UNSIGNED,OUT fdata_float DOUBLE)
BEGIN
SET @string_data=CONCAT('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(tofloat DOUBLE);
SET @q=CONCAT('INSERT INTO make_conversion set
tofloat=(',@string_data,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT tofloat from make_conversion into fdata_float;
END
$$
CREATE PROCEDURE shift_right(IN fdata_bigint BIGINT UNSIGNED,IN shift INT
UNSIGNED,OUT fdata_bigint2 BIGINT UNSIGNED)
BEGIN
SELECT fdata_bigint >> shift INTO fdata_bigint2;
END
$$
CREATE PROCEDURE mask_data(IN fdata BLOB,IN mask_off INT UNSIGNED,OUT
fdata_bigint2 BIGINT UNSIGNED)
BEGIN
DECLARE top_byte TINYBLOB;
DECLARE top_int BIGINT UNSIGNED;
SELECT SUBSTR(BINARY(fdata),1,1) INTO top_byte;
call toBigInt(top_byte,top_int);
SELECT ((top_int << mask_off) & 0xFF) >> mask_off INTO top_int;
SET
@string_data=CONCAT('0x',HEX(top_int),HEX(SUBSTR(BINARY(fdata),2)));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toint INT);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_data,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint FROM make_conversion INTO fdata_bigint2;
END
$$
CREATE PROCEDURE polyConv(IN fdata DOUBLE,IN conv_param VARCHAR(255),OUT
fdata_converted DOUBLE)
BEGIN
DECLARE beginning VARCHAR(255);
DECLARE end_of VARCHAR(255);
DECLARE query VARCHAR(255);
SELECT SUBSTR(conv_param,1,POSITION('x' in conv_param)-1) INTO
beginning;
SELECT SUBSTR(conv_param,POSITION('x' in conv_param)+1) INTO
end_of;
SET @string_query=CONCAT("SELECT ",beginning,fdata,end_of,"INTO
@fdata_converted");
PREPARE st1 FROM @string_query;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT @fdata_converted INTO fdata_converted;
END
$$
CREATE PROCEDURE enumConv(IN fdata INT UNSIGNED,mfid INT UNSIGNED,OUT
fdata_converted
VARCHAR(100))
BEGIN
SELECT name FROM EnumLiterals
WHERE major_frame_desc_id=mfid AND value=fdata
INTO fdata_converted;
END
Are there some error cases I'm failing to catch and that is the cause of
the apparent database connection loss?
BTW: these are the first procedures I've writting so I'm a novice with
stored procedures.
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
