If you are using a 7.3.x.x version you may want to check the set field sizes vs. the actual data length in the field. I had a situation where the amount of data stored in the field was longer than the defined size. Upon fetch the query would not return any data. Once I set the offending field's size to be greater than the data contained in it the query returned data as expected.
-----Original Message----- From: Filip Sergeys [mailto:[EMAIL PROTECTED] Sent: Thursday, December 18, 2003 4:05 AM To: [EMAIL PROTECTED] Subject: strange record length limitation in select query I found a strange limitation on record length in a select query which I like to understand. I know there is a row limitation of 8088 bytes, but I thought that was on storage, not on retrieval. The query is a join of 3 tables, one of these tables, t_messagelng, has a column type varchar(8000). The following query fails: SELECT a.idx as pidx, c.txt as mtxt FROM common.t_parameter as a, common.t_message as b, common.t_messagelng as c WHERE b.seqmsg = c.seqmsg AND a.msglabel = b.nam AND a.groupid = 'VR_PARKING' AND c.seqlng = 2 AND length(a.msglabel) > 2 The error: Auto Commit: On, SQL Mode: Internal, Isolation Level: Repeatable General error;-9008 POS(1) System error: BD Illegal record. After doing all kinds of rearranging where clauses that didn't work, I tested the query with a substr clause on the txt column: SELECT a.idx as pidx, substr(c.txt,1,7981) as mtxt FROM common.t_parameter as a, common.t_message as b, common.t_messagelng as c WHERE b.seqmsg = c.seqmsg AND a.msglabel = b.nam AND a.groupid = 'VR_PARKING' AND c.seqlng = 2 AND length(a.msglabel) > 2 Hurray it worked, and this is the output:(also far less than 8088 bytes) pidx mtxt ------------------------- 0 Afgesloten garage 2 Open garage 3 Openbare weg The length may not be more than 7981 bytes, less works, more not. However; select txt from t_messagelng generates no error and outputs the entire column length Question: What is the mechanism behind this. Is there a way to calculate that value or to foresee errors, because some records are really 8000 bytes long and we need the complete record. Is there a plan to overcome this limitation in future releases. Thanks, Cheers, Filip Sergeys -- *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* * System Engineer, Verzekeringen NV * * www.verzekeringen.be * * Oostkaai 23 B-2170 Merksem * * 03/6416673 - 0477/340942 * *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
