Hello.
See:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
David Godsey wrote:
> 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
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
