How can I set a default session sql_mode for a given user?

I want to have a default session sql_mode that is different from the global sql_mode.

I can set it manually, but I'd like to do it automatically.

I thought I could add it to .my.cnf, but that doesn't work. I tried adding it to the [mysql] group in .my.cnf, and it does not throw an error.

Yet, when I connect my session sql_mode, the session still inherits the global sql_mode.

I'm using MySQL 5.5.16.

As long as the account in question doesn't have the SUPER privilege, you could accomplish this with the global server variable init_connect, which is...


A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters.

This is part of the global server configuration and gets executed right before processing of the first query sent by each new client connection (except for users with SUPER, because if you broke this, you couldn't log in to fix it otherwise).

The init_connect contents are executed by the server for all non-SUPER users... however, we could construct a CASE expression that uses the identity of the logged-in user via the CURRENT_USER() function and evaluates to the appropriate sql_mode for that user, and otherwise use the default.

The expression might be something like this:

mysql> SET @@sql_mode = CASE CURRENT_USER()
                        WHEN 'usera@%' THEN 'TRADITIONAL'  # this user gets 'TRADITIONAL'
                        WHEN 'userb@localhost' THEN 'ANSI' # this user gets 'ANSI'
                        ELSE @@sql_mode                    # everybody else gets the default value,
                        END;                               # from global sql_mode

Once you have the expression you want, wrap it up in string and assign it to the global variable init_connect.

mysql> SET GLOBAL init_connect = 
           "SET @@sql_mode = CASE CURRENT_USER()
                             WHEN 'usera@%' THEN 'TRADITIONAL'
                             WHEN 'userb@localhost' THEN 'ANSI'
                             ELSE @@sql_mode 
                             END;";

The same thing could then be added to the [mysqld] section of my.cnf to make your configuration persist across restarts, although you'd almost certainly have to remove the whitespace I added for readability and possibly adjust the quoting.

Tested on MySQL Server 5.5.30. I attempted to benchmark whether this has any significant impact on initial connection time or first query execution time and my conclusion is that if there is an impact, it is most definitely in the sub-millisecond range and was too short to identify on the system under test.

StackExchange Database Administrators Q#31447, answer score: 9