MySQL Bugs: #33928: more_results incorrectly interprets value returned by mysql_next

Bug #33928	more_results incorrectly interprets value returned by mysql_next_result()
Submitted:	18 Jan 2008 18:34	Modified:	20 Feb 2013 22:18
Reporter:	Andrey Zapariy	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S2 (Serious)
Version:	4.006	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
DBD::mysql method more_results incorrectly interprets value returned by mysql_next_result(). When one constructs multiple update or insert requests into one query ther is no way to process execution of this query with 
do { } while ($stm->more_results)
loop construct, since for subrequests that are not returning result set more_results always return 0

The bug itself arises because of handling mysql_next_result() return value both in methods  dbd_st_more_results() from the mysql.c and more_results from mysql.xs. The main deficiency in current processing is loss of state when zero is erroneously returned by dbd_st_more_results() if imp_sth->result == NULL. Look at the comment /* No "real" rowset*/. Regardless of the availability of the result set we still should process all possible results from all subrequests.

How to repeat:
1. Connect to database with mysql_multi_statements=1. For example:
my $dbh = DBI->connect("dbi:mysql:database=test;hostname=localhost;mysql_multi_statements=1", 'test', '', {RaiseError => 0, AutoCommit => 0});
2. Compose and prepare three replace statement in one query. For example:
my $sth = $dbh->prepare('replace into table1 values (1); replace into table2 values (2); replace into table3 values (3)');
3. Execute, like:
$sth->execute();
4. Check for available results:
my $res = $sth->more_results;
print "more_results returned $res\n";

You'll see 'more_results returned 0' message. But we should call more_results at least one more time or we will loose sync in given connection.

Suggested fix:
Even if imp_sth->result == NULL dbd_st_more_results() should return 1. The check whether there is data in result set is made via $stm->{'NUM_OF_FIELDS'}.

Thank you for the report.

Could you please provide an example when described behavior leads to "out of sync" error?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sorry. Missed request in email. Following is a simple perl snippet that exhibits specified behavior:
---8<---
#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dumper;
my $res;
my $stins;
my $stsel;
my $nfields;

my $dbh = DBI->connect("dbi:mysql:database=innodata;hostname=localhost;mysql_multi_statements=1", 'someuser', 'somepwd', {RaiseError => 0, AutoCommit => 0});
unless($dbh) {
    print "DB connection failed with error: $DBI::errstr\n";
    exit 1;
}

$stsel = $dbh->prepare('replace into source values (6); replace into moresrc values (2); select uid from source where uid=6');
unless(defined($stsel)) {
    warn($dbh->errstr);
    exit 2;
}   
$res = $stsel->execute();
unless(defined($res)) {
    warn($dbh->errstr);
    exit 3;
}   

do {
    $nfields = $stsel->{'NUM_OF_FIELDS'};
    print "\$nfields is ".(defined($nfields)?$nfields:"not defined")."\n";
    if($nfields) {
        $res = $stsel->{'NAME'};
        print Data::Dumper->Dump([$res], [qw(NAMES)]). "\n";
        $res = $stsel->fetchall_arrayref();
        unless(defined($res)) {
            warn($dbh->errstr);
            exit 5;
        }
        print Data::Dumper->Dump([$res], [qw(results)]). "\n";
    }
} while ($stsel->more_results);

$stsel->finish();

$dbh->commit();
$dbh->disconnect();

exit 0;
---8<---

Now, for example, correct output may look like:
---8<---
$nfields is not defined
$nfields is 0
$nfields is 1
$NAMES = [
           'uid'
         ];

$results = [
             [
               '6'
             ]
           ];
---8<---

And without patch output will look like:
---8<---
$nfields is not defined
DBD::mysql::db commit failed: Commands out of sync; you can't run this command now at ./multiple.pl line 45.
---8<---

Thank you for the feedback.

Verified as described.

I posted a patch at http://rt.cpan.org/Public/Bug/Display.html?id=36432 which I believe fixes this problem too.

Thank you for the report.

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

Since there is a patch which can resolve this issue, please, try it (currecnt version 4.0.22 should contain the patch) and if problem still exists re-open bug at CPAN.