MySQL Bugs: #6383: errorhandling for multi statements

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:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.x, 5.0.x	OS:	Any (all)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
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, 
CLIENT_MULTI_STATEMENTS); 
 
	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)); 
	} 
 
	mysql_close(mysql); 
}

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().

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.

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, "");
  if(mysql_real_connect(
        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;
  }

  do
  {
    result= mysql_store_result(conn); 
    if(!result) {
      fprintf(stderr, "error, exit\n"); 
      exit(1); }
  
    // also no error reported here <---------------------
    if(mysql_errno(conn))
      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("[NULL]\t");
          else
            printf("%s\t", row[i]);
        }
        printf("\n");
      }
    }
    mysql_free_result(result);
  } while (!mysql_next_result(conn));

  // de-allocate memory of result, close connection
  mysql_close(conn);
  return 0;
}

Hi Michael!

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

Regards, Ramil.

Michael, 
 
your code will not work without setting client_flag to CLIENT_MULTI_RESULTS in 
mysql_real_connect.