This page describes various issues that users may see with MySQL, and ways of fixing them.
Common issues that can cause the MySQL server to fail to start.
[ERROR] unknown variable 'variable=value'
This is caused by a configuration file or command line option not recognized by the server. If this happens after an upgrade, it's probably a custom config file that contained deprecated options that have been removed in the new version. Another possibility is that the server is trying to read a config file made for a different variant after switching between MySQL/MariaDB/Percona.
Other issues caused by changes in MySQL's default behaviour.
Unable to log in as root after upgrading to MySQL 5.7
MySQL 5.7 tries to be more secure by default. If the installation of the mysql-server-5.7 package detects a root account with an empty password, it will enable unix socket authentication for the account. This means that only the host's root user can log in to MySQL as root. To restore normal password authentication to the root user, log in to the server and run:
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '<passphrase>';
The passphrase can be empty if desired, though this is not recommended.
SQLs start throwing errors after upgrading to MySQL 5.7
MySQL 5.7 also tries to be more strict by default, and will no longer accept values that are considered invalid, such as all-0 dates or values outside the bounds of the field.
The default SQL modes for the different major versions are: 5.5:
- ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
More information about the modes and changing them can be found at http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html