Menu
I'd like to connect to a MySQL server with Oracle SQL Developer, but with autocommit disabled. By default, all MySQL connections have autocommit enabled, which is rather odd.
The global setting in SQL Developer is unchecked, and
set autocommit=0;
results in the error
set autocommit script command forced off, as connection level autocommit is on.
In the connection's settings are no other options besides hostname, port and a drop down box for database selection.
I'm using SQL Developer 3.2.10 with latest JDBC connector, 5.1.
872
To disable autocommit mode explicitly, use the following statement: SET autocommit=0; After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB or NDB ) are not made permanent immediately.
To determine the current state of autocommit mode use the SQL command SELECT @@autocommit .
The COMMIT statement saves all the modifications made in the current. If you commit a database, it saves all the changes that have been done till that particular point. By default, the MySQL database commits/saves the changes done automatically. You can turn off/on the auto-commit using the SET autocommit statement.
In Oracle SQL Developer 4 the setting has moved:
Tools > Preferences > Database > Advanced > Autocommit
Default is off.
Alternative:
set autocommit off;
You will run into an error if you try and use
start transaction;
-- Your SQL statements
commit;
...out of the box on a MySQL database in SQLDeveloper (as Michael mentioned in a comment to another answer.)
In order to get around this error that Michael referenced in his comment you can use the following hint:
/*sqldev:stmt*/start transaction;
-- Your SQL statements
/*sqldev:stmt*/commit;
Found this information here.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
