Description:
Consider simple script:
openxs@suse:~/dbs/4.1> cat 9840a.pl
use strict;
use DBI;
my $dbh = DBI->connect( "dbi:mysql:test:host=127.0.0.1:port=3306",
'root', '', {RaiseError=>1, PrintError=>0} );
$dbh->do( "drop table if exists update_bug" );
$dbh->do( "create table update_bug( k int not null primary key, status int not null )" );
my $v = '4000930012345671';
$dbh->do( "update update_bug set status = 5 where k = ?", undef, substr( $v, -11) ); # OK
$dbh->do( "update update_bug set status = 5 where k = ?", undef, substr( $v, -11 ) ); # OK
cancel( $v ); # OK
cancel( $v ); # bug - k is NULL
sub cancel {
my ( $dcref ) = @_;
print "DCREF: $dcref\n";
$dbh->do( "update update_bug set status = 5 where k = ?", undef, substr( $dcref, -11 ) );
}
openxs@suse:~/dbs/4.1> perl -w 9840a.pl
DCREF: 4000930012345671
DCREF: 4000930012345671
openxs@suse:~/dbs/4.1> tail var/suse.log
19 Quit
060527 10:47:35 20 Connect root@localhost on test
20 Query SET AUTOCOMMIT=1
20 Query drop table if exists update_bug
20 Query create table update_bug( k int not null prim
ary key, status int not null )
20 Query update update_bug set status = 5 where k = '30012345671'
20 Query update update_bug set status = 5 where k = '30012345671'
20 Query update update_bug set status = 5 where k = '30012345671'
20 Query update update_bug set status = 5 where k = NULL
20 Quit
When cancel() is called several times, for second and subsequent executions NULL is substituted instead of real value for parameter. It does not depend on MySQL server version used.
How to repeat:
Run MySQL server with general query log. Execute the following simple Perl script:
use strict;
use DBI;
my $dbh = DBI->connect( "dbi:mysql:test:host=127.0.0.1:port=3306",
'root', '', {RaiseError=>1, PrintError=>0} );
$dbh->do( "drop table if exists update_bug" );
$dbh->do( "create table update_bug( k int not null primary key, status int not null )" );
my $v = '4000930012345671';
$dbh->do( "update update_bug set status = 5 where k = ?", undef, substr( $v, -11));
$dbh->do( "update update_bug set status = 5 where k = ?", undef, substr( $v, -11));
cancel( $v );
cancel( $v );
sub cancel {
my ( $dcref ) = @_;
print "DCREF1: $dcref\n";
$dbh->do("update update_bug set status = 5 where k = ?", undef, substr($dcref, -11));
}
Check query log.
Suggested fix:
Maybe, it has something to do with prepared statements handling...