Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

DBI and last_insert_id

by cog (Parson)
on Jan 28, 2009 at 21:02 UTC ( #739700=perlquestion: print w/replies, xml ) Need Help??
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 }

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()...

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).
      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.

        In future, please test that the code you post here does exhibit the same behaviour as you describe.
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().
      True, but I'm not even getting the correct value in last_insert_id() even without it failing.

        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.

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)";
    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 of the mysql manual for an explanation (if you're using mysql), or the corresponding doc of your particular rdb server.
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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://739700]
Approved by kyle
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2017-09-22 17:48 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (266 votes). Check out past polls.