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.
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.