DBI and last_insert

cog has asked for the wisdom of the Perl Monks concerning the following question:

Here's a shorter version of what I was trying to do:

my $sql = "INSERT INTO table_name"
        . " (a, b, c)"
        . " VALUE (?, ?, ?)"
        . " ON DUPLICATE KEY"
        . " UPDATE field_three = ?";

my $sth = $dbh->prepare( $sql );

for my $entry ( keys %entries ) {

    $sth->execute( $entries{'a'}, $entries{'b'}, $entries{'b'} );

    my $id = $dbh->last_insert_id( undef, undef, undef, undef );

    # use $id to insert stuff in another table

}
[download]

Where the id is an auto-increment column, and also the primary key.

The problem is that last_insert_id() not always gives me the right id. (concurrency? transaction issues?)

I actually tried doing a SELECT on the table right after that INSERT, but since I'm doing this inside a transaction, the SELECT clause returns nothing...

Any ideas on how to go around this? Am I missing something trivial?

Both DBI's and MySQL's documentation point to a few CAVEATS around last_insert_id()...

Comment on DBI and last_insert_id Select or Download Code

Replies are listed 'Best First'.
Re: DBI and last_insert_id by moritz (Cardinal) on Jan 28, 2009 at 21:11 UTC
Do you have `RaiseError` active? It seems that you pass an SQL string with four placeholders to `prepare`, but only pass three to `execute`. If execute fails, I wouldn't be suprised if `last_insert_id()` returned some garbage (although an error message would be useful).	[reply] [d/l] [select]
Re^2: DBI and last_insert_id by cog (Parson) on Jan 29, 2009 at 11:24 UTC
I wish that would be the problem, but unfortunately it isn't. There are indeed four placeholders and I'm passing three to `execute`, but that's just because I edited the code to post it here and missed that. The number of arguments I'm passing in the actual code is correct. I also have `RaiseError` active. The `INSERT` statement definitely works, as later on I can see the row is in the database. It's just that the entry I'm inserting in the other table using the `last_insert_id()` has the wrong `id`.	[reply] [d/l] [select]
Re^3: DBI and last_insert_id by moritz (Cardinal) on Jan 29, 2009 at 12:31 UTC
In future, please test that the code you post here does exhibit the same behaviour as you describe.	[reply]
Re: DBI and last_insert_id by perrin (Chancellor) on Jan 28, 2009 at 23:59 UTC
Well, if the ON DUPLICATE KEY UPDATE clause fires, I doubt you get anything in last_insert_id().	[reply]
Re^2: DBI and last_insert_id by cog (Parson) on Jan 29, 2009 at 11:26 UTC
True, but I'm not even getting the correct value in `last_insert_id()` even without it failing.	[reply] [d/l]
Re^3: DBI and last_insert_id by perrin (Chancellor) on Jan 29, 2009 at 16:56 UTC
Ok, here are a couple more thoughts. First, is there possibly another insert in your real code between the first insert and your call to last_insert_id? A side effect of some sub call maybe? Second, you definitely should be able to see data you inserted during a transaction with a SELECT, even if the data is not committed yet, provided your queries are all coming from the same connection. That sounds very suspicious to me.	[reply]
Re^4: DBI and last_insert_id by cog (Parson) on Jan 29, 2009 at 18:17 UTC
Re: DBI and last_insert_id by graff (Chancellor) on Jan 29, 2009 at 08:05 UTC
Both previous replies are definitely on the mark. To follow up on the second one (and provide the missing detail that was not mentioned there): you want your "ON DUPLICATE KEY UPDATE ..." clause to include a reference to the primary key field -- something like this, assuming the "field_three" part is still needed: `. " ON DUPLICATE KEY" . " UPDATE field_three = ?, your_key_field=LAST_INSERT_ID(your +_key_field)";` [download] I've always found this syntax to be a little disorienting, but you just have to accept the fact that when trying to insert a record that would violate some uniqueness constraint, the application of that constraint leaves the "last_insert_id" value "not meaningful". Check the last paragraph in section 12.2.4.3 of the mysql manual for an explanation (if you're using mysql), or the corresponding doc of your particular rdb server.	[reply] [d/l]
Re: DBI and last_insert_id by G@SP@R (Initiate) on Jan 29, 2009 at 13:30 UTC
Olá cog. ;) I remember having problems with last_insert_id to. Ultimately I was able to get way with something like this. $sth->execute( $entries{'a'}, $entries{'b'}, $entries{'b'} ); my $last_insert_id =$dbh->{q{mysql_insertid}}; It seems though that you can also use something like this: $dbh->do('INSERT INTO table_name ...'); my $id = $dbh->last_insert_id(undef, undef, qw(table_name your_table_id)) or die "no insert id?"; About the select, try running the select just after you've inserted or after you made the call to last_insert_id whilst inside the transaction to make sure last_insert_id is returning the right values.	[reply]

DBI and last_insert_id