jasmineyuan2016
By:
jasmineyuan2016

Change mysql sql mode

February 3, 2017 7.6k views
Applications Ubuntu 16.04

i have mysql installed and table created. i want to remove stricttranstables from sql mode, how to do that? I tried the fonllowing: 1) go to phpmyadmin variable and edit sql mode, but mysql reset it back to default value

2) followed https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting and tried in command line to reset sql mode:

sudo mysqld --sql mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

my command line went frozen!?

3) located file /etc/mysql/my.cnf and tried to change setting there, but didn't find any sql mode info.

Can i change sql mode after installation? how?

Log In to Comment
2 Answers
dinnysingh May 2, 2017

Just add the line:
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

inside file:
/etc/mysql/mysql.conf.d/mysqld.cnf

then
sudo service mysql restart

there you go....

Reply
jtittle1 February 3, 2017

@jasmineyuan2016

Your MySQL configuration files are located in /etc/mysql. By default, mode is not set, so you'll need to scan the files and find where [mysqld] is defined for your installation and then under that tag, add:

sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

.. and then restart MySQL. If you set this from the CLI, without using the configuration files and restarting MySQL, it won't persist.

Reply
Have another answer? Share your knowledge.

Related Questions