MySQL sql_mode for remote access I have setup MySQL with the default conf /etc/my.cnf as following:

[mysqld]

user=root

innodb_file_per_table

innodb_file_format=Barracuda

innodb_large_prefix

log_bin_trust_function_creators

sql-mode='NO_AUTO_CREATE_USER'

max_sp_recursion_depth=10

show_compatibility_56=ON

performance_schema

character-set-server=utf8mb4

[client]

socket=/home/abc/mysql_home/socket

[mysql]

default-character-set=utf8mb4


Accordingly, when I run select @@global.sql_mode, @@session.sql_mode, @@sql_mode; from Linux machine, I see :

NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER 

1 row in set (0.00 sec)


Where as if I connect to the same instance and same user from the Windows machine (SQL developer), I see the following :

 NO_AUTO_CREATE_USER |STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER 

1 row in set (0.00 sec)


Basically, mode STRICT_TRANS_TABLES is getting added for the session variable though I have not made any session level updates. This MySQL strict mode is causing further issues.

Would like to know how this STRICT_TRANS_TABLES is getting added by default only for the remote connection?

Commands used for creating the user :

CREATE USER 'USER_MYSQL'@'localhost' IDENTIFIED BY 'USER_MYSQL';
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'localhost' WITH GRANT OPTION;
CREATE USER 'USER_MYSQL'@'%' IDENTIFIED BY 'USER_MYSQL';
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;


Using MySQL 5.7 version.
/etc/my.cnf is not the only place to get such things.  It sounds as if you have a .my.cnf / my.ini file in your 'home' directory on Windows.

Which version of 5.7 are you using?  (There were several changes to sql_mode.)

What client are you using?  Perhaps it is injecting changes to sql_mode to mess with your head.