0

I'm quite stuck, I installed the new version of mysql (8.0.12) with homebrew. Then I dumped my databases from mysql (I was using the MAMP stack and did that to get rid of it...). However when importing the dump the databases were created, everything seemed to be in order. then I tried to connect my IDE (phpstorm) to it, and I got an error saying "Unable to load authentication plugin 'caching_sha2_password'." (I think this was introduced with this new version...).

I created a my.cnf file and put:

[mysqld] default_authentication_plugin=mysql_native_password

Still did have the same issue...

So I also tried creating a user with a password to be under this new algorithm (caching_sha2_password). Did not work as well, I logged in as sudo to mysql and it gave me the below:

MacBookAir$ sudo mysql Password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.12 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.db]

Last but not least, I changed the configuration in my.cnf file using this below: default_authentication_plugin=mysql_native_password

Any idea guys?

Cheers

LMD4U
  • 11
  • 7
  • Try https://stackoverflow.com/questions/16556497/how-to-reset-or-change-the-mysql-root-password/51444744#51444744 – Huseyin Nov 11 '18 at 08:10
  • No I tried this already, it gives me this mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; ***ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.db]*** – LMD4U Nov 11 '18 at 14:02

2 Answers2

0

So I decided to remove mysql 8, and install 5.7 which then works fine for me and obviously no compatibility issues. This is a quick and dirty alternative to the compatibility issue between mysql server 5.* and 8.0.

The issue was that I migrated my databases from mysql v 5.* (MAMP stack) to mysql server 8.0 (installed separately), therefore the dump was creating some of the system tables with MyIsam engine (as it seems that in prior version, I think Innodb couldn't be used for system tables...?). However, this was causing me a lots of issues in altering any system tables (user password etc...), I'm not sure whether it is because they are trying to get rid of the MyIsam engine and so new plugins are not adapted such as caching_sha2_password (and btw changing my.cnf file and adding "default_authentication_plugin=mysql_native_password" did not work for me...)

Thanks

LMD4U
  • 11
  • 7
0

MyISAM is based on the older (and no longer available) ISAM storage engine

... See https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

There are several checks you can run ON your MySQL 5.* installation to see if you can upgrade ( these are just examples):

mysqlcheck -u root -p --all-databases --check-upgrade

mysql_upgrade --user=root -p --host=localhost —force

If you have the mysql shell:

mysqlsh -- util checkForServerUpgrade root@localhost:3306 --target-version=8.0.16  --config-path=/etc/my.cnf

Those commands may fix, upgrade or repair problems with your database.

On your 5.* installation, I recommend you convert your MyISAM tables to InnoDB and use the new default character set utf8mb4 which has wider support for more character sets.

ALTER TABLE table_name ENGINE=InnoDB;

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

Use mysqldump to save your schema. Install MySQL 8 separately. (don't upgrade your 5.* installation to 8.) Import your mysqldump file into your mysql 8 installation under the new database you created.

Create user accounts: The user accounts must be created using the old authentication plugin - it's the only way PHP can connect. Also, you must be using PHP 7.2.4 or higher.

CREATE USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my_password';

Grant your user access to the appropriate schema. Something like:

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost';

NOTE: PHP's mysql native driver ( mysqlnd ) has not been updated to use the new default authentication plugin (caching_sha2_password) Tell mysql to use the old authentication plugin when creating user accounts by adding this to your /etc/my.cnf file:

default_authentication_plugin=mysql_native_password

But if you don't include the above directive in your my.cnf file, you can still issue the create user statement with the "IDENTIFIED WITH mysql_native_password" clause to use the old auth plugin!

James
  • 392
  • 4
  • 9