Search code examples
mysqloracle-sqldeveloperautocommit

Disable autocommit in SQL Developer when using MySQL

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.

Solution

  • 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.