Home Ask Login Register

Developers Planet

Your answer is one click away!

toomanyairmiles February 2016

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?

Answers


Álvaro González February 2016

There are two syntaxes to change a session value (both should be equivalent):

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

To read the value back:

SELECT @@SESSION.sql_mode;

Of course, since it is a session value:

  • It won't affect the global setting
  • It won't survive past current session

Post Status

Asked in February 2016
Viewed 3,045 times
Voted 7
Answered 1 times

Search




Leave an answer


Quote of the day: live life