MySQL Bugs: #72506: SQLNumResultCols causes unexpected execution of statement in stored procedure

Bug #72506	SQLNumResultCols causes unexpected execution of statement in stored procedure
Submitted:	1 May 2014 20:45	Modified:	7 Aug 2024 5:15
Reporter:	Robert Conde	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	MySQL ODBC 5.3 ANSI Driver 5.02.03.00	OS:	Windows (7 64-Bit)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ODBC, SQLDescribeCol, SQLNumResultCols

Description:
If an INSERT is inside a stored procedure, and you:

1) Prepare the call to the stored procedure
2) Call SQLNumResultCols or SQLDescribeCol
3) Execute the procedure

You get two inserted rows instead of 1. My theory is that in order to get the number of result cols, the ODBC driver actually executes the statement. However, it seems that in the case of a stored procedure, the results are not rolled back (as I would expect they would be). In that sense, I think this probably isn't limited to INSERT, but any operation which changes the database (though I haven't tested that).

How to repeat:
Run the following code. Only one row should be expected after calling the stored procedure, but there are two.

#include <Windows.h>
#include <sql.h>
#include <sqlext.h>
#include <iostream>
#include <string>
#include <vector>

bool checkResult(SQLRETURN result, SQLSMALLINT handleType, SQLHANDLE handle);
bool executeCommand(SQLHDBC hConnection, SQLHSTMT hStatement, const std::string & command);

int main(int argc, char * argv[])
{
   SQLRETURN result;

   //Create the enironment
   SQLHENV hEnvironment = NULL;

   result = ::SQLAllocHandle(SQL_HANDLE_ENV,
                             SQL_NULL_HANDLE,
                             &hEnvironment);

   if(!checkResult(result,
                   SQL_HANDLE_ENV,
                   hEnvironment))
   {
      return -1;
   }

   //Set ODBC version
   result = ::SQLSetEnvAttr(hEnvironment,
                            SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER)SQL_OV_ODBC3,
                            SQL_IS_UINTEGER);

   if(!checkResult(result,
                   SQL_HANDLE_ENV,
                   hEnvironment))
   {
      return -1;
   }

   //Create the connection
   SQLHDBC hConnection = NULL;

   result = ::SQLAllocHandle(SQL_HANDLE_DBC,
                             hEnvironment,
                             &hConnection);

   if(!checkResult(result,
                   SQL_HANDLE_DBC,
                   hConnection))
   {
      return -1;
   }

   //Connect to the database
   result = ::SQLConnect(hConnection,
                         (SQLCHAR *)"myDataSource",
                         SQL_NTS,
                         (SQLCHAR *)"user",
                         SQL_NTS,
                         (SQLCHAR *)"password",
                         SQL_NTS);

   if(!checkResult(result,
                   SQL_HANDLE_DBC,
                   hConnection))
   {
      return -1;
   }

   //Create a statement handle
   SQLHSTMT hStatement;

   result = ::SQLAllocHandle(SQL_HANDLE_STMT, 
                             hConnection, 
                             &hStatement);

   if(!checkResult(result,
                   SQL_HANDLE_STMT,
                   hStatement))
   {
      return -1;
   }

   if(!executeCommand(hConnection,hStatement,"DROP TABLE BUG_TEST"))
   {
      //accept an error here because the table might not exist
   }

   if(!executeCommand(hConnection,hStatement,"CREATE TABLE BUG_TEST(MYNUM INT)"))
   {
      return -1;
   }

   if(!executeCommand(hConnection,hStatement,"DROP PROCEDURE BUG_TEST_PROC"))
   {
      //accept an error here because the table might not exist
   }

   if
   (
      !executeCommand
      (
         hConnection,
         hStatement,
         "CREATE PROCEDURE BUG_TEST_PROC()\n"
         "BEGIN\n"
         "   INSERT INTO BUG_TEST VALUES (5);\n"
         "END\n"
      )
   )
   {
      return -1;
   }

   if(!executeCommand(hConnection,hStatement,"CALL BUG_TEST_PROC"))
   {
      return -1;
   }

   if(!executeCommand(hConnection,hStatement,"SELECT COUNT(*) FROM BUG_TEST"))
   {
      return -1;
   }

   return 0;
}

