Description:
When using server-side prepared insert statements with DBD::mysql, an undef in the first
execute causes the values of that field in every subsequent execute to be ignored (null'd).
If the undef is encountered in a later row, the subsequent rows are inserted correctly.
E.g.
inserting [[ undef],['some value'],['another value]], in a table with just one texfield, will give three rows with NULL, while
inserting [['some value'],undef,['another value']] yields the expected result.
See the script below.
When using 'mysql_emulated_prepare=1' the problem is gone.
How to repeat:
#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect("DBI:mysql:test:localhost;mysql_emulated_prepare=0",'pgl','');
$dbh->trace('5','/tmp/trace.pgl');
$dbh->trace_msg("\n--------------- start correct case ------------------------\n");
$dbh->do("DROP TABLE IF EXISTS _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr;
$dbh->do("CREATE TABLE _tmp_tab_pgl ( field1 VARCHAR(20))") or die $DBI::err." : ".$DBI::errstr;
my $sth = $dbh->prepare("INSERT INTO _tmp_tab_pgl VALUES (?)") or die $DBI::err." : ".$DBI::errstr;
my $data =
[
[ 'some value' ],
[ undef ],
[ 'another value' ],
];
for my $d (@{$data}) {
$sth->execute(@{$d}) or die $DBI::err." : ".$DBI::errstr;
}
$sth->finish();
$sth = $dbh->prepare("SELECT * FROM _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr;
$sth->execute() or die $DBI::err." : ".$DBI::errstr;
while (my $row = $sth->fetchrow_arrayref()) {
print join(' : ',map { (defined($_) ? $_ : 'NULL') } @{$row}),"\n";
}
$dbh->trace_msg("\n--------------- end correct case ------------------------\n");
$dbh->trace_msg("\n--------------- start faulty case ------------------------\n");
$dbh->do("DROP TABLE IF EXISTS _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr;
$dbh->do("CREATE TABLE _tmp_tab_pgl ( field1 VARCHAR(20))") or die $DBI::err." : ".$DBI::errstr;
$sth = $dbh->prepare("INSERT INTO _tmp_tab_pgl VALUES (?)") or die $DBI::err." : ".$DBI::errstr;
$data =
[
[ undef ],
[ 'some value' ],
[ 'another value' ],
];
for my $d (@{$data}) {
$sth->execute(@{$d}) or die $DBI::err." : ".$DBI::errstr;
}
$sth->finish();
$sth = $dbh->prepare("SELECT * FROM _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr;
$sth->execute() or die $DBI::err." : ".$DBI::errstr;
while (my $row = $sth->fetchrow_arrayref()) {
print join(' : ',map { (defined($_) ? $_ : 'NULL') } @{$row}),"\n";
}
$dbh->trace_msg("\n--------------- end faulty case ------------------------\n");
Description: When using server-side prepared insert statements with DBD::mysql, an undef in the first execute causes the values of that field in every subsequent execute to be ignored (null'd). If the undef is encountered in a later row, the subsequent rows are inserted correctly. E.g. inserting [[ undef],['some value'],['another value]], in a table with just one texfield, will give three rows with NULL, while inserting [['some value'],undef,['another value']] yields the expected result. See the script below. When using 'mysql_emulated_prepare=1' the problem is gone. How to repeat: #!/usr/bin/perl -w use DBI; my $dbh = DBI->connect("DBI:mysql:test:localhost;mysql_emulated_prepare=0",'pgl',''); $dbh->trace('5','/tmp/trace.pgl'); $dbh->trace_msg("\n--------------- start correct case ------------------------\n"); $dbh->do("DROP TABLE IF EXISTS _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr; $dbh->do("CREATE TABLE _tmp_tab_pgl ( field1 VARCHAR(20))") or die $DBI::err." : ".$DBI::errstr; my $sth = $dbh->prepare("INSERT INTO _tmp_tab_pgl VALUES (?)") or die $DBI::err." : ".$DBI::errstr; my $data = [ [ 'some value' ], [ undef ], [ 'another value' ], ]; for my $d (@{$data}) { $sth->execute(@{$d}) or die $DBI::err." : ".$DBI::errstr; } $sth->finish(); $sth = $dbh->prepare("SELECT * FROM _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr; $sth->execute() or die $DBI::err." : ".$DBI::errstr; while (my $row = $sth->fetchrow_arrayref()) { print join(' : ',map { (defined($_) ? $_ : 'NULL') } @{$row}),"\n"; } $dbh->trace_msg("\n--------------- end correct case ------------------------\n"); $dbh->trace_msg("\n--------------- start faulty case ------------------------\n"); $dbh->do("DROP TABLE IF EXISTS _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr; $dbh->do("CREATE TABLE _tmp_tab_pgl ( field1 VARCHAR(20))") or die $DBI::err." : ".$DBI::errstr; $sth = $dbh->prepare("INSERT INTO _tmp_tab_pgl VALUES (?)") or die $DBI::err." : ".$DBI::errstr; $data = [ [ undef ], [ 'some value' ], [ 'another value' ], ]; for my $d (@{$data}) { $sth->execute(@{$d}) or die $DBI::err." : ".$DBI::errstr; } $sth->finish(); $sth = $dbh->prepare("SELECT * FROM _tmp_tab_pgl") or die $DBI::err." : ".$DBI::errstr; $sth->execute() or die $DBI::err." : ".$DBI::errstr; while (my $row = $sth->fetchrow_arrayref()) { print join(' : ',map { (defined($_) ? $_ : 'NULL') } @{$row}),"\n"; } $dbh->trace_msg("\n--------------- end faulty case ------------------------\n");