i have mysql installed and table created. i want to remove strict_trans_tables 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
- 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!?
- located file
/etc/mysql/my.cnfand tried to change setting there, but didn’t find any sql mode info.
Can i change sql mode after installation? how?
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
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:
- Log into your server as root
- Get script wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh
- Make is executable chmod +x disable-strict-mode.sh
- 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
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…
sql_mode=NO_ENGINE_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.
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and AI-native businesses
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.