MySQL Bugs: #10752: AUTOCOMMIT=0 fails after connection timeout

Bug #10752	AUTOCOMMIT=0 fails after connection timeout
Submitted:	19 May 2005 19:51	Modified:	28 May 2013 10:54
Reporter:	Daniel Cooper	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.11	OS:	Windows (Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I'm using MySQL 4.1.10 with InnoDb tables and connecting through ODBC and setting AUTOCOMMIT=0 though the ODBC SQLSetConnectAttr function.  Transaction fuctionality (COMMIT, ROLLBACK) works correctly after the connection opens and while the connection is active.  However, after the connection is idle for over 8 hours (28800 seconds), the transaction functionality reverts to AUTOCOMMIT=1.

A trace output to myodbc.log shows the the first query after the idle period fails with:

error :2013 'Lost connection to MySQL server during query'

and then silently reconnects to the server, but without restoring the AUTOCOMMIT mode.  All further changes to the database on this connection are auto-committed.  Subsequent call to SQLEndTrans with a SQL_ROLLBACK argument return sucessfully, even though the changes are committed.

How to repeat:
Set the wait timeout on MySQL server to something lower than 8 hours.

Connect to mysql database with InnoDb tables through MyODBC.

Set the autocommit mode to off with call:

 SQLSetConnectAttr( hDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER );

Test transactions.  Do an INSERT and then ROLLBACK with call:

 SQLEndTran( SQL_HANDLE_DBC ,hDbc, SQL_ROLLBACK );

Do an INSERT and then COMMIT with call:

 SQLEndTran( SQL_HANDLE_DBC ,context->hDbc, SQL_COMMIT );

Check database for correct transaction outcome.  Only one of the inserts will have been committed.

Leave connection open and idle for longer than the timeout period.

Repeat the above transaction test.

Both inserts will have been committed.

Suggested fix:
Restore the autocommit mode when reconnecting.

Tested on Win 2000 Sp4 , Microsoft Visual C 7.0, MyODBC 3.51.11, MySQL server 4.1.11

From the source code (options.c:294) I can verify that bug. The problem is that the autocommit is set through query ("SET AUTOCOMMIT=0") but when reconnecting another thread is created on the server side and then AUTOCOMMIT is 1. The libmysql does not know anything about the change in the AUTOCOMMIT. The same problem will apply for the transaction isolation level which will be lost.

Saves and restores the autocommit state.

Attachment: odbc_autocomm_10752.patch (application/octet-stream, text), 5.69 KiB.

I have attached a patch that should help restore on disconnect. However with the newer libmysql reconnect is not allowed (disabled) so by default in this case an error will be returned and not automatic reconnection. I am not aware of a way the user program to ask fro reconnect to be true therefore this patch has meaning only for old libmysql versions where reconnect was 1.

c/odbc 3.51.12 uses mysql v5 client and as such inherits new behaviour. Please try c/odbc v3.51.12 and report success/failure.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.