bool executeCommand(SQLHDBC hConnection, SQLHSTMT hStatement, const std::string & command)
{
   std::vector<int> results;

   SQLRETURN result;
   
   result = ::SQLPrepare(hStatement,
                         (SQLCHAR *)command.c_str(),
                         (SQLINTEGER)command.length());

   if(!checkResult(result,
                   SQL_HANDLE_STMT,
                   hStatement))
   {
      return false;
   }

   SQLSMALLINT numCols;
   SQLLEN indicator;

   //This causes the stored procedure to be executed...
   result = SQLNumResultCols(hStatement,&numCols);

   results.resize(numCols);

   for(size_t i = 0; i < numCols; i++)
   {
      result = SQLBindCol(hStatement,
                          SQLUSMALLINT(i + 1),
                          SQL_C_LONG,
                          &results[i],
                          1,
                          &indicator);

      if(!checkResult(result,
                      SQL_HANDLE_STMT,
                      hStatement))
      {
         return false;
      }
   }

   if(!checkResult(result,
                   SQL_HANDLE_STMT,
                   hStatement))
   {
      return false;
   }

   result = SQLExecute(hStatement);

   if(!checkResult(result,
                   SQL_HANDLE_STMT,
                   hStatement))
   {
      return false;
   }

   SQLLEN numResults;

   result = SQLRowCount(hStatement,&numResults);

   if(!checkResult(result,
                   SQL_HANDLE_STMT,
                   hStatement))
   {
      return false;
   }

   if(numCols != 0 && numResults != 0)
   {
      result = SQLFetch(hStatement);

      if(!checkResult(result,
                      SQL_HANDLE_STMT,
                      hStatement))
      {
         return false;
      }

      std::cout << "Results = ";

      for(size_t i = 0; i < results.size(); i++)
      {
         std::cout << results[i] << "   ";
      }

      std::cout << std::endl;
   }

   return true;
}

bool checkResult(SQLRETURN result, SQLSMALLINT handleType, SQLHANDLE handle)
{
   if(result == SQL_SUCCESS)
      return true;

         SQLSMALLINT     RecNumber = 1;
         SQLCHAR         Sqlstate[6];
         SQLINTEGER      NativeError;
   const SQLSMALLINT     BufferLength = 1024;
         SQLCHAR         MessageText[BufferLength];
         SQLSMALLINT     TextLength = 0;

   for(;;)
   {
      SQLRETURN ret = SQLGetDiagRec(handleType,
                                    handle,
                                    RecNumber,
                                    Sqlstate,
                                    &NativeError,
                                    MessageText,
                                    BufferLength,
                                    &TextLength);

      if(ret == SQL_NO_DATA)
      {
         break;
      }
      else if(ret == SQL_ERROR)
      {
         std::cout << "Error retrieving diagnostics." << std::endl;
         return false;
      }

      std::cout << Sqlstate << ": " << MessageText << std::endl;

      RecNumber++;
   }

   return result != SQL_ERROR;
}

Hi Robert,

Unfortunately, the behavior you expect from MySQL Connector/ODBC is not supported by MySQL Server. In order to get the insert to rollback you have to start the transaction and then roll it back. The driver will not do that for you because switching autocommit on and off without you knowing it is not desirable. Also, MySQL does not support the nested transactions, so rolling back the transaction with the insert implicitly done from SQLNumResultCols() would roll back the bigger transaction if it was ever started.

I do not think we can help it in any way. Thus, marking the bug report as "Unsupported".

What you said makes some sense...however if you directly call the insert (rather than within a procedure) it works as expected. I don't think this behavior is consistent with your explanation.

In other words:

   bool insertInProc = true;

   if(insertInProc) //doesn't work as expected
   {
      if(!executeCommand(hConnection,hStatement,"DROP PROCEDURE BUG_TEST_PROC"))
      {
         //accept an error here because the table might not exist
      }

      if
      (
         !executeCommand
         (
            hConnection,
            hStatement,
            "CREATE PROCEDURE BUG_TEST_PROC()\n"
            "BEGIN\n"
            "   INSERT INTO BUG_TEST VALUES (5);\n"
            "END\n"
         )
      )
      {
         return -1;
      }

      if(!executeCommand(hConnection,hStatement,"CALL BUG_TEST_PROC()"))
      {
         return -1;
      }
   }
   else //works as expected
   {
      if(!executeCommand(hConnection,hStatement,"INSERT INTO BUG_TEST VALUES (5)"))
      {
         return -1;
      }
   }

Robert,

sorry, I misunderstood the problem at the beginning.
You do not want the insert to be rolled back as I initially thought.
Debugging showed me that the actual bug is in Connector/ODBC, which is calling the stored procedure twice.

Setting the status to verified.

Posted by developer:
 
The issue is no longer repeatable after the code in SQLNumResultCols() was reworked in the version 8.0.

Test case:

DECLARE_TEST(t_bug18709928_num_cols) {
  try{
    odbc::table tab(hstmt, "tab18709928", "col1 INT");
    odbc::procedure proc(hstmt, "proc18709928",
      "()"
      "BEGIN "
      "  INSERT INTO tab18709928 VALUES (5); "
      "END");

    odbc::stmt_prepare(hstmt, "CALL proc18709928");
    odbc::stmt_execute(hstmt);
    is_num(0, odbc::num_result_cols(hstmt));
    is_num(1, odbc::num_result_rows(hstmt));
    odbc::stmt_close(hstmt);

    odbc::sql(hstmt, "SELECT * FROM tab18709928");
    is_num(1, odbc::num_result_cols(hstmt));
    is_num(1, odbc::num_result_rows(hstmt));
    while(SQLFetch(hstmt) == SQL_SUCCESS) {}
    odbc::stmt_close(hstmt);
  }
  ENDCATCH;
}