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:

which mysqld

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.

 10   24 d  
Popular