MySQL Bugs: #73076: Applying modifications in a record set actually commits them

Bug #73076	Applying modifications in a record set actually commits them
Submitted:	23 Jun 2014 8:15	Modified:	26 Aug 2014 11:18
Reporter:	Georgi Sotirov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	6.1.x, 6.2.x	OS:	Any
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
Applying modifications in a record set actually comments them no matter you chose only to Apply them

How to repeat:
1. Open MySQL Workbench;
2. Connect to a server;
3. Open SQL editor;
4. Type a query that returns all the data from a table (i.e. select * from <table>). The table should have a primary key, so the result set is updateable;
5. Modify a value in a cell;
6. Click on Apply (in the down right corner of the result set window);
7. In the dialog "Apply Changes to Recordset" that appears chose "Apply".

Actual result: The modification gets committed and this is indicated in the Output window with the message "Commit recordset changes Commit complete".

Expected result: The modification gets applied (i.e. the related query gets executed) and there is no commit issued.

Effectively there is no possibility to rollback the modifications.

Suggested fix:
This was working properly in 6.0, so it must be a regression. When one applies the changes to recordset this should only execute the related queries and there shouldn't be any transactional statements.

Just to precise that issue happens when I've toggled auto-commit mode to off. When auto-commit is on the dialog "Apply Changes to Recordset" doesn't appear which is normal. So in general Workbench now works the same whether or not auto-commit is toggled on/off.

Thank you for the bug report. Which 6.0.X version worked as you expect?. Thanks.

In 6.0.4 I think, but I do not think this really matters, as it's a bug (of application misbehavior), that needs to be fixed. When clicking on "Apply" I do not have the intention to commit the changes yet, so having them committed is something unexpected and already caused me additional work to manually "rolling" them back.

Verified

I agree it should be possible to not commit after sending resultset change commands to the server, but:

1- the dialog that asks if you want to Apply or Apply and Commit is asking about committing BEFORE the resultset changes, not after.

2- the Commit after the resultset is applied was there since the beginning, I don't think it was any different in 6.0

Posted by developer:
 
The resultset apply code was changed in teh following ways:
- If a transaction is already open/autocommit disabled, there was a dialog that asked if you want to commit the open transaction before applying the changes. That dialog was simplified to offer only Apply/Cancel as choices. This dialog still has no effect on the transaction for the resultset apply itself.

- The resultset changes will no longer be commited if a transaction was already open when Apply is clicked. If autocommit was on, the old behaviour where the whole apply would happen inside a new transaction is maintained.

Alfredo, you're right about the behavior in 6.0.x (I just checked it on 6.0.9), but I remember it working properly (perhaps in an even earlier release). Anyway, I really do not get the point of the commit *before* the application of the changes. I usually make several changes to adjust data and then apply them in the same transaction, so I would like to commit just once (i.e. when I'm ready). This is the behavior I'm used to expect from a database application and I find it really irritating and annoying that in 6.2.1 it's practically the same. Instead of seeing how many rows I've updated/inserted into the Output window I continue see "Commit complete" after clicking just Apply even though I've turned autocommit off...

BTW For unknown reason the button labels have changed to "Appl" and "Rever" (i.e. last letter is missing), which is also annoying.

I believe the reason that it asks whether you want to apply before committing is in case you're in the middle of some other transaction that has nothing to do with what you're doing in the resultsets. I don't remember the circumstances, but I wouldn't be surprised if that was added in response to some different bug report. Anyway, the new behaviour should cover all use cases.

BTW the fix is still not in 6.2.1, it was committed internally but will only be available in the next release. You should see what you expect in the next release.

Fixed as of the upcoming MySQL Workbench 6.2.2 release, and here's the changelog entry:

If a transaction was already open with auto-commit disabled, there was a
dialog that prompted to commit the open transaction before applying the
changes. That dialog was simplified to only offer "Apply" and "Cancel"
options. This dialog still has no effect on the transaction for applying
the result set itself. 

Also, the result set changes will no longer be committed if a transaction 
was already open when "Apply" is clicked. With auto-commit enabled, the old 
behavior of applying the change inside a new transaction is maintained.

Thank you for the bug report.

Yes, perhaps this was the intention, but probably thous should be signaled by another message (e.g. "You're in transaction with unsaved changed. Would you like to commit before applying the result set changes?" or something), which clearly indicates the situation. After all my changes are committed even though my intention was only to apply them.

Anyway, I'll wait for 6.2.2 (as it's not yet released), but #73708 concerns me as well, so I'll have to wait for it being fixed as well.