Ygthkb

How do I append a character to the end of every line in an excel cell?

Do authors have to be politically correct in article-writing?

Citing paid articles from illegal web sharing

Airplane generations - how does it work?

Cat is tipping over bed-side lamps during the night

Current across a wire with zero potential difference

Square Root Distance from Integers

Why did Luke use his left hand to shoot?

How does one write from a minority culture? A question on cultural references

Why would space fleets be aligned?

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?

Does diversity provide anything that meritocracy does not?

How can I play a serial killer in a party of good PCs?

Why avoid shared user accounts?

Why is it that Bernie Sanders is always called a "socialist"?

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

Why did the villain in the first Men in Black movie care about Earth's Cockroaches?

What language shall they sing in?

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

Is there a lava-breathing lizard creature (that could be worshipped by a cult) in 5e?

Most demanding German Newspapers

Strange "DuckDuckGo dork" takes me to random website

Does Skippy chunky peanut butter contain trans fat?

Has any human ever had the choice to leave Earth permanently?

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♦ 14 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♦ 14 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