MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:July 23 2004 9:33pm
Subject:Re: Last insert id problem/bug
View as plain text  
on 7/23/04 5:30 AM, Harald Fuchs at hf0722x@stripped wrote:

> The behavior is indeed strange, but it's not a bug, since it's documented:
> 
>      If you use `INSERT IGNORE' and the record is ignored, the
>      `AUTO_INCREMENT' counter still is incremented and
>      `LAST_INSERT_ID()' returns the new value.
> 
> The solution is, of course, not to use INSERT IGNORE at all.  You
> should instead catch any error, check that it's ER_DUP_KEY and not try
> the second INSERT in that case.

That's what I tried, I thought I was onto something, then I remembered... If
record does exists In the first table, it does not necessarily mean it
should or should not exist in the second table.  In the case where it does
not exist in the second table, I do need to insert it, but alas, with the
insert id of the found duplicate in the first table. I would have to select
the first table to get that id, which in this case, of hundreds of thousands
of imported records in batch, it just not feasible.
-- 
-------------------------------------------------------------
Scott Haneda                                Tel: 415.898.2602
http://www.newgeo.com                       Fax: 313.557.5052
scott@stripped                            Novato, CA U.S.A.


Thread
Last insert id problem/bugScott Haneda23 Jul
  • Re: Last insert id problem/bugScott Haneda23 Jul
  • Re: Last insert id problem/bugHarald Fuchs23 Jul
    • Re: Last insert id problem/bugScott Haneda23 Jul