[REQ] Transaction support

[GoogleCodeExporter]

Maybe we should add support for transactions.
The 3 relevant MySQL commands are BEGIN, COMMIT and ROLLBACK.

My idea would be to add a toolbar Item "Transaction Mode" that enables 
transactions.
Now there would be two ways to continue from here:

1) The item has an on/off state and when pressed the second time a sheet will 
appear asking you 
whether you want to commit or rollback.

2) The item will only enable transaction mode. We will then watch out for 
INSERT,UPDATE,DELETE,... queries and when they occur add a yellow bar below the 
toolbar 
saying "You made unsaved changes to the database" and two buttons "Save" and 
"Revert". (I think 
of it like the Popup warning bar in Camino). See 
http://s3.amazonaws.com/cbo/img/feature-
ab-lg.jpg

Also: http://dev.mysql.com/doc/refman/5.1/en/commit.html

Original issue reported on code.google.com by schlabbe...@gmail.com on 28 Mar 2009 at 2:11

[GoogleCodeExporter]

This is very similar to #210, but goes about describing how we might achieve it 
- nicely, too :)

Something to bear in mind - transactions are only supported in InnoDB (and 
BDB?), and the default table type is 
MyISAM.  We'd have to do something like cacheing changes - quite possible - and 
providing "fake" transactions 
to offer similar support on MyISAM.

Original comment by rowanb@gmail.com on 28 Mar 2009 at 2:43

[GoogleCodeExporter]

Whoopsie...should've paid more attention :| 

You're right about InnoDB which makes this a lot more complicated.
Because in order to provide rollback support without MySQL transactions we 
would have to do one of four things:

a) download the whole database so when we roll back we can just replace the 
changes => won't work for large databases

b) find an inverse query to each executed query and stack them => would require 
a full SQL parser which is on par with MySQLs own

c) only pretend to be making changes => this would mean "lying" to the 
Structure/Content view (which would be pretty difficult on it's own I guess) 
and also the Cutom Query tab 
would have to be disabled as we would still need an SQL Parser for this

d) copy the database on-server to a temporary MEMORY database: 
http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database/26091#260
91 => I guess this 
wouldn't work for large databases either

So what idea do you have in mind? :)

Original comment by schlabbe...@gmail.com on 28 Mar 2009 at 3:51

[GoogleCodeExporter]

If it is to implement transactions, I would do it only for the supported types 
of tables (InnoDB).

a) is not feasible if you don't work on local DB, and your DB has more then a 
couple of megs of data. You'll 
end up transferring lots of data and waiting for this instead of doing actual 
work.

b) is not really a solution. Think that you have a table with auto increment, 
and add a new row. To undo this, 
you'll have to delete that row and decrease the table's AUTO_INCREMENT value. 
What if you have foreign keys 
and you added data in other tables too?

c) would you trust a tool that instead of showing what's actually on the data, 
is 'lying' to you? What if someone 
changes something in other thread, and when you want to commit 'UPDATE x WHERE 
y=20', you don't have 
that row anymore?

d) consider the performance hit if you have a 4G table on a production server.

Original comment by ursache....@gmail.com on 30 Mar 2009 at 11:56

[GoogleCodeExporter]

Original comment by stuart02 on 3 Apr 2009 at 12:32

• Added labels: Component-Logic, Priority-Medium, Type-Enhancement, Usability

[GoogleCodeExporter]

Original comment by stuart02 on 3 Apr 2009 at 12:33

• Added labels: Milestone-Release1.0

[GoogleCodeExporter]

I'm not sure if you are talking fundamentally about the same thing: today I 
accidentally almost erased the 
content of a field (phone number), the only thing that saved the original data 
was that I quitted Sequel Pro 
before leaving the row.

I think this could be solved implementing "Undo" in some way.

Otherwise I like very much the behaviour of saving immediately changes. 
(FileMaker databases I used worked 
the same way; application/system preferences in OS X usually commit changes 
immediately, too -- instead of 
the superstitious dilemma of "do I have to push the Apply or the OK button?")

Thank you for your work!

Original comment by kver...@gmail.com on 7 Apr 2009 at 9:48

[GoogleCodeExporter]

Transactions is an opt-in feature of MySQL to protect you from destroying a 
database by executing a wrong 
query or to abort a series of queries if one fails.
I guess you can roughly call it 'Undo' but as you can see above this is not so 
easily possbile...

Original comment by schlabbe...@gmail.com on 8 Apr 2009 at 3:32

[GoogleCodeExporter]

Original comment by avenja...@gmail.com on 14 May 2009 at 4:06

• Changed title: [REQ] Transaction support
• Added labels: Feature-Request

[GoogleCodeExporter]

@kvera21: If you are changing a field in the content view, and you want to 
cancel editing, just press ESCAPE. The edit will then be cancelled.

While I also think that UNDO support would be very useful, I'm not sure how 
easy it is to implement. A big problem is that other people and/or processes on 
the server might have modified the data in the mean time. If you then try to 
undo your changes, you might also undo other people's changes. The same thing 
goes for all kinds of fake transaction supports.

While the current mode (immediately commiting changes, no undo) might not be 
perfect for everyone, it has the advantage of being perfectly predictable.

for users that want transactions, they can just type the query "BEGIN" in the 
custom query tab. then they can perform all the changes they want (using the 
user interface), and, when they are finished, type "rollback" or "commit".

Original comment by jabakobob@gmail.com on 12 Aug 2010 at 12:18

[GoogleCodeExporter]

I suggest to add just a option in the preferences panel: "auto commit: Yes/No" 
and my be a hint that some storage engines such as MyISAM do not support 
transactions. This way users who never want to have autocommit (-> they will 
NOT work with MyISAM) do not have to write "begin" or "start transaction" after 
"rollback" or "commit" in order to start a new transaction.

In my opinion the console should behave as a e.g a java application would 
behave: If you set autocommit = false, execute a statement, the rollback the 
"transaction" -> if you have InnoDB, the modification is "undone", if it is 
MyISAM it is there.

Keep in mind that if you insert a record into a table A you my join it in a 
query with a table B and at the end you rollback the transaction. You will 
build your own database engine if you try to support all these features with 
"fake transactions".

So keep it simple and every one knows how it works.

Original comment by info@nanosim.net on 6 Oct 2010 at 8:44

[GoogleCodeExporter]

Original comment by schlabbe...@gmail.com on 5 Nov 2010 at 10:10

• Changed state: Accepted

[GoogleCodeExporter]

Original comment by stuart02 on 13 Jan 2012 at 11:36

• Removed labels: Milestone-Release1.0