Bug #36646 for Net-MySQL: bug in Net::MySQL and in dbi:mysqlPP

Wed Jun 11 09:53:45 2008 baptiste.marcel [...] eservglobal.com - Ticket created

Subject:	bug in Net::MySQL and in dbi:mysqlPP
Date:	Wed, 11 Jun 2008 15:53:08 +0200
To:	bug-DBD-mysqlPP [...] rt.cpan.org, bug-Net-MySQL [...] rt.cpan.org
From:	Baptiste Marcel <baptiste.marcel [...] eservglobal.com>

Greetings I have to report a bug When the table has an index, a search with a where clause on that column hangs. I use: Net-MySQL-0.09 <http://search.cpan.org/%7Eoyama/Net-MySQL-0.09/> This is perl, v5.10.0 built for sun4-solaris-thread-multi-64 Binary build 1002 [283697] provided by ActiveState http://www.ActiveState.com Built Jan 9 2008 23:49:05 uname -a SunOS sebastopol 5.10 Generic_120011-14 sun4u sparc SUNW,Ultra-4 Here is a script that reproduces the bug: #!/bin/perl use strict ; use warnings ; use DBI; use Net::MySQL; use Config::INI; use Config::INI::Reader ; # this is to retrieve my PWD my $pwdhash = Config::INI::Reader->read_file('/export/home/chernaya/scripts/sevcat/.mysql_passwd'); my $strDbPasswd=$pwdhash->{'sevdb2'}->{passwd}; my $dbh = DBI->connect("dbi:mysqlPP:database=sevdb2;host=localhost", "chernaya", "$strDbPasswd", {'RaiseError' => 1}); $dbh->do("CREATE TABLE demo2 ( id INTEGER , value VARCHAR(20) , KEY (id) )"); $dbh->do("INSERT INTO demo2 VALUES (?, ?)", undef, 1, "a"); $dbh->do("INSERT INTO demo2 VALUES (?, ?)", undef, 2, "b"); my $sth = $dbh->prepare("SELECT id, value FROM demo2 where id=1"); print "Ready for executed \n"; $sth->execute(); # hangs -> never returns ... print "executed \n"; Note that the same without the where clause, or without the index is OK Note that its buggy whatever the index, being primary or not. Note that it also fails with DBI::mysqlPP Best regards -- Baptiste Marcel

Thu Jun 26 17:42:16 2008 cameron.neilson [...] gmail.com - Correspondence added

From:

cameron.neilson [...] gmail.com

I am having the same problem, and have traced it to the bug you describe. this has essentially rendered this module useless as we often have reason to search on an index. has anyone found a solution? On Wed Jun 11 09:53:45 2008, baptiste.marcel@eservglobal.com wrote: Show quoted text

> Greetings > > I have to report a bug > > When the table has an index, a search with a where clause on that > column > hangs. > > > I use: > Net-MySQL-0.09 <http://search.cpan.org/%7Eoyama/Net-MySQL-0.09/> > > This is perl, v5.10.0 built for sun4-solaris-thread-multi-64 > > > Binary build 1002 [283697] provided by ActiveState > http://www.ActiveState.com > Built Jan 9 2008 23:49:05 > > uname -a > SunOS sebastopol 5.10 Generic_120011-14 sun4u sparc SUNW,Ultra-4 > > > Here is a script that reproduces the bug: > > #!/bin/perl > use strict ; > use warnings ; > use DBI; > use Net::MySQL; > use Config::INI; > use Config::INI::Reader ; > > # this is to retrieve my PWD > my $pwdhash = > Config::INI::Reader-

> >read_file('/export/home/chernaya/scripts/sevcat/.mysql_passwd');

> my $strDbPasswd=$pwdhash->{'sevdb2'}->{passwd}; > > > my $dbh = DBI->connect("dbi:mysqlPP:database=sevdb2;host=localhost", > "chernaya", "$strDbPasswd", > {'RaiseError' => 1}); > > $dbh->do("CREATE TABLE demo2 ( id INTEGER , value VARCHAR(20) , > KEY > (id) )"); > $dbh->do("INSERT INTO demo2 VALUES (?, ?)", undef, 1, "a"); > $dbh->do("INSERT INTO demo2 VALUES (?, ?)", undef, 2, "b"); > my $sth = $dbh->prepare("SELECT id, value FROM demo2 where id=1"); > > > print "Ready for executed \n"; > $sth->execute(); > # hangs -> never returns ... > > print "executed \n"; > > Note that the same without the where clause, or without the index is > OK > Note that its buggy whatever the index, being primary or not. > Note that it also fails with DBI::mysqlPP > > Best regards >

Thu Jun 26 17:42:17 2008 The RT System itself - Status changed from 'new' to 'open'

Sat Jul 12 18:21:51 2008 miranska [...] yahoo.com - Correspondence added

From:

miranska [...] yahoo.com

It seems that it boils down to processing special codes by NET::MySQL, since the database actually returns data (based on debug output). A quick and dirty workaround is to enclose your query in another SELECT statement. Something like SELECT * FROM (SELECT id, value FROM table WHERE id < 10) a; It works on Windows and Linux.

Sun Nov 30 15:21:16 2008 rosenfield.albert [...] gmail.com - Correspondence added

From:

rosenfield.albert [...] gmail.com

I spent some time debugging in Net::MySQL and found the following. After a query has been sent to the server, and a response has been received, the _has_next_packet sub is called to check for an EOF within the data stream from the server: sub _has_next_packet { my $self = shift; #substr($_[0], -1) ne "\xfe"; return substr($_[0], -5) ne "\xfe\0\0\x22\x00"; } At some point the protocol probably changed, and the second code line was commented out and replaced by the third. I don't know why the code counts from the end of the packet (-5 parameter to substr()) rather than from the beginning. If counting from the beginning, you wouldn't have to change the code when the protocol changes because new stuff is always added to the end. Anyway, the protocol is structured as such (last 5 bytes in PDU): 0x00 Number of columns 0x0000 Number of warnings 0x0000 Status bytes A special value 0xFE in the "number of columns" field means EOF. But in addition to checking for 0xFE, the current _has_next_packet() code also checks whether there was exactly 0 warnings (0x0000), whether an index was used by the server (0x0020 means false) and whether multi- query is enabled (0x0002). If that is not the situation (rare with any new server), the driver will listen indefinitely for another data packet which is not coming. The simplest fix is to change the code to this: sub _has_next_packet { my $self = shift; return substr($_[0], -5, 1) ne "\xfe"; } although I'm still not sure why counting starts from the end of the PDU. Hope it helps!

Tue Jan 26 10:43:23 2010 bitcard [...] dejasurf.com - Correspondence added

From:

bitcard [...] dejasurf.com

Thanks: aliquantus' fix worked or me. Details: I had the same hang using WHERE-clause on "indexed" fields in an innodb table. Insert would work fine, but SELECT would fail. I was running mySQL 5.1.41 on Windows7 with Cygwin.