Detailed explanation of the pitfalls of MySQL 8.0

I updated MySQL 8.0 today.

The first problem: Navicat cannot connect to the database

The installed mysql is localhost:3306, and all settings are default. After installation, open Navicat 12 to create a new connection and report an error directly

authentication plugin 'caching_sha2_password'

The authentication plugin could not be loaded

Checked the official document 6.5.1.3 Caching SHA-2 Pluggable Authentication

It turns out that in MySQL 8.0, caching_sha2_password replaced mysql_native_password as the default authentication plugin. The official solution is as follows

1. Reconfigure the server to revert to the previous default authentication plugin (mysql_native_password).

[mysqld]
default_authentication_plugin=mysql_native_password

This setting allows pre-8.0 clients to connect to 8.0 servers, however, the setting should be considered a temporary setting rather than a long-term or permanent solution because it causes new accounts created with the effective setting to forgo the improved authentication security provided by caching_sha2_password .

2. Change the authentication method of the root administrative account to mysql_native_password.

For new MySQL 8.0 installations, the account 'root'@'localhost' is created when the data directory is initialized, and this account will use caching_sha2_password by default. Connect to the server root and use ALTER USER to change the account authentication plugin and password as follows:

ALTER USER 'root'@'localhost'
 IDENTIFIED WITH mysql_native_password
 BY 'password';

At this point, the default identity verification replacement issue for MySQL 8.0 has been resolved.

Second question: Caused by: java.sql.SQLException: Unknown initial character set index '255'...

After updating the database, I started a small Java project locally, connected to the database and ran a test program, but an exception was thrown. I checked the official document Changes in MySQL 8.0.1 (2017-04-10, Development Milestone) and found that version 8.0.1 made several important changes to the Unicode character set support, and the default character set was changed from latin1 to utf8mb4. The default collation_server and collocation_database system variables of this system are changed from latin1_swedish_ci to utf8mb4_0900_ai_ci.

Solution: All these changes have been taken care of in the new version of MySQL Connector Java and no configuration of MySQL is required. So you just need to upgrade the MYSQL version, change 5.1.6 to 5.1.44, and the problem will be solved perfectly.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.44</version>
  </dependency>

Question 3: After the installation is complete, enter the database show databases; or try to change permissions and an error message appears

ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
Table 'mysql.role_edges' doesn't exist

Workaround

mysql_upgrade -u root -p;

Question 4: After the client successfully connected to the database, it was found that the pdo connection to MySQL in the project reported an error again.

Next PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client [caching_sha2_password] in /vendor/yiisoft/yii2/db/Connection.php:687

This error may be caused by the fact that MySQL uses caching_sha2_password as the default authentication plugin instead of mysql_native_password, but the client does not currently support this plugin. Official documentation

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. For information about the implications of this change for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

In MySQL 8.0, caching_sha2_password is the default authentication plugin instead of mysql_native_password. For information about how this change affects server operation and server compatibility with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

Workaround

Edit the my.cnf file and change the default authentication plugin.

$ vi /etc/my.cnf

Add the following code in [mysqld]

default_authentication_plugin=mysql_native_password

Then restart mysql

$ service mysqld restart

The website finally opened normally. . .

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.