Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 13582
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: dennis.doyle [...] morgandoyle.co.uk
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 3.0000
Fixed in: (no value)

Attachments
Copy of harness_stderr.txt



Subject: prepare & execute corruption in 3.000
Download (untitled) / with headers
text/plain 7.5k
Hi, A bug seems to have been introduced between DBD:mysql versions 2.9008 and 3.0000. o/s is: Linux ultra4.me2uweb.net 2.4.21-27.0.2.ELsmp #1 SMP Wed Jan 12 23:35:44 EST 2005 i686 i686 i386 GNU/Linux It seems to be related to $dbh->prepare with DELAYED syntax & $sth->execute and escaping quotes and/or escaped quotes. It looks like corruption of memory from the stderr trace logging which contains control characters so I am unable to past it into this - see attached file. It manfests itself in many different ways - sometimes a runaway process - sometimes apparently stalling in the execute - sometimes producing a segmentation violation these symptoms change if you make the smallest change to any of the code, including changing the tracelevel. On 3 different installations of 2.9008 this style of code has been working fine for months. The 2 different installations of 3.0000 this style of code has been falling all over the place since 3.0000 was installed on Saturday 2/7/5. Hope the following helps: The following small testharness reproduces one variant of the bug: It returns status code: 65280. The database field is updated. The tracelog complains about invalid SQL because the sting being inserted appears to have been overwritten towards the end with STX NULL NULL NULL The stdout from 3.0000 which fails is: ------------------------------------------------------------------------------------------ testfred starts This is perl, v5.8.4 built for i686-linux Copyright 1987-2004, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using `man perl' or `perldoc perl'. If you have access to the Internet, point your browser at http://www.perl.com/, the Perl Home Page. module DBI OK; version: 1.48 module DBD::mysql OK; version: 3.0000 testfred Preparing handle testfred Executing 12 testfred Executing long ------------------------------------------------------------------------------------------ The stderr tracelog with connect params removed for security reasons, from 2.9008 which works is: ------------------------------------------------------------------------------------------ -> prepare for DBD::mysql::db (DBI::db=HASH(0x82093ec)~0x833e658 ' INSERT DELAYED INTO Test1 ( Message) VALUES (?) ') Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x833e7d8) at testfred13.pl line 46 -> execute for DBD::mysql::st (DBI::st=HASH(0x833e7d8)~0x81f12ac '\"12\"') -> dbd_st_execute for 081f109c -> mysql_st_interal_execute Binding parameters: INSERT DELAYED INTO Test1 ( Message) VALUES ('\\\"12\\\"') <- mysql_st_internal_execute returning rows 1 <- dbd_st_execute returning imp_sth->row_num 1 <- execute= 1 at testfred13.pl line 53 -> execute for DBD::mysql::st (DBI::st=HASH(0x833e7d8)~0x81f12ac 'FINAL LOCATION\n\nURL: http://myweb.tiscali.co.uk/dennisdoylewrong\nReturn Code: 404\nReturn Message: Not Found\nPage Size: 707\nHeaders: \nConnection: close\nDate: Thu, 07 Jul 2005 14:19:58 GMT\nAccept-Ranges: bytes\nETag: \"32410b-2c3-3cb2ead0\"\nServer: Apache/1.3.22 (Unix) mod_throttle/3.1.2\nVary: Host\nContent-Length: 707\nContent-Type: text/html\nLast-Modified: Tue, 09 Apr 2002 13:21:2...') -> dbd_st_execute for 081f109c -> mysql_st_interal_execute Binding parameters: INSERT DELAYED INTO Test1 ( Message) VALUES ('FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoylewrong\\nReturn Code: 404\\nReturn Message: Not Found\\nPage Size: 707\\nHeaders: \\nConnection: close\\nDate: Thu, 07 Jul 2005 14:19:58 GMT\\nAccept-Ranges: bytes\\nETag: \\\"32410b-2c3-3cb2ead0\\\"\\nServer: Apache/1.3.22 (Unix) mod_throttle/3.1.2\\nVary: Host\\nContent-Length: 707\\nContent-Type: text/html\\nLast-Modified: Tue, 09 Apr 2002 13:21:20 GMT\\nClient-Date: Thu, 07 Jul 2005 14:20:16 GMT\\nClient-Peer: 212.74.114.59:80\\nClient-Response-Num: 1\\nRefresh: 0; url=http://www.tiscali.co.uk/services/webspace/404.html\\n\\n') <- mysql_st_internal_execute returning rows 1 <- dbd_st_execute returning imp_sth->row_num 1 <- execute= 1 at testfred13.pl line 58 -> finish for DBD::mysql::st (DBI::st=HASH(0x833e7d8)~0x81f12ac) <- finish= 1 at testfred13.pl line 62 -> disconnect for DBD::mysql::db (DBI::db=HASH(0x82093ec)~0x833e658) &imp_dbh->mysql: 833ea44 <- disconnect= 1 at testfred13.pl line 63 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f12ac)~INNER) <- DESTROY= undef -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x833e658)~INNER) <- DESTROY= undef ------------------------------------------------------------------------------------------ The stdout from 2.9008 which works is: ------------------------------------------------------------------------------------------ testfred starts This is perl, v5.8.4 built for i686-linux Copyright 1987-2004, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using `man perl' or `perldoc perl'. If you have access to the Internet, point your browser at http://www.perl.com/, the Perl Home Page. module DBI OK; version: 1.48 module DBD::mysql OK; version: 2.9008 testfred Preparing handle testfred Executing 12 testfred Executing long testfred Ending ------------------------------------------------------------------------------------------ To reproduce the bug: Create a table with a single text field: CREATE TABLE `Test1` ( `Message` text NOT NULL ) TYPE=MyISAM; Then run the following test script - replacing XXX, YYY & ZZZ with your connect paramters:: #!/usr/bin/perl -w $|=1; # DATE: 6/7/5 print "testfred starts\n"; my $version; my $dbh; my $sthdiag; my $ans; my $diagnostic; print `perl -v`; eval "use DBI"; if ($@ ne "") { print("module DBI LOAD FAILED - $@\n"); } else { $version = getversion("DBI"); print "module DBI OK; version: $version\n"; } eval "use DBD::mysql"; if ($@ ne "") { print("module DBD::mysql LOAD FAILED - $@\n"); } else { $version = getversion("DBD::mysql"); print "module DBD::mysql OK; version: $version\n"; } if (!($dbh = DBI->connect('XXX', 'YYY', 'ZZZ', { AutoCommit => 1, RaiseError => 1, PrintError => 1, TraceLevel => 2} ))) { print($dbh->errstr); exit; } print "testfred Preparing handle\n"; $sthdiag = $dbh->prepare(q{ INSERT DELAYED INTO Test1 ( Message) VALUES (?) }); print "testfred Executing 12\n"; $ans = $sthdiag->execute('\"12\"'); print "testfred Executing long\n"; $diagnostic = 'FINAL LOCATION\n\nURL: http://myweb.tiscali.co.uk/dennisdoylewrong\nReturn Code: 404\nReturn Message: Not Found\nPage Size: 707\nHeaders: \nConnection: close\nDate: Thu, 07 Jul 2005 14:19:58 GMT\nAccept-Ranges: bytes\nETag: \"32410b-2c3-3cb2ead0\"\nServer: Apache/1.3.22 (Unix) mod_throttle/3.1.2\nVary: Host\nContent-Length: 707\nContent-Type: text/html\nLast-Modified: Tue, 09 Apr 2002 13:21:20 GMT\nClient-Date: Thu, 07 Jul 2005 14:20:16 GMT\nClient-Peer: 212.74.114.59:80\nClient-Response-Num: 1\nRefresh: 0; url=http://www.tiscali.co.uk/services/webspace/404.html\n\n'; $ans = $sthdiag->execute($diagnostic); print "testfred Ending\n"; $sthdiag->finish(); $dbh->disconnect(); exit; print "testfred ends\n"; exit; sub getversion { my $mod = shift; my $vers = `$^X -m$mod -e 'print \$${mod}::VERSION' 2>/dev/null`; $vers =~ s/^\s*(.*?)\s*$/$1/; # remove stray whitespace return ($vers || undef); } 1;
-> prepare for DBD::mysql::db (DBI::db=HASH(0x8df450c)~0x8ed8d30 ' INSERT DELAYED INTO Test1 ( Message) VALUES (?) ') <- prepare= DBI::st=HASH(0x8ed8ea4) at testfred13.pl line 46 -> execute for DBD::mysql::st (DBI::st=HASH(0x8ed8ea4)~0x8d8b2a0 '\"12\"') -> dbd_st_execute for 08d8b09c Binding parameters: INSERT DELAYED INTO Test1 ( Message) VALUES ('\\\"12\\\"') <- dbd_st_execute returning imp_sth->row_num 1 <- execute= 1 at testfred13.pl line 53 -> execute for DBD::mysql::st (DBI::st=HASH(0x8ed8ea4)~0x8d8b2a0 'FINAL LOCATION\n\nURL: http://myweb.tiscali.co.uk/dennisdoylewrong\nReturn Code: 404\nReturn Message: Not Found\nPage Size: 707\nHeaders: \nConnection: close\nDate: Thu, 07 Jul 2005 14:19:58 GMT\nAccept-Ranges: bytes\nETag: \"32410b-2c3-3cb2ead0\"\nServer: Apache/1.3.22 (Unix) mod_throttle/3.1.2\nVary: Host\nContent-Length: 707\nContent-Type: text/html\nLast-Modified: Tue, 09 Apr 2002 13:21:2...') -> dbd_st_execute for 08d8b09c Binding parameters: INSERT DELAYED INTO Test1 ( Message) VALUES ('FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoylewrong\\nReturn Code: 404\\nReturn Message: Not Found\\nPage Size: 707\\nHeaders: \\nConnection: close\\nDate: Thu, 07 Jul 2005 14:19:58 GMT\\nAccept-Ranges: bytes\\nETag: \\\"32410b-2c3-3cb2ead0\\\"\\nServer: Apache/1.3.22 (Unix) mod_throttle/3.1.2\\nVary: Host\\nContent-Length: 707\\nContent-Type: text/html\\nLast-Modified: Tue, 09 Apr 2002 13:21:20 GMT\\nClient-Date: Thu, 07 Jul 2005 14:20:16 GMT\\nClient-Peer: 212.74.114.59:80\\nClient-Response-Num: 1\\nRefresh: 0; url=http://www.tiscali.co.uk/services/webspac©Binding paramete You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoyl error 1064 recorded: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoyl <- dbd_st_execute returning imp_sth->row_num 18446744073709551614 !! ERROR: 1064 'You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoyl' (err#0) <- execute= undef at testfred13.pl line 58 DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoyl at ./testfred13.pl line 58. DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoyl at ./testfred13.pl line 58. -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x8d8b2a0)~INNER) ERROR: 1064 'You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoyl' (err#0) <- DESTROY= undef -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x8ed8d30)~INNER) &imp_dbh->mysql: 8ed90bc ERROR: 1064 'You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''FINAL LOCATION\\n\\nURL: http://myweb.tiscali.co.uk/dennisdoyl' (err#0) <- DESTROY= undef
From: dennis.doyle [...] morgandoyle.co.uk
[guest - Thu Jul 7 13:56:11 2005]: Show quoted text
> Hi, > > A bug seems to have been introduced between DBD:mysql versions 2.9008 > and 3.0000. > > o/s is: > Linux ultra4.me2uweb.net 2.4.21-27.0.2.ELsmp #1 SMP Wed Jan 12 > 23:35:44 EST 2005 i686 i686 i386 GNU/Linux > > It seems to be related to $dbh->prepare with DELAYED syntax & $sth-
> >execute
> and escaping quotes and/or escaped quotes. > > It looks like corruption of memory from the stderr trace logging
which Show quoted text
> contains control characters so I am unable to past it into this - > see attached file. > > It manfests itself in many different ways > - sometimes a runaway process > - sometimes apparently stalling in the execute > - sometimes producing a segmentation violation > these symptoms change if you make the smallest change to any of the > code, > including changing the tracelevel. > > On 3 different installations of 2.9008 this style of code has been > working fine for months. > > The 2 different installations of 3.0000 this style of code has been > falling all over the place > since 3.0000 was installed on Saturday 2/7/5. > > Hope the following helps: > > The following small testharness reproduces one variant of the bug: > > It returns status code: 65280. > The database field is updated. > The tracelog complains about invalid SQL because the sting being > inserted > appears to have been overwritten towards the end with STX NULL NULL > NULL > > The stdout from 3.0000 which fails is: > > ---------------------------------------------------------------------
--------------------- Show quoted text
> testfred starts > > This is perl, v5.8.4 built for i686-linux > > Copyright 1987-2004, Larry Wall > > Perl may be copied only under the terms of either the Artistic
License Show quoted text
> or the > GNU General Public License, which may be found in the Perl 5 source > kit. > > Complete documentation for Perl, including FAQ lists, should be found > on > this system using `man perl' or `perldoc perl'. If you have access
to Show quoted text
> the > Internet, point your browser at http://www.perl.com/, the Perl Home > Page. > > module DBI OK; version: 1.48 > module DBD::mysql OK; version: 3.0000 > testfred Preparing handle > testfred Executing 12 > testfred Executing long > ---------------------------------------------------------------------
--------------------- Show quoted text
> > The stderr tracelog with connect params removed for security reasons, > from 2.9008 which works is: > > ---------------------------------------------------------------------
--------------------- Show quoted text
> > -> prepare for DBD::mysql::db (DBI::db=HASH(0x82093ec)
~0x833e658 ' Show quoted text
> INSERT DELAYED INTO Test1 ( > Message) VALUES (?) > ') > Setting mysql_use_result to 0 > <- prepare= DBI::st=HASH(0x833e7d8) at testfred13.pl line 46 > -> execute for DBD::mysql::st (DBI::st=HASH(0x833e7d8)~0x81f12ac > '\"12\"') > -> dbd_st_execute for 081f109c > -> mysql_st_interal_execute > Binding parameters: INSERT DELAYED INTO Test1 ( > Message) VALUES ('\\\"12\\\"') > > <- mysql_st_internal_execute returning rows 1 > <- dbd_st_execute returning imp_sth->row_num 1 > <- execute= 1 at testfred13.pl line 53 > -> execute for DBD::mysql::st (DBI::st=HASH(0x833e7d8)~0x81f12ac > 'FINAL LOCATION\n\nURL: > http://myweb.tiscali.co.uk/dennisdoylewrong\nReturn Code: > 404\nReturn Message: Not Found\nPage Size: 707\nHeaders: > \nConnection: close\nDate: Thu, 07 Jul 2005 14:19:58 GMT\nAccept- > Ranges: bytes\nETag: \"32410b-2c3-3cb2ead0\"\nServer:
Apache/1.3.22 Show quoted text
> (Unix) mod_throttle/3.1.2\nVary: Host\nContent-Length: > 707\nContent-Type: text/html\nLast-Modified: Tue, 09 Apr 2002 > 13:21:2...') > -> dbd_st_execute for 081f109c > -> mysql_st_interal_execute > Binding parameters: INSERT DELAYED INTO Test1 ( > Message) VALUES ('FINAL LOCATION\\n\\nURL: > http://myweb.tiscali.co.uk/dennisdoylewrong\\nReturn Code: > 404\\nReturn Message: Not Found\\nPage Size: 707\\nHeaders: > \\nConnection: close\\nDate: Thu, 07 Jul 2005 14:19:58 > GMT\\nAccept-Ranges: bytes\\nETag: \\\"32410b-2c3- > 3cb2ead0\\\"\\nServer: Apache/1.3.22 (Unix) > mod_throttle/3.1.2\\nVary: Host\\nContent-Length: 707\\nContent- > Type: text/html\\nLast-Modified: Tue, 09 Apr 2002 13:21:20 > GMT\\nClient-Date: Thu, 07 Jul 2005 14:20:16 GMT\\nClient-Peer: > 212.74.114.59:80\\nClient-Response-Num: 1\\nRefresh: 0; > url=http://www.tiscali.co.uk/services/webspace/404.html\\n\\n') > > <- mysql_st_internal_execute returning rows 1 > <- dbd_st_execute returning imp_sth->row_num 1 > <- execute= 1 at testfred13.pl line 58 > -> finish for DBD::mysql::st (DBI::st=HASH(0x833e7d8)~0x81f12ac) > <- finish= 1 at testfred13.pl line 62 > -> disconnect for DBD::mysql::db > (DBI::db=HASH(0x82093ec)~0x833e658) > &imp_dbh->mysql: 833ea44 > <- disconnect= 1 at testfred13.pl line 63 > -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81f12ac)~INNER) > <- DESTROY= undef > -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x833e658)~INNER) > <- DESTROY= undef > ---------------------------------------------------------------------
--------------------- Show quoted text
> > The stdout from 2.9008 which works is: > > ---------------------------------------------------------------------
--------------------- Show quoted text
> testfred starts > > This is perl, v5.8.4 built for i686-linux > > Copyright 1987-2004, Larry Wall > > Perl may be copied only under the terms of either the Artistic
License Show quoted text
> or the > GNU General Public License, which may be found in the Perl 5 source > kit. > > Complete documentation for Perl, including FAQ lists, should be found > on > this system using `man perl' or `perldoc perl'. If you have access
to Show quoted text
> the > Internet, point your browser at http://www.perl.com/, the Perl Home > Page. > > module DBI OK; version: 1.48 > module DBD::mysql OK; version: 2.9008 > testfred Preparing handle > testfred Executing 12 > testfred Executing long > testfred Ending > > ---------------------------------------------------------------------
--------------------- Show quoted text
> > To reproduce the bug: > > Create a table with a single text field: > > CREATE TABLE `Test1` ( > `Message` text NOT NULL > ) TYPE=MyISAM; > > Then run the following test script - replacing XXX, YYY & ZZZ with > your connect paramters:: > > #!/usr/bin/perl -w > $|=1; > # DATE: 6/7/5 > > print "testfred starts\n"; > > my $version; > my $dbh; > my $sthdiag; > my $ans; > my $diagnostic; > > print `perl -v`; > > eval "use DBI"; > if ($@ ne "") > { > print("module DBI LOAD FAILED - $@\n"); > } > else > { > $version = getversion("DBI"); > print "module DBI OK; version: $version\n"; > } > > eval "use DBD::mysql"; > if ($@ ne "") > { > print("module DBD::mysql LOAD FAILED - $@\n"); > } > else > { > $version = getversion("DBD::mysql"); > print "module DBD::mysql OK; version: $version\n"; > } > > if (!($dbh = DBI->connect('XXX', 'YYY', 'ZZZ', > { AutoCommit => 1, RaiseError => 1, PrintError => 1,
TraceLevel => 2} Show quoted text
> ))) > { > print($dbh->errstr); > exit; > } > > print "testfred Preparing handle\n"; > > $sthdiag = $dbh->prepare(q{ > INSERT DELAYED INTO Test1 ( > Message) VALUES (?) > }); > > print "testfred Executing 12\n"; > > $ans = $sthdiag->execute('\"12\"'); > > print "testfred Executing long\n"; > > $diagnostic = 'FINAL LOCATION\n\nURL: > http://myweb.tiscali.co.uk/dennisdoylewrong\nReturn Code: > 404\nReturn Message: Not Found\nPage Size: 707\nHeaders: > \nConnection: close\nDate: Thu, 07 Jul 2005 14:19:58 GMT\nAccept- > Ranges: bytes\nETag: \"32410b-2c3-3cb2ead0\"\nServer:
Apache/1.3.22 Show quoted text
> (Unix) mod_throttle/3.1.2\nVary: Host\nContent-Length: > 707\nContent-Type: text/html\nLast-Modified: Tue, 09 Apr 2002 > 13:21:20 GMT\nClient-Date: Thu, 07 Jul 2005 14:20:16 GMT\nClient- > Peer: 212.74.114.59:80\nClient-Response-Num: 1\nRefresh: 0; > url=http://www.tiscali.co.uk/services/webspace/404.html\n\n'; > $ans = $sthdiag->execute($diagnostic); > > print "testfred Ending\n"; > > $sthdiag->finish(); > $dbh->disconnect(); > exit; > > print "testfred ends\n"; > > exit; > > sub getversion > { > my $mod = shift; > my $vers = `$^X -m$mod -e 'print \$${mod}::VERSION' 2>/dev/null`; > $vers =~ s/^\s*(.*?)\s*$/$1/; # remove stray whitespace > return ($vers || undef); > } > 1;
We are pretty confident that the bug relates to the changes to escaping of quotes done by execute for the following reasons: 1. Replacing all occurrences of execute{} with do{} in our code and remoing the quotes in SQL strings ourselves is rapidly reducing the number of failures. 2. do{} does not mess with quotes wheras execute{} does. 3. The todo list for DBD::mysql version 3 says the quoting needs testing, which I assume means it has been changed and it doesn't work. Since we never encountered any problems with execute's escaping of quotes in 2.9008, would it be worth switching that bit of code back to the original - or will that cause faults elsewhere? In the meantime, I'd suggest any other users experiencing the same problems try the workaround suggested above - far from ideal but better than having your processing hanging and/or thrashing. Hope this helps. Dennis Doyle
Download (untitled) / with headers
text/plain 193b
Fixed in latest 3.0007 and 3.0007_1 module DBI OK; version: 1.48 module DBD::mysql OK; version: 3.0007 testfred Preparing handle testfred Executing 12 testfred Executing long testfred Ending


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.