Thanks for the reply. I'm not using persistend connections though. It appears that it looses the connection in the middle of the query or in other words, before the procedure returns. So that means I not getting the data I need. So for debug purposes, are you saying to do a "check status" from PHP or in the procedure? From the procedure it wouldn't do any good right? From PHP it would be after I didn't get the data, so I would have to reconnect and rerun the query. That won't really work for me either.
David Godsey > 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] > > 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]
