Frank, if the tables are InnoDB type, then the following patch might fix the problem. The patch will be in 3.23.54, which, I guess, will be released around December 5th, 2002.
" MySQL/InnoDB-4.0.5, November 18, 2002 ... Fixed a bug: if a SELECT was done with a unique key from a primary index, and the search matched to a delete-marked record, InnoDB could erroneously return the NEXT record. " If you re-run the problematic query, does it produce the correct result? The bug above was automatically corrected by the InnoDB purge after some time. Can you produce a repeatable test case? Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query ....................... Subject: Intermitent join problems From: Frank Tuvell Date: Tue, 19 Nov 2002 19:54:46 -0500 ---------------------------------------------------------------------------- ---- I'm using PHP 4.22, MySQL 3.23.53a on a RedHat Apache web server. I have a join that 99.9% of the time works fine but there have been 3 occasions in the last few weeks where extra records have found their way into the resulting dataset. This has happened during high load times. The join is as follows: select a.Username, b.MsgID, b.Subject, b.Body from Users a, Messages b where b.ForumID=$forumID and b.ParentID=0 and a.UserID=b.UserID order by $sortBy limit $startRec,$endRec On the 3 occasions that a problem occurred, records made their way into the dataset that didn't have a b.ForumID equal to $forumID. I've noticed a number of recent fixes listed for joins in the MySQL doc and am wondering if something has slipped by. Has anyone else seen this problem? Any ideas on how I could avoid it? Would changing the form of my join help (i.e. do the join in the FROM and not the WHERE clause with the JOIN keyword)? Any suggestions would be greatly appreciated! Regards, Frank Tuvell Atlanta, GA - USA --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
