Perl5 DBI Mysql: reliable way to get last_insert_id

Question

In my code I use database->last_insert_id(undef,undef,undef,"id"); to get the autoincremented primary key. This works 99.99% of the time. But once in a while it returns a value of 0. In such situations, Running a select with a WHERE clause similar to the value of the INSERT statement shows that the insert was successful. Indicating that the last_insert_id method failed to get the proper data.

Is this a known problem with a known fix? or should I be following up each call to last_insert_id with a check to see if it is zero and if yes a select statement to retrieve the correct ID value?

My version of mysql is mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64)

Edit1: Adding the actual failing code.

use Dancer2::Plugin::Database;
<Rest of the code to create the insert parameter>
eval{
     database->quick_insert("build",$job);
     $job->{idbuild}=database->last_insert_id(undef,undef,undef,"idbuild");
     if ($job->{idbuild}==0){
        my $build=database->quick_select("build",$job);
        $job->{idbuild}=$build->{idbuild};
        }
     };
debug ("=================Scheduler build Insert=======================*** ERROR :Got Error",$@) if $@;

Note: I am using Dancer's Database plugin. Whose description says,

Provides an easy way to obtain a connected DBI database handle by simply calling the database keyword within your Dancer2 application

Returns a Dancer::Plugin::Database::Core::Handle object, which is a subclass of DBI's DBI::db connection handle object, so it does everything you'd expect to do with DBI, but also adds a few convenience methods. See the documentation for Dancer::Plugin::Database::Core::Handle for full details of those.

Answer 1

I've never heard of this type of problem before, but I suspect your closing note may be the key. Dancer::Plugin::Database transparently manages database handles for you behind the scenes. This can be awfully convenient... but it also means that you could change from using one dbh to using a different dbh at any time. From the docs:

Calling database will return a connected database handle; the first time it is called, the plugin will establish a connection to the database, and return a reference to the DBI object. On subsequent calls, the same DBI connection object will be returned, unless it has been found to be no longer usable (the connection has gone away), in which case a fresh connection will be obtained.

(emphasis mine)

And, as ysth has pointed out in comments on your question, last_insert_id is handle-specific, which suggests that, when you get 0, that's likely to be due to the handle changing on you.

But there is hope! Continuing on in the D::P::DB docs, there is a database_connection_lost hook available which is called when the database connection goes away and receives the defunct handle as a parameter, which would allow you to check and record last_insert_id within the hook's callback sub. This could provide a way for you to get the id without the additional query, although you'd first have to work out a means of getting that information from the callback to your main processing code.

The other potential solution, of course, would be to not use D::P::DB and manage your database connections yourself so that you have direct control over when new connections are created.