on autocommit, init_connect, and ‘super’

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()
BEGIN
IF USER() like ‘UI%’ THEN
SET autocommit=1;
ELSE
SET autocommit=0;
END IF;
END

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)

Advertisements

5 Responses to “on autocommit, init_connect, and ‘super’”

  1. Jeremy Cole Says:

    Hi,

    “we realized that in MySQL, autocommit is set to 0 by default”

    You mean it’s set to 1 by default.

    In any case, this doesn’t make sense to me. You explain that you do NOT have the SUPER privilege in production, but that’s where the init_connect wasn’t working? Isn’t that backwards? Could you explain?

    Regards,

    Jeremy

  2. Tobias "flupps" Asplund Says:

    mysql> CREATE PROCEDURE test.proc () BEGIN END;
    Query OK, 0 rows affected (0.00 sec)

    mysql> GRANT EXECUTE ON PROCEDURE test.proc TO user@host;
    Query OK, 0 rows affected (0.00 sec)

  3. Xaprb Says:

    This gotcha still gets me sometimes.

    BTW if you’re typing DELETE FROM USER as in “delete a MySQL user” those tables are MyISAM and can’t be rolled back.

  4. ben Says:

    Jeremy – thanks; I corrected my typing about the default for autocommit. The ‘super’ problem was that init_connect WAS working in production. And it didn’t work with our code – our code turned out not to work with autocommit=0. We didn’t realize it because when we tested in dev and QA, the app user bypassed init_connect (being super) and so still had autocommit=1.

    Tobias – thanks too; I need to RTFM!

    Xaprb – Also thanks. I was thinking of our own ‘user’ table, not information_schema.user. Should have used a better example…

  5. encaps php gallery Says:

    What may be wrong when transaction doesn’t work?
    I did:
    >SET autocommit=0;
    >START TRANSACTION;
    >INSERT INTO some_table (‘some_value);
    >ROLLBACK;

    but ‘some_value’ is inserted into some_table 😦

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: