Robert, yes, if there are multiple UNIQUE keys in a table, then in the REPLACE implementation there is a loop which DELETEs all records which have conflicting UNIQUE key values.
Since you have kept the binlog of your database, you can rerun the SQL statements and try to find where your rows magically disappear. I am not fond of the REPLACE statement. It is 'implicit programming', which can produce hard-to-find bugs in your application. Similar implicit bugs arise from the use of ON DELETE/UPDATE CASCADE and triggers. Regards, Heikki sql query .................... Subject: Re: InnoDB is losing records, there are gaps in autoincrement sequence From: Robert Wunderer Date: 24 Jan 2003 12:53:04 +0100 ---------------------------------------------------------------------------- ---- Hi. ----- Original Message ----- > InnoDB appears to be losing records. > > The version I am using is 3.32.53 in Windows 2000. > > Gaps are appearing in the autoincrement sequence when > the application does not permit record deletions. > > Records disappear although they have been retrieved > for printing earlier. We seem to be having the same problem: Even though there is not a single "delete" statement referencing the table in question from time to time records seem to disappear. I even let mysql do an update log and couldn't find a delete statement there either. Today I found the following article in the mailing list archives: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:40074 It explains how a REPLACE statement on a table with multiple unique keys might cause more than one row to be deleted. I am not sure if this is definite cause of the disappearing records in my application, but I am going to investigate this further. Regards, Robert. --------------------------------------------------------------------- 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
