Having switched from Oracle to MySQL we were chugging along with development when we realized that in MySQL, autocommit is set to 1
by default. Ie, if you don’t explicitly start and end a transaction, each statement will commit as it executes. Type “delete from user” in the MySQL client, hit return, think “oops”, and it’s too late. “rollback” won’t help you now.
So I used “init_connect” to set autocommit=0 for incoming connections. (In my.cnf, I added init_connect=’set autocommit=0′.) Seemed to work great. (init_connect can be used to set session-level variables for connections.)
Then we moved this change from dev and QA into production. Suddenly, our UI wasn’t working. We were scrambling to figure out what was different between QA and production. I was told that the permissions must be different. Being a real newbie, I compared all the grants for ‘UI’ in the information_schema database and said they were exactly the same. What it took me a couple of hours to figure out was that I needed to check the permissions the the mysql database too. Checked mysql.user and found out that ‘UI’ had been granted ‘super’ in dev and QA, but not production. Users with ‘super’ bypass init_connect. (That way, if you set something in init_connect that really screws things up and won’t let people connect, you can still get in with a ‘super’ user to fix things up.)
So, how could I fix this without granting ‘UI’ the super privilege in production? I followed MySQL support’s suggestion (thanks, Todd Farmer) and created a small stored procedure that sets autocommit to 0 unless you’re ‘UI’, and changed init_connect to call the procedure.
The procedure is as follows:
CREATE PROCEDURE session_init()
IF USER() like ‘UI%’ THEN
And my init_connect is set to ‘call init.session_init()’. ‘init’ is a database that just holds the procedure session_init(). Then I grant all the users execute on init.*. (Is there a way to grant a user execute on a single procedure rather than on databasename.* – ie all the procedures in a database? Would be nice…) Problem solved. (Yes, of course, there is a way to grant execute on a single procedure. See the comments. – Ben)