last_insert_id() is generally only useful directly after a (successful) INSERT, and even then the exact semantics are riddled with potential pitfalls, especially if you're writing a portable application (see the DBI docs for details.) For MySQL (and incidentally SQLite too) you can list all 4 params to last_insert_id as undef, or more commonly the blank string for brevity, and it should do what you expect, even in transactions. (PostgreSQL gives you the RETURNING clause, which is even better if you can design for that RDBMS.)

Remember to put the rollback inside an eval block too as it may also fail, especially if the handle is now defunct for some reason (connection error or the like.)

I'd also suggest use of Carp::confess() or Carp::croak() so that you can point to a the specific Perl line that caused the error to be thrown inside the eval block.

If you're still having problems, posting a minimal reproduction, including the most basic SQL schema you can design to show the problem, is likely required.

This seems to work as expected for me, printing incrementing PK values for the id column: 

use strict;
use warnings;
use DBI;
use Carp qw( confess croak );

my $dbh = DBI->connect(
    "DBI:mysql:db=test1001;host=localhost",
    "pm", "correcthorsebatterystaple", {
        RaiseError => 1,
        AutoCommit => 0,
        PrintError => 0,    # do our own error handling.
    }
) or die "DB connect: $DBI::errstr";
 
eval {
    $dbh->do(q[ INSERT INTO ref(color) VALUES (?) ], undef, 'blue');
    my $last_ref = $dbh->last_insert_id('','','','');
    printf STDERR "last ref: %s\n", $last_ref;
    $dbh->do(q[ INSERT INTO t1(ref_id) VALUES (?) ], undef, $last_ref)
+;
    my $last_t1 = $dbh->last_insert_id('','','','');
    printf STDERR "last t1: %s\n", $last_t1;
    $dbh->commit;
};
if ($@) {
    my $err = $@;    # save $@, as rolling back below could also fail.
    # attempt to rollback, which could also fail.
    eval { $dbh->rollback; } if ($dbh);
    confess( "DB error: $err" );
}

__END__

Schema used for the test1001 DB is as follows:

CREATE DATABASE test1001;
use test1001;
GRANT ALL ON test1001.* TO pm;
CREATE TABLE ref(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    color TEXT
);
CREATE TABLE t1(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    ref_id BIGINT REFERENCES ref(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

[download]

In reply to Re: Perl msyql last_insert_id works once? by Apero
in thread Perl msyql last_insert_id works once? by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.