Bug #6383 errorhandling for multi statements
Submitted: 2 Nov 2004 10:32 Modified: 5 Nov 2004 14:55
Reporter: Georg Richter Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.x, 5.0.x OS:Any (all)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[2 Nov 2004 10:32] Georg Richter
When using multi statements, api functions mysql_query/mysql_multi_query only 
return an error if the 1st statement failed. 
This behaviour is dangerous when using multi statements inside transactions. 
Output from test: 
1st Query 
Error: You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near 
'thisshouldproduceanerror;SET @a:=1' at line 1 
2nd Query 

How to repeat:
#include <my_global.h> 
#include <mysql.h> 
#include <stdio.h> 
int main() { 
	MYSQL		*mysql; 
	MYSQL_RES	*result; 
	MYSQL_ROW	row; 
	mysql = mysql_init(NULL); 
	mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, 
	printf("1st Query\n"); 
    if (mysql_query(mysql, "thisshouldproduceanerror;SET @a:=1")) { 
		printf("Error: %s\n", mysql_error(mysql)); 
	printf("2nd Query\n"); 
    if (mysql_query(mysql, "SET @a:=1;thisshouldproduceanerror")) { 
		printf("Error: %s\n", mysql_error(mysql)); 
[5 Nov 2004 13:13] Georg Richter
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

From http://dev.mysql.com/doc/mysql/en/C_API_multiple_queries.html: 
 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().
[5 Nov 2004 14:27] Michael Kofler
I still don't see a way how to find out if an error happened in then 2nd, 3rd whatever command of a multi-query. mysql_error works only for the 1st command, even if I use mysql_next_result.
[5 Nov 2004 14:32] Michael Kofler
I tried to add a file to this bug, but no way ... So here is the code:

#include <stdio.h>
#include <mysql.h>

int main(int argc, char *argv[])
  int i;
  MYSQL *conn;        // connection to MySQL server
  MYSQL_RES *result;  // result of SELECT query
  MYSQL_ROW row;      // one record (row) of SELECT query

  // connect to MySQL
  conn = mysql_init(NULL);
  mysql_options(conn, MYSQL_READ_DEFAULT_GROUP, "");
        conn, "localhost", "root", "uranus", 
        "", 0, NULL, 0) == NULL) {
      fprintf(stderr, "sorry, no database connection ...\n");
      return 1;
  mysql_set_server_option(conn, MYSQL_OPTION_MULTI_STATEMENTS_ON);

  // retrieve list of all publishers in mylibrary
  const char *sql="SELECT 1;SELECT 2;SELECT dummy;SELECT 4";
  if(mysql_query(conn, sql)) {
    // no error reported here <<<<<<---------------
    fprintf(stderr, "%s\n", mysql_error(conn));
    fprintf(stderr, "Fehlernummer %i\n", mysql_errno(conn));
    fprintf(stderr, "%s\n", sql);
    return 1;

    result= mysql_store_result(conn); 
    if(!result) {
      fprintf(stderr, "error, exit\n"); 
      exit(1); }
    // also no error reported here <---------------------
      fprintf(stderr, "%s\n", mysql_error(conn));
    else {
      // loop through all found rows
      while((row = mysql_fetch_row(result)) != NULL) {
        for(i=0; i < mysql_num_fields(result); i++) {
          if(row[i] == NULL)
            printf("%s\t", row[i]);
  } while (!mysql_next_result(conn));

  // de-allocate memory of result, close connection
  return 0;
[5 Nov 2004 14:42] Ramil Kalimullin
Hi Michael!

You have to check if (mysql_errno(conn))... just after
the loop } while (!mysql_next_result(conn));
as well.

Regards, Ramil.
[5 Nov 2004 14:55] Georg Richter
your code will not work without setting client_flag to CLIENT_MULTI_RESULTS in