Sfrgttk

What happened to my GE option?

False written accusations not made public - is there law to cover this?

Eww, those bytes are gross

How to assess the long-term stability of a college as part of a job search

Why did Luke use his left hand to shoot?

Boss asked me to sign a resignation paper without a date on it along with my new contract

Globe trotting Grandpa. Where is he going next?

What game did these black and yellow dice come from?

How to deal with possible delayed baggage?

Short story where statues have their heads replaced by those of carved insect heads

Changing the laptop's CPU. Should I reinstall Linux?

Why don't key signatures indicate the tonic?

Most demanding German Newspapers

How to access internet and run apt-get through a middle server?

Strange "DuckDuckGo dork" takes me to random website

In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?

Why do neural networks need so many training examples to perform?

What will happen if Parliament votes "no" on each of the Brexit-related votes to be held on the 12th, 13th and 14th of March?

Does Skippy chunky peanut butter contain trans fat?

Is there any other language containing the sound of the "evanescent l" in Venetian?

Potential client has a problematic employee I can't work with

How to not let the Identify spell spoil everything?

Does diversity provide anything that meritocracy does not?

Has Britain negotiated with any other countries outside the EU in preparation for the exit?

MySQL sql_mode for remote access

MySQL 5 on Mac 10.5: “problem connecting to the database”mysql 5.1 — unknown variable 'basedir=c:/mysql/'Confused by GRANT ALL PRIVILEGES ON *.* (…) WITH GRANT OPTIONError 1236 'Found old binary log without GTIDs' after master restartMysql server does not start if binary logging is disabledCan't access MySQL database with created user through dllMaster/Slave MySQL error in replicationMySQL connection fails with one database and not the otherMySQL: initialize the Data Directory (--data-dir) for Operating System user other than mysqlMySQL Workbench is adding --enable-cleartext-plugin option on mysqldump command

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.

mysql mysql-5.7

share|improve this question

edited May 22 '18 at 23:45

Rick James

43k22259

asked May 7 '18 at 20:14

LakshmikanthaLakshmikantha

11

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

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.

mysql mysql-5.7

share|improve this question

edited May 22 '18 at 23:45

Rick James

43k22259

asked May 7 '18 at 20:14

LakshmikanthaLakshmikantha

11

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

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.

mysql mysql-5.7

share|improve this question

edited May 22 '18 at 23:45

Rick James

43k22259

asked May 7 '18 at 20:14

LakshmikanthaLakshmikantha

11

[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

NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER 



1 row in set (0.00 sec)

 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)

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;

share|improve this question

edited May 22 '18 at 23:45

Rick James

43k22259

asked May 7 '18 at 20:14

LakshmikanthaLakshmikantha

11

share|improve this question

edited May 22 '18 at 23:45

Rick James

43k22259

asked May 7 '18 at 20:14

LakshmikanthaLakshmikantha

11

edited May 22 '18 at 23:45

Rick James

43k22259

edited May 22 '18 at 23:45

Rick James

43k22259

asked May 7 '18 at 20:14

LakshmikanthaLakshmikantha

11

asked May 7 '18 at 20:14

LakshmikanthaLakshmikantha

11

1 Answer
1

active

oldest

votes

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.

share|improve this answer

answered May 22 '18 at 23:45

Rick JamesRick James

43k22259

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f206086%2fmysql-sql-mode-for-remote-access%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered May 22 '18 at 23:45

Rick JamesRick James

43k22259

add a comment | 

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.

share|improve this answer

answered May 22 '18 at 23:45

Rick JamesRick James

43k22259

add a comment | 

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

share|improve this answer

answered May 22 '18 at 23:45

Rick JamesRick James

43k22259

share|improve this answer

answered May 22 '18 at 23:45

Rick JamesRick James

43k22259

answered May 22 '18 at 23:45

Rick JamesRick James

43k22259

answered May 22 '18 at 23:45

Rick JamesRick James

43k22259