Skip Menu |
 

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

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

People
Owner: CAPTTOFU [...] cpan.org
Requestors: gert.pache [...] tngtech.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in:
  • 4.007
  • 4.008
  • 4.009
  • 4.010
  • 4.011
  • 4.012
Fixed in: 4.013



Subject: BUG with prepared statements and mysql_server_prepare=1 caused by NULL values
Download (untitled) / with headers
text/plain 370b
When enabling mysql_server_prepare the dbd::mysql driver reproducibly sends corrupted data to mysql when: * NULL values are used * and the prepared statement is reused. The attached test.pl script illustrates such an error condition. The attached dbd_mysql_4.012_pstmt_null_patch suggests a solution which both passes the dbd::mysql test suite and the test.pl script.
Subject: test.pl
Download test.pl
text/x-perl 1.6k
use strict; use DBI; use DBI qw(:sql_types); use Data::Dumper; # Using mysql_server_prepare!!! my $dbh = DBI->connect("DBI:mysql:dbname=test;host=127.0.0.1;port=3306;mysql_server_prepare=1", "root", "") or die $!; $dbh->do("DROP TABLE IF EXISTS test"); $dbh->do("create table test (id int, value0 varchar(10), value1 varchar(10), value2 varchar(10))"); my $sth_insert = $dbh->prepare("INSERT INTO test (id, value0, value1, value2) VALUES (?, ?, ?, ?)"); my $sth_lookup = $dbh->prepare("SELECT * FROM test where id=?"); # Insert null value $sth_insert->bind_param(1, 42, SQL_WVARCHAR); $sth_insert->bind_param(2, 102, SQL_WVARCHAR); $sth_insert->bind_param(3, undef, SQL_WVARCHAR); $sth_insert->bind_param(4, 10004, SQL_WVARCHAR); $sth_insert->execute(); # Insert afterwards none null value # This will insert currently (DBD::MySQL-4.012) corrupted data.... # incorrect use of MYSQL_TYPE_NULL in prepared statement in dbdimp.c $sth_insert->bind_param(1, 43, SQL_WVARCHAR); $sth_insert->bind_param(2, 2002, SQL_WVARCHAR); $sth_insert->bind_param(3, 20003, SQL_WVARCHAR); $sth_insert->bind_param(4, 200004, SQL_WVARCHAR); $sth_insert->execute(); # verify $sth_lookup->execute(42); my $row_id_42 = $sth_lookup->fetchrow_hashref(); $sth_lookup->execute(43); my $row_id_43 = $sth_lookup->fetchrow_hashref(); print "Row1: ", Dumper($row_id_42); print "Row2: ", Dumper($row_id_43); # the following line failes in DBD::MySQL-4.012, since row with id==43 (second insert) is corrupted. $row_id_43->{value0} eq "2002" or die "Row: id=43, expected value0='2002' but got: '$row_id_43->{value0}'"; # never reached in DBD::MySQL-4.012 print "Test finished without error.\n"
Subject: dbd_mysql_4.012_pstmt_null_patch
Download dbd_mysql_4.012_pstmt_null_patch
application/octet-stream 5.2k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 4.7k
On Tue Sep 15 11:14:06 2009, gpache wrote: Show quoted text
> When enabling mysql_server_prepare the dbd::mysql driver reproducibly > sends corrupted data to mysql when: > * NULL values are used > * and the prepared statement is reused. > > The attached test.pl script illustrates such an error condition. > > The attached dbd_mysql_4.012_pstmt_null_patch suggests a solution which > both passes the dbd::mysql test suite and the test.pl script.
Hi there! Thank you for this patch, and I do apologize for taking so long to address this. I cannot reproduce this failure you are encountering, at least using MariaDB (which has many improvements, including to the client library) this is running your script before the patch is applied on OS X, MariaDB 5.1.35-maria-beta1-log: patrick-galbraiths-macbook-pro:trunk patg$ ~/perl/nulls_prepare_bind.pl Row1: $VAR1 = { 'value1' => undef, 'value0' => '102', 'id' => 42, 'value2' => '10004' }; Row2: $VAR1 = { 'value1' => '20003', 'value0' => '2002', 'id' => 43, 'value2' => '200004' }; Test finished without error. Looking at the log, if turned on, this is what I see: 100411 22:33:12 1 Connect root@localhost on test 1 Query set autocommit=1 1 Prepare DROP TABLE IF EXISTS test 1 Execute DROP TABLE IF EXISTS test 1 Close stmt 1 Prepare create table test (id int, value0 varchar(10), value1 varchar(10), value2 varchar(10)) 1 Execute create table test (id int, value0 varchar(10), value1 varchar(10), value2 varchar(10)) 1 Close stmt 1 Prepare INSERT INTO test (id, value0, value1, value2) VALUES (?, ?, ?, ?) 1 Prepare SELECT * FROM test where id=? 1 Execute INSERT INTO test (id, value0, value1, value2) VALUES ('42', '102', NULL, '10004') 1 Execute INSERT INTO test (id, value0, value1, value2) VALUES ('43', '2002', '20003', '200004') 1 Execute SELECT * FROM test where id='42' 1 Execute SELECT * FROM test where id='43' 1 Close stmt 1 Close stmt 1 Quit However, on Linux, using MySQL 5.0 (5.0.51a-3ubuntu5.4-log) patg@hanuman:~$ ./nulls_prepare_bind.pl Row1: $VAR1 = { 'value1' => undef, 'value0' => '102', 'id' => 42, 'value2' => '10004' }; Row2: $VAR1 = undef; Row: id=43, expected value0='2002' but got: '' at ./nulls_prepare_bind.pl line 45. 100411 22:42:37 432200 Connect root@localhost on test 432200 Query set autocommit=1 432200 Prepare [1] DROP TABLE IF EXISTS test 432200 Execute [1] DROP TABLE IF EXISTS test 432200 Prepare [2] create table test (id int, value0 varchar(10), value1 varchar(10), value2 varchar(10)) 432200 Execute [2] create table test (id int, value0 varchar(10), value1 varchar(10), value2 varchar(10)) 432200 Prepare [3] INSERT INTO test (id, value0, value1, value2) VALUES (?, ?, ?, ?) 432200 Prepare [4] SELECT * FROM test where id=? 432200 Execute [3] INSERT INTO test (id, value0, value1, value2) VALUES ('42', '102', NULL, '10004') 432200 Execute [3] INSERT INTO test (id, value0, value1, value2) VALUES ('\0\0', '2002', '20003', '200004') 432200 Execute [4] SELECT * FROM test where id='42' 432200 Execute [4] SELECT * FROM test where id='43' 432200 Quit So, obviously, something was wrong with the MySQL client libraries that is fixed in Maria! With your patch applied on Linux (MySQL 5.0) patg@hanuman:~/patched/DBD-mysql-4.012$ ~/nulls_prepare_bind.pl Row1: $VAR1 = { 'value1' => undef, 'value0' => '102', 'id' => 42, 'value2' => '10004' }; Row2: $VAR1 = undef; Row: id=43, expected value0='2002' but got: '' at /home/patg/nulls_prepare_bind.pl line 45. patg@hanuman:~/patched/DBD-mysql-4.012$ sudo make install Installing /usr/local/lib/perl/5.8.8/auto/DBD/mysql/mysql.so Files found in blib/arch: installing files in blib/lib into architecture dependent library tree Installing /usr/local/lib/perl/5.8.8/DBD/mysql.pm Installing /usr/local/man/man3/DBD::mysql.3pm Installing /usr/local/man/man3/DBD::mysql::INSTALL.3pm Installing /usr/local/man/man3/Bundle::DBD::mysql.3pm Writing /usr/local/lib/perl/5.8.8/auto/DBD/mysql/.packlist Appending installation info to /usr/local/lib/perl/5.8.8/perllocal.pod patg@hanuman:~/patched/DBD-mysql-4.012$ ~/nulls_prepare_bind.pl Row1: $VAR1 = { 'value1' => undef, 'value0' => '102', 'id' => 42, 'value2' => '10004' }; Row2: $VAR1 = { 'value1' => '20003', 'value0' => '2002', 'id' => 43, 'value2' => '200004' }; Test finished without error. patg@hanuman:~/patched/DBD-mysql-4.012$ So, I will include your kind work in 4.013 because most installations will probably still be using 5.0 Thank you!
This fix is now in git and will be in 4.014


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.