Setup
mysql Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using EditLine wrapper
Distributor ID: Ubuntu Description: Ubuntu 18.04.5 LTS Release: 18.04 Codename: bionic
MYSQL Keeps reseting my sql_mode and so my website is not working well.
The MySQL message is the following
Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdevnew.f.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
What I do till now is from terminal to connect inside mysql with the command
mysql -u root -p
and run the following commands
mysql> set global sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
And the error is gone, but for a couple of days… lets say that we restart mysql server and the above settings are gone.
Also tried to change the following files with the sql_mode that i would like to load after mysql restart.
File list
/etc/mysql/my.cnf
/etc/mysql/mysql.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
(I’ve also tried with value in quotes)
Then restart mysql
sudo service mysql restart
But the setting is not affecting mysql restart and I keep getting the error mentioned above.
mysqld --verbose --help
prints the following
sql-mode NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Any ideas of what am I doing wrong?
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!
Hi there,
I’ve just tested this and it seems to be working well when the change is added to the /etc/mysql/mysql.conf.d/mysqld.cnf file under the [mysqld] section.
Do you see any errors in your MySQL error log that could indicate on what the issue might be?
- tail -100 /var/log/mysql/error.log
Also check for any other references of sql_mode:
grep -r sql_mode /etc/mysql/*
You might have to remove the duplicate ones.
Best,
Bobby
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.
Start building today
From GPU-powered inference and Kubernetes to managed databases and storage, get everything you need to build, scale, and deploy intelligent applications.
