MySQL Bugs: #49462: Reconnect fails to reinitialize connection, including autocommit

Bug #49462	Reconnect fails to reinitialize connection, including autocommit
Submitted:	4 Dec 2009 15:31	Modified:	8 Dec 2009 15:05
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.2.10 r4680	OS:	Windows (XP)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any

Description:
When WB silently reconnects to a server because of some communication failure, it fails to reinitialize the connection properly.  Here's the initial connection when WB first connects to a server:

091204  7:01:26	    5 Connect	root@localhost on 
		    5 Query	set autocommit=1
		    5 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
		    5 Query	SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
		    5 Query	SET CHARACTER SET utf8
		    5 Query	SET NAMES utf8
		    5 Query	SELECT CONNECTION_ID()
		    5 Query	SELECT DATABASE()
		    5 Query	set autocommit=0

I then shut down the mysqld instance and restarted it, and WB reconnected:

091204  7:02:16	    2 Connect	root@localhost on 
		    2 Query	SET NAMES utf8

The result of this is that autocommit is now enabled, so a user could be working in WB with transactional tables, using commit/rollback normally, and suddenly (and silently) switched to autocommit.  This is a huge problem, made worse by the fact that the commit/rollback buttons will remain active and will indicate success when pushed, despite having no impact.

There are other associated problems, as well, such as failure to track the connection id properly, which is used for killing connections.

How to repeat:
1.  Start mysqld
2.  Start WB
3.  Create InnoDB table
4.  Stop and restart mysqld
5.  Insert row in WB
6.  Rollback
7.  View table contents

Suggested fix:
Ensure reconnects all properly reinitialize.

switching the commit/rollback buttons according to the autocommit state would also be helpful

Replaced usage of auto-reconnect feature provided by MySQL/C++ Connector (OPT_RECONNECT) with more advanced approach. Now when reconnecting WB restores auto-commit mode, active schema and properly updates connection id.

autocommit-state is now correctly restored after connection to server has been re-established.

fix will be in 5.2.11

An entry was added to the 5.2.11 changelog:

When MySQL Workbench silently reconnected to a server after a communication failure, it failed to reinitialize the connection correctly. As a result of this failure Autocommit was silently enabled. If a user was working with transactional tables, the commit and rollback toolbar buttons appeared to work, although they had no effect.