Jon, ----- Original Message ----- From: ""Jon Frisby"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, June 22, 2002 12:51 AM Subject: Nasty LIMIT/multi-table DELETE bug?
> 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. there were several bugs in multi-table delete in 4.0.1. I am not sure if this is one of the bugs which have already been fixed to 4.0.2. I am Cc:ing Sinisa who knows the answer. > 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 Regards, Heikki Innobase Oy (sql database) --------------------------------------------------------------------- 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
