Question
Change mysql sql mode
- Posted on February 3, 2017
- ApplicationsUbuntu 16.04
Asked by jasmineyuan2016
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!
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.
Want to learn more? Join the DigitalOcean Community!
Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.
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,modeis 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.
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:
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