Bug #88572 It seems Fetch is not a thread safe on windows 7

• Submitted: 21 Nov 2017 2:27
• Modified: 23 Nov 2017 15:30
• Reporter: Nik Rayan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: Current
• OS: Windows (7)
• CPU Architecture: Any

[21 Nov 2017 2:27] Nik Rayan

Description:
It seems Fetch is not a thread safe on windows 7
It crashes after a while running the plain code below.

The workarround is use Mutex for Fetch.

How to repeat:
Create a table which can successfully pass 
"SELECT ID FROM CENTRALDB.ACCOUNT WHERE NAME = 'tyhmvsewhvfqttusqggd'; "
ID and NAME are utf8 text.

Compile the following code and run under windows 7:

#include <windows.h>  
#include <sql.h>
#include <iostream>
#include <sqltypes.h>
#include <sqlext.h>

typedef wchar_t tchar;

void _0Close(HENV &henv, HDBC &hdbc)
{
	if (hdbc != NULL) {
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
		hdbc = NULL;
	}
	if (henv != NULL) {
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		henv = NULL;
	}
}

bool _0Open(const char* szDSN, const char* szLogin, const char* szPassword, HENV &henv, HDBC &hdbc)
{

	if (szDSN[0] == 0) {
		std::cout <<  "_0Open:   No data source name specified" << std::endl;
		return false;
	}

	SQLRETURN nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	if ((nRetCode != SQL_SUCCESS) && (nRetCode != SQL_SUCCESS_WITH_INFO)) {
		std::cout <<  "_0Open:   SQLAllocEnv FAILED" << std::endl;
		return false;
	}

	// Attempt to set the version
	nRetCode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
	if ((nRetCode != SQL_SUCCESS) && (nRetCode != SQL_SUCCESS_WITH_INFO)) {
		std::cout <<  "_0Open:   SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION) FAILED" << std::endl;
	}
	
	// Allocate the database handle
	nRetCode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	if ((nRetCode != SQL_SUCCESS) && (nRetCode != SQL_SUCCESS_WITH_INFO)) {
		std::cout <<  "_0Open:   SQLAllocConnect FAILED" << std::endl;
		_0Close(henv, hdbc);
		return false;
	}

	nRetCode = SQLConnect(hdbc, (SQLCHAR*)szDSN, SQL_NTS, (SQLCHAR*)szLogin, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS);
	if ((nRetCode != SQL_SUCCESS) && (nRetCode != SQL_SUCCESS_WITH_INFO)) {
		std::cout <<  "_0Open:   SQLConnect failed" << std::endl;
		_0Close(henv, hdbc);
		return false;
	}

	return true;
}
 
#define LSTR 260
void SetErrorODBCSTMT(HSTMT hstmt)
{
   SQLINTEGER iSQLNativeError;
   SQLTCHAR szErrorString[LSTR];
   SQLTCHAR szSQLState[LSTR];
   SQLSMALLINT textlength = LSTR;
   SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, szSQLState, &iSQLNativeError, szErrorString, LSTR, &textlength);
   std::cout <<  "SQL error "<< int(iSQLNativeError) <<" "<< szErrorString << std::endl;
}

void _0Select(HDBC &hdbc, const char* szSelectStatement)
{
   HSTMT hStmt = NULL;

   SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hStmt);
   if (hStmt == SQL_NULL_HSTMT) {
      std::cout <<  "_0Select:  SQLAllocHandle(SQL_HANDLE_STMT) failed" << std::endl;
      return;
   }

   RETCODE rc = SQLExecDirect(hStmt, (SQLCHAR*)szSelectStatement, SQL_NTS);
   if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
      std::cout <<  "_0Select: FAILED" << std::endl;
	   SetErrorODBCSTMT(hStmt);
      return;
   }

   rc = SQLFetch(hStmt);

   if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) std::cout <<  "XXX Fetch failed" << std::endl;

   if (hStmt != NULL) {
      SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
   }
}

DWORD WINAPI ThreadFunction( void *x) 
{

   HENV    henv = NULL;
   HDBC    hdbc = NULL;

   if (!_0Open("1234", "1234", "1234", henv, hdbc)) return 0;

	static int i = 0;
	int i1secago = 0;
   
	uint32_t uTCLockTime = GetTickCount();
   while (true) {
       _0Select(hdbc, "SELECT ID FROM CENTRALDB.ACCOUNT WHERE NAME = 'tyhmvsewhvfqttusqggd'; ");
       //~ _0Select(hdbc, "SELECT ID FROM CENTRALDB.ACCOUNT WHERE NAME = 'wrpweofsiebvtasnjnlu'; ");
       
             if ((GetTickCount() - uTCLockTime) > 1000){
                std::cout << "Performance (profiles reloads per sec): " << i-i1secago << std::endl;
                uTCLockTime = GetTickCount();
                i1secago = i;
             }
                
             i++;
   }
   _0Close(henv, hdbc);
}

int main()
{
	DWORD dwThreadID;
	for(int i=0; i<10; i++){
		CreateThread(NULL, 0, (LPTHREAD_START_ROUTINE)ThreadFunction, (LPVOID)NULL, 0, &dwThreadID);
		std::cout << "Thread created: " << i << std::endl;
	}
	Sleep(1000000);
}

[23 Nov 2017 15:30] Nik Rayan

wrong runtime