This queue is for tickets about the Class-DBI CPAN distribution.

Report information
The Basics
Id:
118491
Status:
open
Priority:
Low/Low
Queue:

People
Owner:
Nobody in particular
Requestors:
chintzbaby [...] chintzbaby.com
Cc:
AdminCc:

BugTracker
Severity:
(no value)
Broken in:
(no value)
Fixed in:
(no value)



Subject: Ubuntu 16.04, MySQL 5.7, "Can't get last insert id" error on inserting row.
Date: Mon, 24 Oct 2016 17:51:25 +0100
To: bug-Class-DBI@rt.cpan.org
From: ChintzBaby <chintzbaby@chintzbaby.com>
Hi,
  • Class-DBI-v3.0.17
  • Perl : v5.22.1
  • Ubuntu 16.04 : Linux  4.4.0-45-lowlatency #66-Ubuntu SMP PREEMPT Wed Oct 19 14:57:51 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
  • MySQL :  Ver 14.14 Distrib 5.7.15, for Linux (x86_64)
  • DBD-mysql-4.038
  • libmysqlclient.so.20.3.2

I've upgraded Ubuntu from 15.10 (MySQL5.6) and now have MySQL 5.7, and I get "Can't get last insert id" when inserting a row into a table, where in 5.6/15.10 it worked fine. 

The row does get inserted into the table ok, but the inserted row_id is not returned by MySQL :

