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]
[mysqld] default-authentication-plugin = mysql_native_password
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',''));
On inserts or updates MySQL may complain a default value is not set even when the deafult value may be NULL. The cause is a ‘STRICT_TRANS_TABLES’ setting.
SET GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION';