0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.