MySQL Bugs: #49461: No warning/failure when transaction cannot be commited

Bug #49461	No warning/failure when transaction cannot be commited
Submitted:	4 Dec 2009 15:17	Modified:	18 Dec 2009 10:36
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:
In the event of lost connections to a server, WB automatically (and silently) reconnects.  This is exceedingly problematic in the context of transactions, where changes have been made to a transactional table, but not committed, followed by a disconnect.  Pushing the "commit" button on WB causes WB to silently reconnect, issue the COMMIT statement, and report that it succeeded.

In reality, of course, the only thing that succeeded was that the server processed a COMMIT statement - the fact that it did so in a different connection than the transactional statements is entirely missed, and the end result is that WB tells the user that it committed successfully, when the actual statements had no impact whatsoever.

This is different from QB behavior, which alerts users of dropped connections (three times for me - one for disconnection, once for reconnection, once for commit failure), and then subsequently refuses to commit the transaction.

Here are general query logs:

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
091204  7:01:39	    5 Query	INSERT INTO test.trans_a VALUES (2)
091204  7:01:44	    7 Connect	root@localhost on 
		    7 Shutdown	
bin\mysqld.exe, Version: 5.1.39-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3310, Named Pipe: (null)
Time                 Id Command    Argument
091204  7:01:59	    1 Connect	root@localhost on test
		    1 Query	SET NAMES utf8
091204  7:02:16	    2 Connect	root@localhost on 
		    2 Query	SET NAMES utf8
		    2 Query	commit
091204  7:02:23	    2 Quit	

How to repeat:
1.  Start mysqld
2.  Start WB, connect to server
3.  Create InnoDB table.
4.  Insert row.
5.  Stop and restart mysqld
6.  Commit in WB.

Suggested fix:
Alert users when uncommitted statements exist and the connection is lost.
Alert users when the connection is lost.
Alert users when the connection is re-established.
Disable commit/rollback button when there are no uncommitted statements able to be committed (this may be because of a new connection, or because commit/rollback or implicit commit statements were last executed).

You are right here and data lost can happen ...

A simple test case:

CREATE TABLE t(i INTEGER) ENGINE=INNODB;

START TRANSACTION;

INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);

root# killall -9 mysqld

MySQL now will rollback the whole transaction ... you need to do the two inserts again.

When you don't recognise that server is gone and press commit then you don't recognise the rollback and the data are lost.

From now when not in auto-commit mode WB doesn't restore connection automatically, user has to reconnect manually by pressing corresponding toolbar button.

fixed defected reported by johannes

fixed in repository. will be included in 5.2.11

An entry has been added to the 5.2.11 changelog:

If a connection failed MySQL Workbench reconnected silently. This caused problems with transactions. If changes were made to a table, before a COMMIT, and the connection lost, then MySQL Workbench would reconnect silently and allow the user to COMMIT. However, it did not warn that this COMMIT was on a new connection, and that the COMMIT would have no effect.