Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

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

Comment on DBI and last_insert_id
Select or Download Code
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 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.
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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://739700]
Approved by kyle
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (11)
As of 2014-10-31 15:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (219 votes), past polls