Skip Menu |

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

Report information
The Basics
Id: 118491
Status: open
Priority: 0/
Queue: Class-DBI

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

Bug Information
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 Show quoted text
>> 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 Show quoted text
>> 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 Show quoted text
>> 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.