Database Administrators Asked by Lakshmikantha on November 21, 2021
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.
Answered by Rick James on November 21, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP