I am getting this error when connecting to mysql with PHP:
Lost connection to MySQL server during query
This happens only when I use this procedure, but it doesn't necessarily
fail when this procedure is called. The error will happen frequently,
however it is not consistent. This is my first procedure I've written, so
I'm sure I've done something wrong here. I assume the error message means
I'm hitting some kind of timeout?
Any ideas would be welcome. Thanks.
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
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]
