MySQL Bugs: #91951: SQLCancel and SQLFetchScroll are not thread-safe

Bug #91951	SQLCancel and SQLFetchScroll are not thread-safe
Submitted:	9 Aug 2018 12:41	Modified:	1 Jun 2022 17:17
Reporter:	Zhang Garriot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	driver, ODBC

Description:
MySQL ODBC Driver causes our application crash when SQLCancel and SQLFetchScroll are called in different threads simultaneously. 

We have a use case that SQLFetchScroll is called in a thread to fetch data, but in some conditions, the data are not needed, so we call SQLCancel to cancel the processing. 

But when SQLCancel and SQLFetchScroll are called in the same time, MySQL ODBC driver crashes our applications. 

How to repeat:
The issue can be reproduced with the following code on Windows 2008R2 with Visual Studio 2015

// mysql_bit.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include <iostream>
#include <string>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>

static void CloseDatabase(SQLHENV hEnv, SQLHDBC hDbc)
{
	SQLDisconnect(hDbc);
	SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}

DWORD WINAPI execute(LPVOID p) {
	SQLHSTMT stmt = *((SQLHSTMT*)p);
	//allocate enough buffer
	SQLUINTEGER  *buffer = new SQLUINTEGER[70000000];
	SQLBindCol(stmt, 1, SQL_C_ULONG, buffer, 1, NULL);
	SQLRETURN sqlReturn = SQLFetchScroll(stmt, SQL_FETCH_NEXT, 0);
	std::cout << "sql fetch finished with code " << sqlReturn << std::endl;
	delete buffer;
	return 0;
}

DWORD WINAPI cancel(LPVOID p) {
	SQLHSTMT stmt = *((SQLHSTMT*)p);
	SQLRETURN sqlReturn = SQLCancel(stmt);
	std::cout << "sql cancel finished with code " << sqlReturn << std::endl;
	return 0;
}

int main() {
	//connection and query information
	std::wstring dsn = L"DSN=mysql_bit_test;UID=tpch;PWD=mstr2018";
	std::wstring sql = L"select L_ORDERKEY from tpch1x.LINEITEM";

	SQLHENV HENV = SQL_NULL_HENV;
	SQLHDBC HDBC = SQL_NULL_HDBC;
	SQLHSTMT HSTMT = SQL_NULL_HSTMT;

	//init HENV and HDBC
	SQLAllocHandle(SQL_HANDLE_ENV, NULL, &HENV);
	SQLSetEnvAttr(HENV, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
	SQLAllocHandle(SQL_HANDLE_DBC, HENV, &HDBC);
	wchar_t connOutput[256];
	SQLSMALLINT outlen;
	SQLRETURN rt = SQLDriverConnect(HDBC, SQL_NULL_HSTMT, &dsn[0], dsn.length(), connOutput, 256, &outlen, SQL_DRIVER_NOPROMPT);

	//init HSTMT, set attribute and execute
	SQLAllocHandle(SQL_HANDLE_STMT, HDBC, &HSTMT);
	int rowSize = 500;
	SQLSetStmtAttr(HSTMT, SQL_ATTR_ROW_ARRAY_SIZE, &rowSize, 0);
	SQLExecDirect(HSTMT, &sql[0], sql.length());

	//Run SQLFetchScroll in an individual thread
	DWORD fetchThread;
	CreateThread(NULL, 0, execute, &HSTMT, 0, &fetchThread);
	//Run SQLCancel in another thread
	DWORD cancelThread;
	CreateThread(NULL, 0, cancel, &HSTMT, 0, &cancelThread);

	//wait until the threads are finished
	std::cout << "Wait for sub threads output...and then input any text" << std::endl;
	std::wstring input;
	std::wcin >> input;
	SQLFreeStmt(HSTMT, SQL_DROP);
	CloseDatabase(HENV, HDBC);

	return 0;
}

Suggested fix:
Make MySQL ODBC Driver internal thread-safe

Hi Zhang,

Thank you for the detailed description of the problem.

This behavior is expected.

On the statement level the driver is not thread-safe. The HSTMT handle is not supposed to be shared between threads for simultaneous access to the database.
You will not get any advantage of concurrent threads because the MySQL Server won't execute concurrent statements in the same connection. That is why there is not much sense in slowing down the code by adding extra mutexes to protect HSTMT. The MySQL database server is just not designed for that.

Therefore, I would suggest you to create a separate connection (HDBC) for each thread and use a separate HSTMT statement in each one of them.

Closing, not a bug.
Please feel free to reopen it if there are new details or the crash is happening with the concurrent connections instead of statements.

I don't agree with the previous comment. If you refer to the documentation for SQLCancel (https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcancel-function?view=sql-ser...) it states that SQLCancel can ne used to cancel "A function running on the statement on another thread.". That means that SQLCancel should be thread safe.

In an application migrated from Microsoft SQL Server we're seeing intermittent crashes in the MySQL ODBC driver when SQLCancel is called from another thread.

Note: a related issue was fixed via MySQL Bug #105606 -- here's the suggested 8.0.30 release note for it:

SQLCancel() was not thread safe; it and other ODBC API functions are now
thread safe.

Thank you for the bug report.