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>
Download (untitled) / with headers
text/plain 5.1k
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>
Download (untitled) / with headers
text/plain 324b
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
Download (untitled) / with headers
text/html 1009b
Another vote to please include this fix. Thank you.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

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