MySQL Bugs: #32727: Unable to abort distributed transactions enlisted in MSDTC

Bug #32727	Unable to abort distributed transactions enlisted in MSDTC
Submitted:	26 Nov 2007 16:04	Modified:	3 Dec 2007 7:27
Reporter:	John Water	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.01.00.00 and 3.51.21.00	OS:	Windows (XP)
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	Abort, Distributed Transactions, ODBC

Description:
Applications with the MyODBC drivers, v5.01.00.00 or v3.51.21.00 is not able to abort distributed transactions enlisted in MSDTC, even the app explicitly calls pTRansaction->Abort(), where pTransaction is the distributed transaction handle.

A simple repro will be attached: a zip file, odbcbug.zip contains an executable, odbcbug.exe for Windows, the source code, odbcbug.c and an ODBC tracing file, sql.log.

There are actually two problems:
1) The MySQL ODBC driver commits an aborted transaction;
2) The driver complains
Error in my_thread_global_end(): 4 threads didn't exit
If you run the repro against an MySQL database;

I have run the repro against Microsoft SQL Server, Oracle, DB2, and ASE.  All the database servers work well.  However, the MySQL database will contain a row in the test table after I ran the repro against my MySQL database and this row should not be in the test table, because the repro explicitly called pTransaction->abort().

Please fix this problem in the ODBC driver.

Thanks,

John

How to repeat:
a repro will be attached.  Here are the steps to reproduce this problem:
1) download the file, odbcbug.zip;
2) unzip the file to a directory;
3) run odbcbug.exe and the command line could be
            odbcbug.exe "dsn=your_dsn;uid=your_uid;pwd=your_pwd"

The main function in the repro is

---------------------------------------------------------------------
void ShowBug( p_odbc_conn conn )
/******************************/
{
    ITransactionDispenser *	pTransactionDispenser	= NULL;
    ITransaction *		pTransaction		= NULL;
    HRESULT			hr	= S_OK ;
    SQLHSTMT			stmt	= NULL;
    RETCODE			ret;
    SQLSMALLINT			on_off	= SQL_AUTOCOMMIT_ON;
    char *			insert	= "insert into test values( 1, 'test' )";

    ret = SQLSetConnectAttr( conn->dbc, SQL_ATTR_AUTOCOMMIT,
			(SQLPOINTER)on_off, SQL_IS_UINTEGER );
    _c( conn->info->env, conn->dbc, stmt );

    // Obtain the ITransactionDispenser Interface pointer
    // by calling DtcGetTransactionManager()
    hr = DtcGetTransactionManager( NULL, NULL, IID_ITransactionDispenser,
		    0, 0, NULL, (void **)&pTransactionDispenser );

    if( FAILED( hr ) ) {
	printf( "DtcGetTransactionManager failed: %x\n", hr );
	goto done;
    }
    hr = pTransactionDispenser->BeginTransaction( NULL,
		    ISOLATIONLEVEL_ISOLATED, ISOFLAG_RETAIN_DONTCARE,
		    NULL, &pTransaction ) ;
		
    if( FAILED( hr ) ) {
	printf("BeginTransaction failed: %x\n",hr);
	goto done;
    }
    ret = SQLSetConnectAttr( conn->dbc, SQL_ATTR_ENLIST_IN_DTC,
			(SQLPOINTER)pTransaction, SQL_IS_UINTEGER );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLAllocHandle( SQL_HANDLE_STMT, conn->dbc, &stmt );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLExecDirect( stmt, (SQLCHAR *)insert, SQL_NTS );
    _c( conn->info->env, conn->dbc, stmt );

    ret = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    _c( conn->info->env, conn->dbc, NULL );

    hr = pTransaction->Abort( NULL, FALSE, FALSE );
    if( FAILED( hr ) ) {
	printf( "pTransaction->Abort() failed: %x\n", hr );
	goto done;
    }

  done:
    if( pTransaction != NULL ) {
	hr = pTransaction->Release();
	if( FAILED( hr ) ) {
	    printf( "pTransaction->Release() failed: %x\n", hr );
	}
    }
    if( pTransactionDispenser != NULL ) {
	hr = pTransactionDispenser->Release();
	if( FAILED( hr ) ) {
	    printf( "pTransaction->Release() failed: %x\n", hr );
	}
    }

    return;
}
---------------------------------------------------------------------

John,
None of the MySQL Connector/ODBC drivers support distributed transactions via MS DTC.

If the driver doesn't support MSDTC, it should give an error when the following function is called by the app:

    ret = SQLSetConnectAttr( conn->dbc, SQL_ATTR_ENLIST_IN_DTC,
			(SQLPOINTER)pTransaction, SQL_IS_UINTEGER );

However the driver currently returns SQL_SUCCESS.

We will fix it to return the proper error. Thanks.

Make setting of SQL_ATTR_ENLIST_IN_DTC be reported as an error

Attachment: bug32727.diff (text/plain), 1.49 KiB.

The fix for this has  been committed, and will be in the next release (3.51.23). Thanks for the bug report.

A note has been added to the 3.51.23 changelog: 

Connector/ODBC would incorrectly return SQL_SUCCESS when checking for distributed transaction support.

Please, is there a solution for this Problem?

Marcela, Connector/ODBC does not support enlistment in DTC transactions and we currently have no plans to support it.