| Bug #21028 | DBD::mysql does not handle multiple data sets in stored procedures | ||
|---|---|---|---|
| Submitted: | 13 Jul 2006 8:35 | Modified: | 23 Dec 2006 20:50 |
| Reporter: | Giuseppe Maxia | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connectors: DBD::mysql ( Perl ) | Severity: | S2 (Serious) |
| Version: | 3.0002_5 and later | OS: | Linux (Linux) |
| Assigned to: | Alexey Stroganov | CPU Architecture: | Any |
[13 Jul 2006 19:25]
Valeriy Kravchuk
Thank you for a bug report. Verified with a similar test case:
openxs@suse:~/dbs/5.0> cat 21028.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Test::More;
use Data::Dumper;
use English qw( -no_match_vars );
our $VERSION = 0.01;
my $CONF = $ENV{MYCONF} || "$ENV{HOME}/.my.cnf";
my $emulate = 1;
my $dbh;
eval {
#
# change the connection statements
# to suit your purposes
$dbh =
DBI->connect("dbi:mysql:test:127.0.0.1;mysql_emulated_prepare=$emulate",
'root',
'',
{RaiseError => 1, PrintError => 0} )
or die "can't connect : $DBI::errstr\n";
};
if ($EVAL_ERROR) {
plan (skip_all => ' -- no connection available');
}
else {
plan ( tests => 10 );
}
print "\nEmulation of ps: $emulate, version: $DBD::mysql::VERSION\n";
my $drop_proc = qq{
drop procedure if exists test_multi_sets
};
my $create_proc = qq{
create procedure test_multi_sets ()
deterministic
begin
select user() as first_col;
select user() as first_col, now() as second_col;
select user() as first_col, now() as second_col, schema() as third_col;
end
};
eval { $dbh->do($drop_proc) };
ok( ! $EVAL_ERROR, 'drop procedure' );
eval { $dbh->do($create_proc) };
ok( ! $EVAL_ERROR , 'create procedure');
my $sth;
eval { $sth = $dbh->prepare(qq{call test_multi_sets() }) } ;
ok( $sth , 'preparing statement handler');
eval { $sth->execute() };
ok( ! $EVAL_ERROR, 'executing sth - 1st time ' );
diag $EVAL_ERROR if $EVAL_ERROR;
my $dataset;
eval { $dataset = $sth->fetchrow_arrayref(); } ;
ok( $dataset && @$dataset == 1 , 'fetching first dataset');
my $more_results;
eval { $more_results = $sth->more_results() };
ok( $more_results, 'more results available (1st time) ' ) ;
eval { $dataset = $sth->fetchrow_arrayref(); } ;
ok( $dataset && @$dataset == 2 , 'fetching second dataset');
eval { $more_results = $sth->more_results() };
ok( $more_results, 'more results available (2nd time) ' ) ;
eval { $dataset = $sth->fetchrow_arrayref(); } ;
ok( $dataset && @$dataset == 3 , 'fetching third dataset');
eval { $more_results = $sth->more_results() };
ok( ! $more_results, 'no more results available' ) ;
I've got the following results:
openxs@suse:~/dbs/5.0> perl -w 21028.pl
1..10
Emulation of ps: 0, version: 3.0006_1
ok 1 - drop procedure
ok 2 - create procedure
ok 3 - preparing statement handler
ok 4 - executing sth - 1st time
ok 5 - fetching first dataset
ok 6 - more results available (1st time)
not ok 7 - fetching second dataset
# Failed test (21028.pl at line 74)
ok 8 - more results available (2nd time)
not ok 9 - fetching third dataset
# Failed test (21028.pl at line 80)
ok 10 - no more results available
# Looks like you failed 2 tests of 10.
openxs@suse:~/dbs/5.0> vi 21028.pl
openxs@suse:~/dbs/5.0> perl -w 21028.pl
1..10
Emulation of ps: 1, version: 3.0006_1
ok 1 - drop procedure
ok 2 - create procedure
ok 3 - preparing statement handler
ok 4 - executing sth - 1st time
ok 5 - fetching first dataset
ok 6 - more results available (1st time)
not ok 7 - fetching second dataset
# Failed test (21028.pl at line 74)
ok 8 - more results available (2nd time)
not ok 9 - fetching third dataset
# Failed test (21028.pl at line 80)
ok 10 - no more results available
# Looks like you failed 2 tests of 10.
[23 Dec 2006 20:50]
Patrick Galbraith
This patch is approved and has fixed the problem. In version 4.0.0 on cpan subversion tree for DBD::mysql
Description: in version 3.0002_4, DBD::mysql handled multiple data sets from a stored procedure call. All versions after that don't have this feature any longer. I am talking about procedures of this sort: create procedtre testproc() deterministic begin select a, b, c from t1; select a, b from t1; select a, b, c, d from t1; end The test case provided passes with DBD::mysql 3.0002_4 and fails with any othe version. The test provided with DBD::mysql (t/80procs.t) does not catch this problem because it will only create a multi-set procedure but *does not call it*. How to repeat: #!/usr/bin/perl use strict; use warnings; use DBI; use Test::More; use Data::Dumper; use English qw( -no_match_vars ); our $VERSION = 0.01; my $CONF = $ENV{MYCONF} || "$ENV{HOME}/.my.cnf"; my $dbh; eval { # # change the connection statements # to suit your purposes $dbh = DBI->connect('dbi:mysql:test' #. ";mysql_emulated_prepare=1" . ";mysql_read_default_file=$CONF", undef, undef, {RaiseError => 1, PrintError => 0} ) or die "can't connect : $DBI::errstr\n"; }; if ($EVAL_ERROR) { plan (skip_all => ' -- no connection available'); } else { plan ( tests => 10 ); } my $drop_proc = qq{ drop procedure if exists test_multi_sets }; my $create_proc = qq{ create procedure test_multi_sets () deterministic begin select user() as first_col; select user() as first_col, now() as second_col; select user() as first_col, now() as second_col, schema() as third_col; end }; eval { $dbh->do($drop_proc) }; ok( ! $EVAL_ERROR, 'drop procedure' ); eval { $dbh->do($create_proc) }; ok( ! $EVAL_ERROR , 'create procedure'); my $sth; eval { $sth = $dbh->prepare(qq{call test_multi_sets() }) } ; ok( $sth , 'preparing statement handler'); eval { $sth->execute() }; ok( ! $EVAL_ERROR, 'executing sth - 1st time ' ); diag $EVAL_ERROR if $EVAL_ERROR; my $dataset; eval { $dataset = $sth->fetchrow_arrayref(); } ; ok( $dataset && @$dataset == 1 , 'fetching first dataset'); my $more_results; eval { $more_results = $sth->more_results() }; ok( $more_results, 'more results available (1st time) ' ) ; eval { $dataset = $sth->fetchrow_arrayref(); } ; ok( $dataset && @$dataset == 2 , 'fetching second dataset'); eval { $more_results = $sth->more_results() }; ok( $more_results, 'more results available (2nd time) ' ) ; eval { $dataset = $sth->fetchrow_arrayref(); } ; ok( $dataset && @$dataset == 3 , 'fetching third dataset'); eval { $more_results = $sth->more_results() }; ok( ! $more_results, 'no more results available' ) ; Suggested fix: Patch the other versions with the code that worked for 3.0002_4. Include this test in the test suite.