Handling MySQL 8

 

MySQL 8 has been around since 2016 but migrating to this version from an older one recently revealed two issues. Here they are with two quick solutions.

Tested for:  Server version: 8.0.12 MySQL Community Server – GPL

cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.5 (Maipo)

First issue was the new default authentication plugin:

Fatal error: Uncaught PDOException: PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]

solution:

cd /etc/my.cnf

add:

[mysqld]
default-authentication-plugin = mysql_native_password

then

mysql -uroot -p

CREATE USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secret';
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO 'myuser'@'localhost';

Grouping is a new feature in MySQL 8, but may not be compatible with the existing database schema:

Statement could not be executed (42000 - 1055 - 
Expression #55 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 
'my_database.my_table.my_field' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by)

solution: again log in as root

mysql -uroot -p
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Links

Advertisements