Possible bug in execute_array with dbd::mysql

Hi,

I think I may have found a bug in DBD::mysql for execute_array.

The following code demonstrates:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
#'DBI:ODBC:mjetest', 'xxx', 'yyy',
) or die "DBI::errstr";

my ($sth, $ref, $rowval, @row, $affected, $param, $inserted, @tuple_status);

$dbh->do(q/drop table if exists mytest/);
$dbh->do(q/create table mytest (a int primary key, b char(20))/);
$sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->execute(1, 'one');
$sth->execute(2, 'two');
$sth->execute(3,, 'three');

$sth->bind_param_array(1, [51,1,52,53]);
$sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
$inserted = $sth->execute_array( { ArrayTupleStatus => \@tuple_status } );
print join(",", @tuple_status), "\n";
print Dumper(\@tuple_status), "\n";

which when run produces:

DBD::mysql::st execute_array failed: Duplicate entry '1' for key 1 at z.pl line
23.
1,ARRAY(0x82b3ea0),ARRAY(0x82b3e94),ARRAY(0x82b3f00)
$VAR1 = [
1,
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
],
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
],
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
]
];

Note, the tuple_status array contains reference for the 2nd, 3rd and 4th
execute although the 3rd and 4th execute work - checked in the table which
shows:

a:b:
1:one:
2:two:
3:three:
51:fiftyone:
52:fiftytwo:
53:fiftythree:

afterwards. If I simply switch to DBD:ODBC and use myodbc it correctly
displays:

1,ARRAY(0x8293db4),1,1
$VAR1 = [
1,
[
-1,
'[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-5.0.15-log]Duplicate
entry \'1\' for key 1 (SQL-23000)(DBD: st_execute/
SQLExecute err=-1)',
'23000'
],
1,
1
];

and the table contents are the same.

I have not yet been able to fix this.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

Re: Possible bug in execute_array with dbd::mysql

The problem I reported is actually worse than it seems.
When I debug the code previously posted I find execute_array just calls
execute multiple times with the different parameters and in fact the
problem is that once an execute fails all following executes also fail.

This code (not using execute_array) fails:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
#'DBI:ODBC:mjetest', 'xxx', 'yyy',
) or die "DBI::errstr";

my ($sth, $ref, $rowval, @row, $affected, $param, $inserted, @tuple_status);

$dbh->do(q/drop table if exists mytest/);
$dbh->do(q/create table mytest (a int primary key, b char(50))/);
$sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->execute(1, 'one');
$sth->execute(2, 'two');
$sth->execute(3,, 'three');

#$sth->bind_param_array(1, [51,1,52,53]);
#$sth->bind_param_array(2, ['fiftyone', 'one', 'fiftytwo', 'fiftythree']);
#$inserted = $sth->execute_array( { ArrayTupleStatus => \@tuple_status } );
#print join(",", @tuple_status), "\n";
#print Dumper(\@tuple_status), "\n";

$sth->execute(99, 'ninetynine');
$sth->execute(1, 'one');
$sth->execute(999, 'ninehundredandninetynine');

with:

bash-2.05$ perl z.pl
DBD::mysql::st execute failed: Duplicate entry '1' for key 1 at z.pl
line 28.
DBD::mysql::st execute failed: Duplicate entry '1' for key 1 at z.pl
line 29.

and yet the db contains:

> select * from mytest;
a:b:
1:one:
2:two:
3:three:
99:ninetynine:
999:ninehundredandninetynine:

showing the last execute actually worked!

As before, if DBD::ODBC and myodbc is used it works correctly so
this looks like an issue in dbd::mysql.

Martin

Martin J. Evans wrote:
> Hi,
>
> I think I may have found a bug in DBD::mysql for execute_array.
>
> The following code demonstrates:
>
> #!/usr/bin/perl
> use strict;
> use warnings;
> use DBI;
> use Data::Dumper;
>
> my $dbh = DBI->connect(
> 'DBI:mysql:mjetest', 'xxx', 'yyy',
> #'DBI:ODBC:mjetest', 'xxx', 'yyy',
> ) or die "DBI::errstr";
>
> my ($sth, $ref, $rowval, @row, $affected, $param, $inserted, @tuple_status);
>
> $dbh->do(q/drop table if exists mytest/);
> $dbh->do(q/create table mytest (a int primary key, b char(20))/);
> $sth = $dbh->prepare(q/insert into mytest values (?,?)/);
> $sth->execute(1, 'one');
> $sth->execute(2, 'two');
> $sth->execute(3,, 'three');
>
> $sth->bind_param_array(1, [51,1,52,53]);
> $sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
> $inserted = $sth->execute_array( { ArrayTupleStatus => \@tuple_status } );
> print join(",", @tuple_status), "\n";
> print Dumper(\@tuple_status), "\n";
>
> which when run produces:
>
> DBD::mysql::st execute_array failed: Duplicate entry '1' for key 1 at z.pl line
> 23.
> 1,ARRAY(0x82b3ea0),ARRAY(0x82b3e94),ARRAY(0x82b3f00)
> $VAR1 = [
> 1,
> [
> 1062,
> 'Duplicate entry \'1\' for key 1',
> 'S1000'
> ],
> [
> 1062,
> 'Duplicate entry \'1\' for key 1',
> 'S1000'
> ],
> [
> 1062,
> 'Duplicate entry \'1\' for key 1',
> 'S1000'
> ]
> ];
>
> Note, the tuple_status array contains reference for the 2nd, 3rd and 4th
> execute although the 3rd and 4th execute work - checked in the table which
> shows:
>
> a:b:
> 1:one:
> 2:two:
> 3:three:
> 51:fiftyone:
> 52:fiftytwo:
> 53:fiftythree:
>
> afterwards. If I simply switch to DBD:ODBC and use myodbc it correctly
> displays:
>
> 1,ARRAY(0x8293db4),1,1
> $VAR1 = [
> 1,
> [
> -1,
> '[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-5.0.15-log]Duplicate
> entry \'1\' for key 1 (SQL-23000)(DBD: st_execute/
> SQLExecute err=-1)',
> '23000'
> ],
> 1,
> 1
> ];
>
> and the table contents are the same.
>
> I have not yet been able to fix this.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
>
>