How to fix several compatibility issues with new MySQL versions
Let’s see how to solve some problems after updating MySQL server. All ones fixes in one place by one method, so I show detailed only first solve and next will be more short.
... which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
Find path of mysqld program:
Find file which you can use for change mysqld options. Change mysqld path in command if you need.
sudo /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Remember printed filenames. Actually remember anyone filename e. g. “/etc/mysql/my.cnf”.
Next you need to know active sql modes:
sudo mysql -u root -p -e "select @@sql_mode"
Copy printed constants to somewhere. Drop “ONLY_FULL_GROUP_BY” constant from list. Next go to open filename of mysqld options file:
sudo mcedit /etc/mysql/my.cnf
And after “!includedir” put “[mysqld]” section (if it missing) and write into [mysqld] section:
sql_mode = "YOUR_NEW_CONSTANTS_LIST"
Save file and restart mysql server:
sudo service mysql restart
Incorrect integer value
Open options file like above and from constants list drop “STRICT_TRANS_TABLES” constant. Save file and restart mysql server.
Incorrect datetime value: ‘0000-00-00 00:00:00’
Again, open options file and now drop constant “NO_ZERO_DATE” from list. Save file and restart mysql server.