MySQL Bugs: #19927: C API - Incorrect return value?!

Bug #19927	C API - Incorrect return value?!
Submitted:	19 May 2006 7:18	Modified:	4 Feb 2007 3:43
Reporter:	Ian Sparrowhawk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Using the C API, with the MYSQL_OPTION_MULTI_STATEMENTS_ON option set, the mysql_commit command returns 1 with mysql_error reporting "Lost connection to MySQL server during query".  However, inspection of the database shows that the data has been written and commited (insertion of two rows into the same table).  

The MySQL documentation states that the return value for this call is, as you'd expect, "Zero if successful. Non-zero if an error occurred." and this is indeed the return value seen when calling mysql_commit having affected the insertions with two separate mysql_real_query calls.

How to repeat:
Using the C API, attempt to affect two row insertions into a table within a single mysql_query call (as detailed in 26.2.9. C API Handling of Multiple Query Execution)

The table creation script and c code I used are as follows:

DROP TABLE IF EXISTS T1;
CREATE TABLE T1 (
  ID1 int(3) NOT NULL default 0,
  Name1 char(35) NOT NULL default '',
  Number1 int(4) NOT NULL default 0,
  PRIMARY KEY  (ID1)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;

#include <mysql.h>
#include <iostream>

MYSQL     *connection;
MYSQL_RES *queryresult;
MYSQL_ROW row;
char      *query1;
int       retval;

#define AUTOCOMMIT_OFF 0

int main()
{
   connection = mysql_init(NULL);
   if (connection == NULL)
   {
      std::cout << "Failed to get connection handler\n";
      exit(1);
   }

   mysql_real_connect(connection, 
                      "localhost", //hostname
                      "root",      //username
                      NULL,        //password
                      "world2",    //database
                      0,           //port
                      NULL,        //socket name
                      0);
   if (connection == NULL)
   {
      std::cout << "Failed to connect to DB\n";
   }
   else
   {
      if (mysql_autocommit(connection, AUTOCOMMIT_OFF) != 0)
      {
         std::cout << "Couldn't turn autocommit off\n";
      }
      else
      {
         if (mysql_set_server_option(connection, 
             MYSQL_OPTION_MULTI_STATEMENTS_ON) != 0)
         {
            std::cout << "Failed to turn on multi statements\n";
         }
         else
         {
            std::cout << "Connected to DB\n";
            query1 = "insert into T1 values (1, 'One', 11);\
                          insert into T1 values (2, 'Two', 12)";
            retval = mysql_real_query(connection, query1, strlen(query1));
            if (retval != 0)
            {
               std::cout << "Error executing query\n";
               std::cout << mysql_error(connection) << "\n";
            }
            else
            {
               std::cout << "Inserts successful\n";
               retval = mysql_commit(connection);
               if (retval != 0)
               {
                  std::cout << "Failed to commit, retval = " << retval << "\n";
                  std::cout << mysql_error(connection) << "\n";
               }
            }
         }
      }
   }
   mysql_close(connection);
}

Suggested fix:
The mysql_commit return value should be consistent regardless of what mysql_set_server_option attributes have been set

If you could please inform me of your decision on this and any progress I'd be most grateful!

Best regards,

Ian Sparrowhawk

ian.sparrowhawk@ipl.com

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

See http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
you need to cycle through all result sets even for DML statements

The section on multiple queries doesn't explicitly say that even DML
statements must be processed using mysql_next_result() if multiple
statement support is enabled. I'll add a note to that effect.

Isn't it a bug that mysql_error returns "Lost connection to MySQL server during query" when in fact the problem is more like "Can not process statement until previous result sets are read", or has the connection actually be closed by the time mysql_error is called?

The server closes the connection when it detects new queries coming in out of sync (before results have been processed completely)

Unfortunately I was going by 22.2.3.68. mysql_store_result() which states that:

"You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, and so forth). 

You don't have to call mysql_store_result() or mysql_use_result() for other queries"

Perhaps someone could correct this?

The paragraph above is ok, but when doing multiple queries in one call you have to cycle through all returned result sets (even empty ones for DML queries) with mysql_next_result() to fetch the "affected rows", "warning count" and error information for each query

Please be aware that the documentation currently reads:

    "You don't have to call mysql_store_result() 
     or mysql_use_result() for other queries"

In the interest of others evaluating the suitability of MySQL for commercial applications I suggest that the above paragraph be updated to make it more complete, consistent and accurate (regardless as to whether single or multiple statements are being executed).

This is not about the result row fetching functions mysql_(use|store)_result()

This is about the result status fetch functions and about these we say:

  By default, mysql_query() and mysql_real_query() return only the
  first query status and the subsequent queries status can be processed 
  using mysql_more_results() and mysql_next_result().

  (http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
   2nd paragraph)

The only thing i see here is that it should be "need to be processed"
instead of "can be processed". OK, maybe we could add that this is needed
for any kind of result set, not only for those that return result rows,
too.

Changing to "Documentation issue"

test case, produces two binaries bug19927_with and bug19927_without, the later fails due to not fetching with mysql_next_result

Attachment: bug19927-1.0.tar.bz2 (application/x-tar, text), 190.41 KiB.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html has
been revised to be more explicit what each option does, to provide working sample
code, and to oint out that stored procedures that produce result sets *must*
be processed with multi-results enabled.