Bug #108000 for DBD-mysql: 5.7.9 mysqlclient does not return correct mysql_insert_id via database handle after SELECT statement

Mon Oct 26 16:24:30 2015 MICHIELB [...] cpan.org - Ticket created

Subject:

5.7.9 mysqlclient does not return correct mysql_insert_id via database handle

From https://bugs.mysql.com/bug.php?id=78778 Hi, I am one of the maintainers of the Perl DBD::mysql module. I ran the test suite of this module against a MySQL 5.7.8 database and found one of the tests failing: # Failed test 'dbh insert id 0 == max(id) 2 in dbd_mysql_t31' # at t/31insertid.t line 58. # got: 0 # expected: 2 # Looks like you failed 1 test of 18. This test inserts some records in a table and then checks mysql_insertid via the statement handle and via the database handle. I found when I use the 5.7.8 client the value returned by the database handle is '0'. Full code for the test is here: https://github.com/perl5-dbi/DBD-mysql/blob/bb33077f26958872a9b71429fad9711328389ca5/t/31i... When I run the same test using a 5.5.x client against the 5.7.8 database the tests succeed; so my assumption is the problem would be in libmysqlclient.

Mon Mar 28 17:27:41 2016 garu [...] cpan.org - Correspondence added

Hi! Just got bit by this :( Apparently, there is someone working on the original bug report (https://bugs.mysql.com/bug.php?id=78778) but he's asking for some extra information and got no reply. Is anyone able to provide help? My DBD knowledge is not that good. Also, is there a workaround other than skipping that test and hoping for the best? Cheers! And thank you so much for all the effort put in DBD::mysql! garu

Mon Mar 28 17:27:42 2016 The RT System itself - Status changed from 'new' to 'open'

Thu Jul 28 10:13:28 2016 vlmarek [...] volny.cz - Correspondence added

From:

vlmarek [...] volny.cz

Hi, I have hit the same issue (mysql 5.7.11) and I will be providing more data to mysql team. But I wonder. What is the difference between $sth->{'mysql_insertid'} and $dbh->{'mysql_insertid'} ? From the specs https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html my_ulonglong mysql_insert_id(MYSQL *mysql) Which would I think correspond to $dbh-> one. What $sth-> one should do? (strangely dbh one fails) Also if I enable debug level3, I can see for the successfull call: -> FETCH for DBD::mysql::st (DBI::st=HASH(0x39ce58bc0)~INNER 'mysql_insertid') thr#39cae9cf0 -> dbd_st_FETCH_attrib for 39cd74cf0, key mysql_insertid INSERT ID 2 <- FETCH= ( '2' ) [1 items] at 31insertid.t line 58 -> FETCH for DBD::mysql::st (DBI::st=HASH(0x39ce58bc0)~INNER 'mysql_insertid') thr#39cae9cf0 -> dbd_st_FETCH_attrib for 39cd74cf0, key mysql_insertid INSERT ID 2 <- FETCH= ( '2' ) [1 items] at Builder.pm line 1159 via at More.pm line 482 And for the unsuccessfull call: -> FETCH for DBD::mysql::db (DBI::db=HASH(0x39d026d20)~INNER 'mysql_insertid') thr#39cae9cf0 <- FETCH= ( '0' ) [1 items] at 31insertid.t line 60 -> FETCH for DBD::mysql::db (DBI::db=HASH(0x39d026d20)~INNER 'mysql_insertid') thr#39cae9cf0 <- FETCH= ( '0' ) [1 items] at Builder.pm line 1159 via at More.pm line 482 This is the same for both mysql 5.6 (where the test passes) and 5.7 (where it does not) I have no idea whether it means anything, but maybe it rings a bell? Cheers __ Vlad

Mon Aug 01 02:30:07 2016 MICHIELB [...] cpan.org - Subject changed from '5.7.9 mysqlclient does not return correct mysql_insert_id via database handle' to '5.7.9 mysqlclient does not return correct mysql_insert_id via database handle after SELECT statement'

Mon Aug 01 02:30:07 2016 MICHIELB [...] cpan.org - Taken

Mon Aug 01 02:35:52 2016 MICHIELB [...] cpan.org - Correspondence added

RT-Send-CC:

vlmarek [...] volny.cz

Hi, I think the failure is not as bad as I first thought. An INSERT statement is executed on the database handle. Subsequently, on the same database handle, a SELECT is performed. Now $dbh->{mysql_insertid} is 0 on the database handle; when compiled against libmysqlclient 5.7 or up. That actually seems more correct to me, although it is of course a change in behavior when compared to earlier versions of libmysqlclient. I think it would be best to always use $sth->{mysql_insertid}, and I've updated the docs to reflect this. Also, I updated the test t/31insertid.t so it now passes on the newer client versions. I put this out as version 4.035_01 (a DEV release) on CPAN. Please let me know your ideas. -- Michiel On Thu 28 Jul 2016 10:13:28, neuron wrote: Show quoted text

> Hi, > > I have hit the same issue (mysql 5.7.11) and I will be providing more > data to > mysql team. But I wonder. What is the difference between > $sth->{'mysql_insertid'} and $dbh->{'mysql_insertid'} ? > > From the specs > > https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html > > my_ulonglong mysql_insert_id(MYSQL *mysql) > > Which would I think correspond to $dbh-> one. What $sth-> one should > do? > (strangely dbh one fails) > > > Also if I enable debug level3, I can see for the successfull call: > > -> FETCH for DBD::mysql::st (DBI::st=HASH(0x39ce58bc0)~INNER > 'mysql_insertid') thr#39cae9cf0 > -> dbd_st_FETCH_attrib for 39cd74cf0, key mysql_insertid > INSERT ID 2 > <- FETCH= ( '2' ) [1 items] at 31insertid.t line 58 > -> FETCH for DBD::mysql::st (DBI::st=HASH(0x39ce58bc0)~INNER > 'mysql_insertid') thr#39cae9cf0 > -> dbd_st_FETCH_attrib for 39cd74cf0, key mysql_insertid > INSERT ID 2 > <- FETCH= ( '2' ) [1 items] at Builder.pm line 1159 via at More.pm > line 482 > > > > And for the unsuccessfull call: > > -> FETCH for DBD::mysql::db (DBI::db=HASH(0x39d026d20)~INNER > 'mysql_insertid') thr#39cae9cf0 > <- FETCH= ( '0' ) [1 items] at 31insertid.t line 60 > -> FETCH for DBD::mysql::db (DBI::db=HASH(0x39d026d20)~INNER > 'mysql_insertid') thr#39cae9cf0 > <- FETCH= ( '0' ) [1 items] at Builder.pm line 1159 via at More.pm > line 482 > > > This is the same for both mysql 5.6 (where the test passes) and 5.7 > (where it does not) > > I have no idea whether it means anything, but maybe it rings a bell? > > Cheers > __ > Vlad

Mon Aug 01 07:33:38 2016 vlmarek [...] volny.cz - Correspondence added

From:

vlmarek [...] volny.cz

I hoped that it will be something like what you said. I would consider your change as a fix for this ticket. Mysql guys could at least confirm that the difference in behavior is expected. I found nothing in the mysql 5.7 news to support that though https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-0.html Thank you for your help __ Vlad

Fri Aug 19 01:50:18 2016 MICHIELB [...] cpan.org - Correspondence added

Fixed in 4.035_02. On Mon 01 Aug 2016 02:35:52, MICHIELB wrote: Show quoted text

> Hi, > > I think the failure is not as bad as I first thought. > > An INSERT statement is executed on the database handle. > Subsequently, on the same database handle, a SELECT is performed. > Now $dbh->{mysql_insertid} is 0 on the database handle; when compiled > against libmysqlclient 5.7 or up. > > That actually seems more correct to me, although it is of course a > change in behavior when compared to earlier versions of > libmysqlclient. > > I think it would be best to always use $sth->{mysql_insertid}, and > I've updated the docs to reflect this. Also, I updated the test > t/31insertid.t so it now passes on the newer client versions. > > I put this out as version 4.035_01 (a DEV release) on CPAN. Please let > me know your ideas. > -- > Michiel > > On Thu 28 Jul 2016 10:13:28, neuron wrote:

> > Hi, > > > > I have hit the same issue (mysql 5.7.11) and I will be providing more > > data to > > mysql team. But I wonder. What is the difference between > > $sth->{'mysql_insertid'} and $dbh->{'mysql_insertid'} ? > > > > From the specs > > > > https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html > > > > my_ulonglong mysql_insert_id(MYSQL *mysql) > > > > Which would I think correspond to $dbh-> one. What $sth-> one should > > do? > > (strangely dbh one fails) > > > > > > Also if I enable debug level3, I can see for the successfull call: > > > > -> FETCH for DBD::mysql::st (DBI::st=HASH(0x39ce58bc0)~INNER > > 'mysql_insertid') thr#39cae9cf0 > > -> dbd_st_FETCH_attrib for 39cd74cf0, key mysql_insertid > > INSERT ID 2 > > <- FETCH= ( '2' ) [1 items] at 31insertid.t line 58 > > -> FETCH for DBD::mysql::st (DBI::st=HASH(0x39ce58bc0)~INNER > > 'mysql_insertid') thr#39cae9cf0 > > -> dbd_st_FETCH_attrib for 39cd74cf0, key mysql_insertid > > INSERT ID 2 > > <- FETCH= ( '2' ) [1 items] at Builder.pm line 1159 via at More.pm > > line 482 > > > > > > > > And for the unsuccessfull call: > > > > -> FETCH for DBD::mysql::db (DBI::db=HASH(0x39d026d20)~INNER > > 'mysql_insertid') thr#39cae9cf0 > > <- FETCH= ( '0' ) [1 items] at 31insertid.t line 60 > > -> FETCH for DBD::mysql::db (DBI::db=HASH(0x39d026d20)~INNER > > 'mysql_insertid') thr#39cae9cf0 > > <- FETCH= ( '0' ) [1 items] at Builder.pm line 1159 via at More.pm > > line 482 > > > > > > This is the same for both mysql 5.6 (where the test passes) and 5.7 > > (where it does not) > > > > I have no idea whether it means anything, but maybe it rings a bell? > > > > Cheers > > __ > > Vlad

Fri Aug 19 01:50:19 2016 MICHIELB [...] cpan.org - Status changed from 'open' to 'resolved'

Fri Aug 19 01:50:19 2016 MICHIELB [...] cpan.org - Fixed in 4.035_02 added