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)
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.ini file in your 'home' directory on Windows.
Which version of 5.7 are you using? (There were several changes to
What client are you using? Perhaps it is injecting changes to
sql_mode to mess with your head.
Answered by Rick James on November 21, 2021
Get help from others!