Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the pits that MySQL 8.0 walked through". The content is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "what are the pits that MySQL 8.0 walked through?"

First problem: Navicat cannot connect to the database

The installed mysql is localhost:3306, and everything is configured by default. Open a new connection in Navicat 12 after installation and report an error directly.

Authentication plugin 'caching_sha2_password'

Authentication plug-in cannot be loaded

Check the official document 6.5.1.3 Caching SHA-2 Pluggable Authentication

Originally, in MySQL 8.0, caching_sha2_password replaced mysql_native_password as the default authentication plug-in. The official solution is as follows

Reconfigure the server to revert to the previous default authentication plug-in (mysql_native_password).

[mysqld] default_authentication_plugin=mysql_native_password

This setting allows clients prior to 8.0 to connect to the 8.0 server, however, this setting should be treated as a temporary setting, not a long-term or permanent solution, as it causes new accounts created with valid settings to relinquish the improved authentication security caching_sha2_password provided.

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

For a new MySQL 8.0 installation, when initializing the data directory, the account 'root'@'localhost', is created and the account uses caching_sha2_password by default. Connect to the server root and use ALTER USER to change the account authentication plug-in and password as follows:

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

At this point, the problem of default authentication replacement for MySQL 8.0 has been resolved.

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

After updating the database, a small java project was started locally, and a test program was run to connect to the database to directly throw an exception. Check the official document Changes in MySQL 8.0.1 (2017-04-10, Development Milestone). The original version 8.0.1 has made several important changes to the Unicode character set support, and the default character set has changed from latin1 to utf8mb4. The default collation_server and collocation_database system variables for this system are changed from latin1_swedish_ci to utf8mb4_0900_ai_ci.

Solution: all of these changes have been handled in the new version of MySQL Connector Java, and there is no need to configure MySQL. So you just need to upgrade the version of MYSQL, change from 5.1.6 to 5.1.44, and the problem is solved perfectly.

Mysql mysql-connector-java 5.1.44

Question 3: enter the database show databases; after installation, or try to change the permissions.

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

Table 'mysql.role_edges' doesn't exist

Solution method

Mysql_upgrade-u root-p

Problem 4: after the client successfully connects to the database, it is found that the pdo connection mysql in the project has been reported wrong 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 mysql's default use of caching_sha2_password as the default authentication plug-in instead of mysql_native_password, but the client does not support this plug-in for the time being. The official document states

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 plug-in, not mysql_native_password. For information about the impact of this change on server operation and server compatibility with clients and connectors, see caching_sha2_password as the preferred authentication plug-in.

Solution method

Edit the my.cnf file to change the default authentication plug-in.

$vi / etc/my.cnf

Add the following code to [mysqld]

Default_authentication_plugin=mysql_native_password

Then restart mysql

$service mysqld restart

The website finally opened normally.

The above is all the contents of the article "what are the pits that MySQL 8.0 walked through?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.