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.

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.