Change mysql sql mode

February 3, 2017 436 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?

2 Answers

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

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

Have another answer? Share your knowledge.