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

[18 Jan 2008 18:34] Andrey Zapariy
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'}.
[31 Jan 2008 21:43] Sveta Smirnova
Thank you for the report.

Could you please provide an example when described behavior leads to "out of sync" error?
[1 Mar 2008 0:01] Bugs System
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".
[3 Mar 2008 10:54] Andrey Zapariy
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<---
[17 May 2008 19:55] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[24 Jul 2008 21:07] Chris Heath
I posted a patch at http://rt.cpan.org/Public/Bug/Display.html?id=36432 which I believe fixes this problem too.
[20 Feb 2013 22:18] Sveta Smirnova
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.