David, Are you using persistent connections? Sounds like perhaps a persistent connection is timing out.
Maybe a quick work around would be to call a "check status" routine (ie - do a show status), just to see if the connection is still there. If this fails, just do a mysql_connect... Before continuing. -- George -----Original Message----- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 4:09 PM To: mysql@lists.mysql.com Subject: Lost connection to MySQL server during query 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]