| Subject: | prepare & execute corruption in 3.000 |
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