MySQL specific database configuration file

In MySQL's configuration file I've globally disabled autocommit as so.

[mysqld]
autocommit=0

I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the user or the database itself (doesn't matter). Thinking it would look something like this.

[mysqld]
autocommit=0
execute_sql="Custom SQL to set autocommit for a database"

Let's say

• you are using MySQL in Linux

• datadir is /var/lib/mysql

You could use the init-file option

You could just create an init file called /var/lib/mysql/init-file.sql

Put the customer SQL in /var/lib/mysql/init-file.sql

You could then try one of two things

Try #1

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file.sql

then run

service mysql restart

Try #2

Directly run this

service mysql restart --init-file=/var/lib/mysql/init-file.sql

Give it a Try !!!

CAVEAT

That was a global setting of init code. If your session has to enable autocommit, then just run

SET autocommit=0

in the session or put SET AUTOCOMMIT=0 in the init-file option file. This may still not produce the effect you want since everything goes back to default settings upon connection termination.

UPDATE 2013-02-28 15:40 EST

If you are looking for a startup option for my.cnf to affect the client, try this:

[client]
autocommit=0

or

[mysql]
autocommit=0

Perhaps try adding another my.cnf in the Linux home directory with these options

For more information, see the MySQL Options File Documentation

StackExchange Database Administrators Q#35700, answer score: 2