Change mysql sql mode

February 3, 2017 29.5k 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 and tried in command line to reset sql mode:


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?

1 comment
  • Thank you so much dinnysingh. Fix to mysqld.cnf worked!! Have been on this problem for two days and all answers regarding previous versions of UBUNTU do not work. ianm

3 Answers

Just add the line:

inside file:

sudo service mysql restart

there you go....

  • I am having the same issue and following these steps did not fix the problem:

    1. add sqlmode = “NOAUTOCREATEUSER,NOENGINESUBSTITUTION” to /etc/mysql/mysql.conf.d/mysqld.cnf
    2. sudo service mysql restart


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:


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


mysqld_safe] section of /etc/my.cnf! Because it didn’t work when the server rebooted, I searched for other .cnf files. Correctly adding it to the [mysqld] section of /etc/my.cnf works perfectly.

Have another answer? Share your knowledge.