Line 609 in /usr/local/share/perl/5.22.1/Class/DBI.pm :
   sub _auto_increment_value {
   ...
   my $id = $dbh->last_insert_id(undef, undef, $self->table, undef)    # std
      || $dbh->{mysql_insertid}                                         # mysql
      || eval { $dbh->func('last_insert_rowid') }
      or $self->_croak("Can't get last insert id");

Called from my CGI program :
use DBI;
use MyApp;
use MyApp::PictureTag;
...
MyApp::PictureTag->insert (
   {
      Picture_id => $PicId,
      Tag_id     => $TagId,
   }
);

PictureTag has a primary auto-increment key.

DBI Trace :

    -> ping for DBD::mysql::db (DBIx::ContextualFetch::db=HASH(0x55b68bc45d30)~0x55b68bc45e68) thr#55b68a5a8220
 T  <- ping= ( 1 ) [1 items] at /usr/local/share/perl/5.22.1/Ima/DBI.pm line 326 via  at /usr/local/share/perl/5.22.1/Class/DBI.pm line 600
    >> last_insert_id DISPATCH (DBIx::ContextualFetch::db=HASH(0x55b68bc45d30) rc1/3 @5 g2 ima2801 pid#13281) at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at /usr/local/share/perl/5.22.1/Class/DBI.pm line 628
    -> last_insert_id for DBD::mysql::db (DBIx::ContextualFetch::db=HASH(0x55b68bc45d30)~0x55b68bc45e68 undef undef 'PictureTag' undef) thr#55b68a5a8220
 T  <- last_insert_id= ( '0' ) [1 items] at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at /usr/local/share/perl/5.22.1/Class/DBI.pm line 628
    >> FETCH       DISPATCH (DBIx::ContextualFetch::db=HASH(0x55b68bc45e68) rc2/23 @2 g2 ima404 pid#13281) at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at /usr/local/share/perl/5.22.1/Class/DBI.pm line 628
    -> FETCH for DBD::mysql::db (DBIx::ContextualFetch::db=HASH(0x55b68bc45e68)~INNER 'mysql_insertid') thr#55b68a5a8220
 T  <- FETCH= ( '0' ) [1 items] at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at /usr/local/share/perl/5.22.1/Class/DBI.pm line 628
    >> func        DISPATCH (DBIx::ContextualFetch::db=HASH(0x55b68bc45d30) rc1/3 @2 g2 ima6 pid#13281) at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609
    -> last_insert_rowid for DBD::mysql::db (DBIx::ContextualFetch::db=HASH(0x55b68bc45d30)~0x55b68bc45e68) thr#55b68a5a8220

I've seen some reference to this here : https://rt.cpan.org/Public/Bug/Display.html?id=108000 and in the change notes for 4.035_01, but I do not know how to solve this issue (apart from going back to MySQL 5.6).

I've scrubbed Google and I can't find anyone else who has had this issue!

Thanks much for any help.
Andy


Subject: Re: [rt.cpan.org #118491] AutoReply: Ubuntu 16.04, MySQL 5.7, "Can't get last insert id" error on inserting row.
Date: Wed, 26 Oct 2016 13:14:33 +0100
To: bug-Class-DBI@rt.cpan.org
From: ChintzBaby <chintzbaby@chintzbaby.com>

Hi,

On further inspection, I found this fixed it on my system /usr/local/share/perl/5.22.1/Class/DBI.pm : Line 609 ish :

sub _auto_increment_value {
   my $self = shift;
   my $sth  = shift; # Added for MySQL 5.7
   my $dbh  = $self->db_Main;
...

my $id = $dbh->last_insert_id(undef, undef, $self->table, undef)    # std
   || $dbh->{mysql_insertid}                                         # mysql
   || eval { $dbh->func('last_insert_rowid') }
   || $sth->{'mysql_insertid'}                                      # Added for MySQL 5.7
   or $self->_croak("Can't get last insert id");

   return $id;
}

sub _insert_row {
   my $self = shift;
   my $data = shift;
   eval {
      my @columns = keys %$data;
      my $sth     = $self->sql_MakeNewObj(
         join(', ', @columns),
         join(', ', map $self->_column_placeholder($_), @columns),
      );
      $self->_bind_param($sth, \@columns);
      $sth->execute(values %$data);
      my @primary_columns = $self->primary_columns;

      $data->{ $primary_columns[0] } = $self->_auto_increment_value ($sth) # Added $sth for _auto_increment_value
         if @primary_columns == 1
         && !defined $data->{ $primary_columns[0] };

Anyone have any further thoughts?

Andy


On 24/10/16 17:51, Bugs in Class-DBI via RT wrote:
Show quoted text
Greetings,

This message has been automatically generated in response to the
creation of a trouble ticket regarding:
	"Ubuntu 16.04, MySQL 5.7, "Can't get last insert id" error on inserting row.", 
a summary of which appears below.

There is no need to reply to this message right now.  Your ticket has been
assigned an ID of [rt.cpan.org #118491].  Your ticket is accessible
on the web at:

    https://rt.cpan.org/Ticket/Display.html?id=118491

Please include the string:

         [rt.cpan.org #118491]

in the subject line of all future correspondence about this issue. To do so, 
you may reply to this message.

                        Thank you,
                        bug-Class-DBI@rt.cpan.org

-------------------------------------------------------------------------
Hi,

  * Class-DBI-v3.0.17
  * Perl : v5.22.1
  * Ubuntu 16.04 : Linux  4.4.0-45-lowlatency #66-Ubuntu SMP PREEMPT Wed
    Oct 19 14:57:51 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
  * MySQL :  Ver 14.14 Distrib 5.7.15, for Linux (x86_64)
  * DBD-mysql-4.038
  * libmysqlclient.so.20.3.2


I've upgraded Ubuntu from 15.10 (MySQL5.6) and now have MySQL 5.7, and I 
get "Can't get last insert id" when inserting a row into a table, where 
in 5.6/15.10 it worked fine.

The row does get inserted into the table ok, but the inserted row_id is 
not returned by MySQL :

Line 609 in /usr/local/share/perl/5.22.1/Class/DBI.pm :
    sub _auto_increment_value {
    ...
    my $id = $dbh->last_insert_id(undef, undef, $self->table, undef)    
# std
       || $dbh->{mysql_insertid} # mysql
       || eval { $dbh->func('last_insert_rowid') }
*or $self->_croak("Can't get last insert id");*

Called from my CGI program :
use DBI;
use MyApp;
use MyApp::PictureTag;
...
MyApp::PictureTag->insert (
    {
       Picture_id => $PicId,
       Tag_id     => $TagId,
    }
);

PictureTag has a primary auto-increment key.

DBI Trace :

     -> ping for DBD::mysql::db 
(DBIx::ContextualFetch::db=HASH(0x55b68bc45d30)~0x55b68bc45e68) 
thr#55b68a5a8220
  T  <- ping= ( 1 ) [1 items] at /usr/local/share/perl/5.22.1/Ima/DBI.pm 
line 326 via  at /usr/local/share/perl/5.22.1/Class/DBI.pm line 600
     >> last_insert_id DISPATCH 
(DBIx::ContextualFetch::db=HASH(0x55b68bc45d30) rc1/3 @5 g2 ima2801 
pid#13281) at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at 
/usr/local/share/perl/5.22.1/Class/DBI.pm line 628
     -> last_insert_id for DBD::mysql::db 
(DBIx::ContextualFetch::db=HASH(0x55b68bc45d30)~0x55b68bc45e68 undef 
undef 'PictureTag' undef) thr#55b68a5a8220
  T  <- last_insert_id= ( '0' ) [1 items] at 
/usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at 
/usr/local/share/perl/5.22.1/Class/DBI.pm line 628
     >> FETCH       DISPATCH 
(DBIx::ContextualFetch::db=HASH(0x55b68bc45e68) rc2/23 @2 g2 ima404 
pid#13281) at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at 
/usr/local/share/perl/5.22.1/Class/DBI.pm line 628
     -> FETCH for DBD::mysql::db 
(DBIx::ContextualFetch::db=HASH(0x55b68bc45e68)~INNER 'mysql_insertid') 
thr#55b68a5a8220
  T  <- FETCH= ( '0' ) [1 items] at 
/usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at 
/usr/local/share/perl/5.22.1/Class/DBI.pm line 628
     >> func        DISPATCH 
(DBIx::ContextualFetch::db=HASH(0x55b68bc45d30) rc1/3 @2 g2 ima6 
pid#13281) at /usr/local/share/perl/5.22.1/Class/DBI.pm line 609 via  at 
/usr/local/share/perl/5.22.1/Class/DBI.pm line 609
     -> last_insert_rowid for DBD::mysql::db 
(DBIx::ContextualFetch::db=HASH(0x55b68bc45d30)~0x55b68bc45e68) 
thr#55b68a5a8220

I've seen some reference to this here : 
https://rt.cpan.org/Public/Bug/Display.html?id=108000 and in the change 
notes for 4.035_01, but I do not know how to solve this issue (apart 
from going back to MySQL 5.6).

I've scrubbed Google and I can't find anyone else who has had this issue!

Thanks much for any help.
Andy




Subject: [rt.cpan.org #118491]
Date: Thu, 17 Nov 2016 17:23:02 +0000
To: "bug-class-dbi@rt.cpan.org" <bug-class-dbi@rt.cpan.org>
From: Robert Schulz <rschulz@mmm.com>


Please consider this a pile-on vote for fixing this bug.


From what I can see, 

DBI recommends $sth->{mysql_insertid} over the $dbh method anyway.


Ive confirmed Andy's solution.


The only other bit I did not see in his notes:


- $self->_auto_increment_value

+ $self->_auto_increment_value($sth)



Many Thanks to All

Another vote to please include this fix. Thank you.


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.