MySQL specific database configuration file

configurationMySQLmysql-5.5

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"

Best Answer

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

[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

Trying to Understand #1

If I put a procedure call (or whatever application code) in the init-file, will it be executed as expected (in my test case the sleep call seems to be skipped)?

Yes, it will. In the past, I have recommended using init-file to create dedicated keycaches for MyISAM tables:

Aug 28, 2012 : MySQL InnoDB Index in swap (See Suggestion #3)
Mar 20, 2012 : optimal table design mysql with primay key and varchar value

I am currently do this for clients at my employer's company.

Trying to Understand #2

Could I have some kind of logging of what gets executed (apart from modifying the code itself)?

Unfortunately, no. You would have one of the following:

Write changes to some MyISAM table to mark stages
Run something like SELECT 'DEBUG#1;' ... SELECT 'DEBUG#2;' to see if it echoes to the error file. Place these SELECT queries in different places within your code.

Trying to Understand #3

If the code in the init-file takes time, could that ? affect the availability of the MySQL instance right after the restart

Just from the example you posted in the question, they answer would still be no. However, in order for to be sure, you will have to let mysqld tell you this. Assuming /var/log/mysqld.log is your error log, here is how to check:

Open an SSH seesion (Session1) to DB Server
Open an SSH seesion (Session2) to DB Server
In Session1, service mysql stop
In Session1, tail -f /var/log/mysqld.log
In Session2, service mysql start

Watch the output of Session1. If you see something like

121018 17:59:09 [Note] /usr/sbin/mysqld: ready for connections.

in under 5 minutes (300 seconds), this then confirms that the answer is no, the availability of the MySQL instance right after the restart is not affected.

Why does SLEEP() not work ???

As to why the SLEEP function seems to be skipped, here is my best guess:

Way back in MySQL 5.0.12, SLEEP was getting inserted into the query cache via some optimization. It could be possible that the Stored Procedure's internal code is being optimized and mysqld decided not to let SLEEP interfere with the formation of the EXPLAIN plan and its execution.

The patch for it was applied when back in MySQL 5.0. Could that patch have been missed when going from MySQL 5.0 to MySQL 5.1 and beyond? That's one possibility. Another possibility is that SLEEP is optimized away in spite of the patch.

Some use SLEEP to imbed mutexing, perform stress testing, or fancy job scheduling

Using SLEEP does not always perform as expected.

UPDATE 2012-11-27 15:50 EDT

If you really want to control the SLEEP function, DO NOT USE THE FUNCTION !!!

Create a loop that checks for the passing of time

Instead of your code

delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  do sleep(300);
  insert into init_test_tab VALUES(p);
END
$$

imbed the loop like this:

delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  declare dt_now,dt_then datetime;
  declare x int;

  set dt_now = now();
  set dt_then = dt_now + interval 300 second;
  while dt_now < dt_then do
    set dt_now = now();
  end while;

  insert into init_test_tab VALUES(p);
END
$$

Please see if that construct work for you.

Mysql – Unusual issue with localhost:3000 after watching ruby on rails 4 tutorial: Lynda.com

This is how I have always set up my rails projects, using SQLite in your test and development will save you this heartache!

#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: mysql2
  encoding: utf8
  database: #your db name
  username: #your db username
  password: #your db password
  host: #your db hostname
  port: 3306