Maybe you have autocommit on or something weird? From the MySQL documentation ( version 5.5), LAST_INSERT_ID() is per-connection, so it should be valid for each of your clients.

• https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id
• https://dba.stackexchange.com/questions/21181/is-mysqls-last-insert-id-function-guaranteed-to-be-correct
• https://dba.stackexchange.com/questions/31346/does-last-insert-id-returns-correct-result-when-writes-from-multiple-sessions

As a stylistic note, calling your error handler as &ErrorDBI; may or may not be what you want. Your method of calling it passes it the current contents of @_. The more explicit way of calling a function is:

ErrorDBI();
[download]

Also, I would set $dbh->{RaiseError} to 1, just to be sure that all database errors are fatal.

From the docs

Important
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

my $sthopen = $dbh->prepare("INSERT INTO.....
$sthopen->execute;
&ErrorDBI;
my $resid = $dbh->prepare("SELECT LAST_INSERT_ID()");
$resid->execute;
&ErrorDBI;
my @idrow = $resid->fetchrow_array;
[download]

• The portable way to get the last inserted ID is to use DBI's last_insert_id() method. Well, it should be. The problem of getting automatically generated IDs is solved in very different ways in different databases, and this method should hide the implementation details. Unfortunately, some databases require extra arguments for the method call, some databases don't always return a value depending on how the insert statement was prepared. Anyway, better use that method instead of a DB-specific query. In the worst case, both are equal, in the best case, DBI can use a better API function of the database to get the value.
• You don't need the ampersand in Perl 5 to call a function. Quite the opposite is true: You should avoid the ampersand in Perl 5 when calling functions, as it can introduce nasty, hard-to-debug errors. The ampersand was needed in Perl 4, but that was decades ago. Read more at Re^2: Merge log files causing Out of Memory (just a note on ampersand).
• DBI can automatically handle all errors for you, you don't have to explicitly check for errors. Add the RaiseError attribute with a true value to the connect() call and any failed command will raise an error (i.e. call die).

Alexander

As others have pointed out, this is a per-connection function, so if there are multiple clients, that may be affecting it. Also, triggers or the like that grab a new id (even if left unused) may b affecting it.

One kludge, perhaps, to guarantee it to work, would be to use a stored procedure to do the insert, run the select, and return the result from the select to you.

Also, triggers or the like that grab a new id (even if left unused) may b affecting it. One kludge, perhaps, to guarantee it to work, would be to use a stored procedure to do the insert, run the select, and return the result from the select to you.

If a trigger modifies the data for LAST_INSERT_ID, how could a stored procedure executing INSERT and SELECT LAST_INSERT_ID() guarantee that the data is not overwritten?

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

It cannot guarantee it was not overwritten, but it can guarantee what was inserted by that statement.

If a trigger runs as a result of the select, the insert will not finish until the trigger is finished, so the next statement should be accurate. (I am assuming noone is trying to specifically avoid detection with some fancy logic in the trigger.)

The shown code could not be as such. It must have been distributed among different programs, else your query does not make sense. At least be self-consistent.