Bug: Dancer::Plugin::Database does not accurately return the last insert id.

[jahagirdar]

Self contained testcase attached. Issue: database->last_insert_id(undef,undef,undef,$table) does not consistently return the last insert id value. In the attached testcase and log, I am calling last_insert_id in 4 different tests.

1. The first two are via dancer routes using database.... which fail
2. The third is via direct sql inside a dancer route which passes and

3. The final is via direct SQL outside Dancer which also passes..

   Looks like you failed 2 tests of 5.

   t/006_million_inserts.t .. ok 1 - Got app not ok 2 - Dancer Database Plugin with SQL Insert not ok 3 - Dancer Database Plugin with quick_insert ok 4 - Dancer DBI SQL Test ok 5 - DBI Test

Sample Failure message [App:21638] info @2017-11-04 15:00:56> recieved Last insert ID as 175 in t/006_million_inserts.t l. 36
[App:21638] debug @2017-11-04 15:00:56> Executing INSERT query INSERT INTO test (count) VALUES (?) with params 175 in /opt/perl5/perls/perl-5.26.1/lib/site_perl/5.2 6.1/Dancer2/Plugin.pm l. 533
[App:21638] info @2017-11-04 15:00:56> recieved Last insert ID as 176 in t/006_million_inserts.t l. 36
[App:21638] debug @2017-11-04 15:00:56> Executing INSERT query INSERT INTO test (count) VALUES (?) with params 176 in /opt/perl5/perls/perl-5.26.1/lib/site_perl/5.2 6.1/Dancer2/Plugin.pm l. 533
[App:21638] info @2017-11-04 15:00:57> recieved Last insert ID as 0 in t/006_million_inserts.t l. 36
[App:21638] debug @2017-11-04 15:00:57> Executing INSERT query INSERT INTO test (count) VALUES (?) with params 0 in /opt/perl5/perls/perl-5.26.1/lib/site_perl/5.26. 1/Dancer2/Plugin.pm l. 533
[App:21638] core @2017-11-04 15:00:57> Entering hook plugin.database.database_error in (eval 222) l. 1
DBD::mysql::db do failed: Duplicate entry '0' for key 'test_UNIQUE' at /opt/perl5/perls/perl-5.26.1/lib/site_perl/5.26.1/Dancer/Plugin/Database/Core/Handle.pm line 310.

Test and log attached.

test.zip

[1nickt]

Hi, Thanks for creating a test file. Unfortunately it does not run:

[App:15978] error @2017-11-04 10:23:11> Route exception: Can't get a database connection without settings supplied!
Please check you've supplied settings in config as per the Dancer::Plugin::Database documentation ...

Anyway, I can confirm that fetching the last_insert_id as follows works consistently on my system:

get '/insert/:name' => sub {
    database->quick_insert('people', { name => params->{name} });
    my $id = ( database->selectall_array('select last_insert_id()') )[0][0];
    return $id;
};

(Note that this is a naive implementation for demo purposes only.)

(In general I recommend getting the last insert ID for MySQL databases in this way rather than using the DBI method call.)