Bug #20104 DBD::MySQL loses data (with dbh->do in subroutine)
Submitted: 27 May 2006 10:03 Modified: 29 Dec 2006 18:30
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S3 (Non-critical)
Version:3.0004, older versions OS:Linux (SuSE 9.3, Debian, Ubuntu)
Assigned to: CPU Architecture:Any

[27 May 2006 10:03] Valeriy Kravchuk
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...
[31 May 2006 18:27] Valeriy Kravchuk
Looks like the reason for this behaviour is a Perl bug. See ticket 39247 at perl.org.
[1 Jun 2006 15:14] Valeriy Kravchuk
Closed as real reason for the behaviour described is a bug in Perl 5.8.x.
[29 Dec 2006 18:30] Jim Winstead
I have patched a couple of places where magical values might have still caused trouble. It will be in the next release of DBD::mysql (probably 4.01).