Question

Change mysql sql mode

Posted February 3, 2017 35.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?

Add a comment
jasmineyuan2016
By:
jasmineyuan2016
Add a comment
1 comment
  • ianmitchell777 July 5, 2018
    Reply Report

    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

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

4 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 Report
  • TurkeyHunter November 15, 2018

    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
    Reply Report
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 Report
subbusubrahmanyam99 August 2, 2019

sqlmode=NOENGINE_SUBSTITUTION

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.

Reply Report
Archie22is November 26, 2019

I came across this solution online by Nathaniel Hammond: https://gist.github.com/nfourtythree

The implementation is swift and takes just a couple of seconds or minutes:

  1. Log into your server as root
  2. Get script wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh
  3. Make is executable chmod +x disable-strict-mode.sh
  4. Run the script ./disable-strict-mode.sh

You can keep the script where you downloaded it for future mods or references or you can rm -rf disable-strict-mode.sh and delete the shell script.

You will then have MySQL 5.7 without strict mode.

DISCLAIMER: Not sure if it works with version 8.0

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help