I just discovered a very scary behavior. We're using MySQL 4.0.1 on Linux,
both tables are InnoDB, AUTO_COMMIT is on (default) and these queries are
being issues from the MySQL interactive console.
If I do a query of the form:
SELECT
transaction_report.*,
confirmed
FROM
transaction_report,
user
WHERE
user_id = user.id AND
DATE_FORMAT(when_reported, '%Y-%m-%d') = '2002-06-21' AND
transaction_report.partner_id = 1 AND
confirmed != 1
LIMIT
50;
And then a query of the form:
DELETE transaction_report FROM transaction_report, user WHERE user_id =
user.id AND confirmed != 1;
Only 50 rows will be deleted. If I reissue the DELETE, it will delete 50
more rows. If I reissue the SELECT with a LIMIT 20, then the DELETE
statements will remove 20 rows.
This seems to be limited to a single connection handle (issuing the SELECT
in one MySQL console doesn't cause problems with the DELETE in a second
console...).
-JF
---------------------------------------------------------------------
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
