|
Home > Archive > MySQL ODBC Connector > January 2006 > Error 2016 and 2013
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Error 2016 and 2013
|
|
| David Godsey 2006-01-24, 1:23 pm |
| 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 MajorFrameDescriptio
n
WHERE name=n
INTO mfid,pid,foffset,fle
ngth,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=r
fid
INTO ptime,fdata;
call toBigInt(fdata,fdata
_bigint);
IF (foffset %8) >0 THEN
SET @mask_off=foffset%8;
call mask_data(fdata,@mas
k_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_para
m)) THEN
call polyConv(fdata_float
,conv_param,fdata_fl
oat);
END IF;
SET @fdata_converted=fda
ta_float;
WHEN "Double"
THEN
call toFloat(fdata_bigint
,fdata_float);
IF(!ISNULL(conv_para
m)) THEN
call polyConv(fdata_float
,conv_param,fdata_fl
oat);
END IF;
SET @fdata_converted=fda
ta_float;
WHEN "Signed"
THEN
call toSigned(fdata_bigin
t,fdata_signed);
SET @fdata_converted=fda
ta_signed;
WHEN "Unsigned"
THEN
SET @fdata_converted=fda
ta_bigint;
ELSE
SET @fdata_converted=HEX
(fdata);
END CASE;
call enumConv(fdata_bigin
t,mfid,@fdata_enum);
IF(!ISNULL(@fdata_en
um)) THEN
SET @fdata_converted=@fd
ata_enum;
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(toin
t BIGINT UNSIGNED);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+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(toin
t INT UNSIGNED);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+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(toin
t INT);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+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(tofl
oat DOUBLE);
SET @q=CONCAT('INSERT INTO make_conversion set
tofloat=(',@string_d
ata,'+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,to
p_int);
SELECT ((top_int << mask_off) & 0xFF) >> mask_off INTO top_int;
SET @string_data=CONCAT(
'0x',HEX(top_int),HE
X(SUBSTR(BINARY(fdat
a),2)));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t INT);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+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,PO
SITION('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/mysql? unsub...sie.nctu.edu.tw
| |
| Gleb Paharenko 2006-01-26, 4:56 pm |
| 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 MajorFrameDescriptio
n
> WHERE name=n
> INTO mfid,pid,foffset,fle
ngth,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(fra
me_data),
> FLOOR(foffset/8)+1,
> CEIL((flength + (foffset %8 ))/8))
> FROM RawMajorFrames
> WHERE raw_major_frame_id=r
fid
> INTO ptime,fdata;
>
> call toBigInt(fdata,fdata
_bigint);
> IF (foffset %8) >0 THEN
> SET @mask_off=foffset%8;
> call mask_data(fdata,@mas
k_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(con
v_param)) THEN
> call polyConv(fdata_float
,conv_param,fdata_fl
oat);
> END IF;
> SET @fdata_converted=fda
ta_float;
>
> WHEN "Double"
> THEN
> call toFloat(fdata_bigint
,fdata_float);
> IF(!ISNULL(con
v_param)) THEN
> call polyConv(fdata_float
,conv_param,fdata_fl
oat);
> END IF;
> SET @fdata_converted=fda
ta_float;
>
> WHEN "Signed"
> THEN
> call toSigned(fdata_bigin
t,fdata_signed);
> SET @fdata_converted=fda
ta_signed;
> WHEN "Unsigned"
> THEN
> SET @fdata_converted=fda
ta_bigint;
> ELSE
> SET @fdata_converted=HEX
(fdata);
> END CASE;
> call enumConv(fdata_bigin
t,mfid,@fdata_enum);
> IF(!ISNULL(@fdata
_enum)) THEN
> SET @fdata_converted=@fd
ata_enum;
> 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(toin
t BIGINT UNSIGNED);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+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(toin
t INT UNSIGNED);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+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(toin
t INT);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+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(tofl
oat DOUBLE);
> SET @q=CONCAT('INSERT INTO make_conversion set
> tofloat=(',@string_d
ata,'+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,to
p_int);
> SELECT ((top_int << mask_off) & 0xFF) >> mask_off INTO top_int;
> SET @string_data=CONCAT(
'0x',HEX(top_int),HE
X(SUBSTR(BINARY(fdat
a),2)));
> DROP TEMPORARY TABLE IF EXISTS make_conversion;
> CREATE TEMPORARY TABLE make_conversion(toin
t INT);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+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,PO
SITION('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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ 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/mysql? unsub...sie.nctu.edu.tw
| |
|
|
|
|
|