Bug #86484 Option to set sql_mode on a database level
Submitted: 27 May 2017 10:03 Modified: 27 May 2017 12:47
Reporter: lalit Choudhary Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: SQL_MODE

[27 May 2017 10:03] lalit Choudhary
Description:
Currently, sql_mode variable can be set for the mysql server on a GLOBAL level for all of its objects. Also, it can be set dynamically for session/ global level for mysql server.

With new mysql performance improvement and capability handle a large amount of data user started hosting multiple application databases on single mysql server.And here the need of different sql_mode for a database within mysql server comes up.

Scenario: MySQL server with 2 databases for 2 applications.
There is possibility that ,
1 Application DB needs sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE

and Other Application DB needs sql_mode with NO_ZERO_IN_DATE,NO_ZERO_DATE due application needs of having a date with ZERO.

In such cases, we can not set different sql_mode for 2 or more databases on single mysql server.

There should be an option to set sql_mode on database level.

Workaround :

Set sql_mode while taking connection for a particular user.

we can use set sql_mode for particular user connection using MySQL init_connect variable option.

SET GLOBAL init_connect = "SET @@sql_mode = CASE CURRENT_USER()
                             WHEN 'sql@%' THEN ''
                             ELSE @@sql_mode
                             END;" 

How It works:
For 'sql_user@%' USER for each connect sql_mode will set to ‘’ [blank], before executing any other query. We can say it’s a part of connection initialization.  

How to repeat:
Scenario: MySQL server with 2 databases for 2 applications.
There is possibility that,
1 Application DB needs sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE

and Other Application DB needs sql_mode with NO_ZERO_IN_DATE,NO_ZERO_DATE due application needs of having a date with ZERO.

In such cases, we can not set different sql_mode for 2 or more databases on single mysql server.

There should be an option to set sql_mode on database level.

Suggested fix:
Scenario: MySQL server with 2 databases for 2 applications.
There is possibility that,
1 Application DB needs sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE

and Other Application DB needs sql_mode with NO_ZERO_IN_DATE,NO_ZERO_DATE due application needs of having a date with ZERO.

In such cases, we can not set different sql_mode for 2 or more databases on single mysql server.

There should be an option to set sql_mode on database level.
[27 May 2017 12:47] lalit Choudhary
Correct Scenario: MySQL server with 2 databases for 2 applications.
There is possibility that ,
1 Application DB needs sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE

and Other Application DB needs sql_mode without NO_ZERO_IN_DATE,NO_ZERO_DATE due application needs of having a date with ZERO.

In such cases, we can not set different sql_mode for 2 or more databases on single mysql server.

There should be an option to set sql_mode on database level.