Bug #42035 session defaults on the account level
Submitted: 11 Jan 2009 22:15 Modified: 1 Oct 2009 7:39
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[11 Jan 2009 22:15] Roland Bouman
Description:
Many applications require specific session level settings with regards to storage_engine, sql_mode, and autocommit. It would be nice if the application's user account could be defined along with the set of defaults so that these would be automatically applied when the account is used to establish a connection.

Not only would this feature help new applications which require specific settings, it would also provide a clean an maintainable mechanism to run applications that expect MySQL to be completely default on servers that are configured to run with special options that are incompatible with these applications

How to repeat:
na

Suggested fix:
Add the ability to specify default session-level settings on the account level so that these can automatically be applied as soon as the user connects. 

Example syntax:

CREATE USER user@host IDENTIFIED BY 'password'
SESSION DEFAULTS SET 
    SQL_MODE := 'TRADITIONAL,NO_ENGINE_SUBSTITUTION,IGNORE_SPACE,PIPES_AS_CONCAT,ANSI_QUOTES'
,   AUTOCOMMIT := OFF
,   STORAGE_ENGINE := INNODB
;
[12 Jan 2009 7:57] Valeriy Kravchuk
Thank you for a reasonable feature request.
[1 Oct 2009 7:35] Shane Bester
This is a hackish way of doing it:

mysqld --init-connect="set session sql_mode=case current_user() when 'shane@%' then 'strict_all_tables' when 'roland@%' then 'strict_trans_tables' when 'bob@%' then ''  else 'traditional' end;"
[1 Oct 2009 7:39] Roland Bouman
Hi Shane,

thanks!

I have been thinking along these lines, but stopped pursuing it because it would become pretty unmaintainable. I envisioned a solution that would use a stored procedure so you can specify it all in database tables and manage it from there, but unfortunately you cannot persistently set the SQL_MODE from within a stored routine.

Really, it would be so much nicer if this could be dealt with in a clean way.

Anyway thanks for the suggestion. I'll link to it from the relevant blog post.