Search code examples
phpmysqlrestrictionstrict

Changing MySQL mode per session

I have a developer who is supporting an old application asking me to change our MySQL mode globally away from strict - however we have several other databases on the same server that apparently require strict mode.

I believe it's possible to change mode at session level and I've tried the following code to do it.

session_start();

include_once("./includes/conn.php");
mysql_query("SET SESSION sql_mode = ''");

But this doesn't seem to work - testing the mode reports back the global and session values are the same. 

session User : name
session Security : 27478239

MySQL server version : 5.5.5-10.0.23-MariaDB
GLOBAL : STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SESSION : STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Is it possible to change SQL mode at session level (or database level) and if so how can this be achieved?

Solution

  • There are two syntaxes (well, actually three) to change a session value, all of which should be equivalent:

    SET SESSION sql_mode = '';
SET @@SESSION.sql_mode = '';
SET @@sql_mode = '';

    To read the value back:

    SELECT @@SESSION.sql_mode;
SELECT @@sql_mode;

    Of course, since it is a session value:

    • It won't affect the global setting
    • It won't survive past current session
    • It won't leak to other sessions