List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 1 2001 2:44am
Subject:Re: last_insert_id() bug ?? using INSERT IGNORE
View as plain text  
At 11:44 AM +1200 10/1/01, marcus davy wrote:
>If you specify the keyword IGNORE in an INSERT, any rows that duplicate
>an existing PRIMARY or UNIQUE key in the table are ignored and are not
>inserted.
>But the last_insert_id() function still appears to increment by one
>in situations when the query is not adding any new information to
>the table.
>
>This looks like a bug to me can anyone enlighten me to this anomaly?

Why is it a bug?

INSERT IGNORE can't fully assess whether the record is to be ignored
until the record's contents have been generated.

>
>I am using 3.23.42-log on red hat 7.1 (also same results on 3.23.40-log).
>I have searched the list archive for this topic but havent found anything yet.
>
>
>mysql commands to test this below
>
>CREATE DATABASE test;
>use test;
>CREATE TABLE organism (
>  OM varchar(100) NOT NULL unique,
>  OM_ID tinyint(1) NOT NULL auto_increment,
>  PRIMARY KEY (OM_ID)
>  ) TYPE=MyISAM;
>
>INSERT IGNORE INTO organism VALUES('foo', NULL);
>INSERT IGNORE INTO organism VALUES('fodda', NULL);
>SELECT * FROM organism;
>
>#+-------+-------+
>#| OM    | OM_ID |
>#+-------+-------+
>#| foo   |     1 |
>#| fodda |     2 |
>#+-------+-------+
>#2 rows in set (0.00 sec)
>
>SELECT last_insert_id();
>
>#+------------------+
>#| last_insert_id() |
>#+------------------+
>#|                2 |
>#+------------------+
>#1 row in set (0.00 sec)
>
>INSERT IGNORE INTO organism VALUES('fodda', NULL);
>
>#Query OK, 0 rows affected (0.00 sec) 
># NO NEW INFORMATION ADDED
>
>SELECT * FROM organism;
>#+-------+-------+
>#| OM    | OM_ID |
>#+-------+-------+
>#| foo   |     1 |
>#| fodda |     2 |
>#+-------+-------+
>#2 rows in set (0.01 sec)
>
>SELECT last_insert_id();
>
>#+------------------+
>#| last_insert_id() |
>#+------------------+
>#|                3 |
>#+------------------+
>#1 row in set (0.01 sec)
>
># last_insert_id has incremented by 1, by adding a further new field,
># last_insert_id() will correctly show the last inserted id again


-- 
Paul DuBois, paul@stripped
Thread
last_insert_id() bug ?? using INSERT IGNOREmarcus davy1 Oct
  • Re: last_insert_id() bug ?? using INSERT IGNOREPaul DuBois1 Oct
    • RE: last_insert_id() bug ?? using INSERT IGNOREWill French1 Oct
      • Re: last_insert_id() bug ?? using INSERT IGNOREBenjamin Pflugmann1 Oct
    • Re: last_insert_id() bug ?? using INSERT IGNOREmarcus davy2 Oct
      • Re: last_insert_id() bug ?? using INSERT IGNOREPaul DuBois2 Oct
        • Re: last_insert_id() bug ?? using INSERT IGNOREmarcus davy2 Oct