I got exactly that error message last night when doing a numeric comparison on a varchar column. Oops. Kind of misleading, though.
----- Original Message ----- From: "David Godsey" <[EMAIL PROTECTED]> To: "George Law" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Tuesday, January 24, 2006 4:43 PM Subject: RE: Lost connection to MySQL server during query > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
