Cannot connect to mysql : Unable to load authentication plugin 'caching_sha2_password
up vote
0
down vote
favorite
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
mysql
asked Nov 10 at 22:00
LMD4U
95
add a comment |
up vote
0
down vote
favorite
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
mysql
asked Nov 10 at 22:00
LMD4U
95
Try stackoverflow.com/questions/16556497/…
– Huseyin
Nov 11 at 8: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 at 14:02
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
mysql
asked Nov 10 at 22:00
LMD4U
95
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
mysql
mysql
asked Nov 10 at 22:00
LMD4U
95
asked Nov 10 at 22:00
LMD4U
95
asked Nov 10 at 22:00
LMD4U
95
asked Nov 10 at 22:00
LMD4U
95
asked Nov 10 at 22:00
LMD4U
95
95
Try stackoverflow.com/questions/16556497/…
– Huseyin
Nov 11 at 8: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 at 14:02
add a comment |
Try stackoverflow.com/questions/16556497/…
– Huseyin
Nov 11 at 8: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 at 14:02
Try stackoverflow.com/questions/16556497/…
– Huseyin
Nov 11 at 8:10
Try stackoverflow.com/questions/16556497/…
– Huseyin
Nov 11 at 8: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 at 14:02
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 at 14:02
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
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
answered Nov 11 at 15:47
LMD4U
95
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
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
answered Nov 11 at 15:47
LMD4U
95
add a comment |
up vote
0
down vote
accepted
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
answered Nov 11 at 15:47
LMD4U
95
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
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
answered Nov 11 at 15:47
LMD4U
95
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
answered Nov 11 at 15:47
LMD4U
95
answered Nov 11 at 15:47
LMD4U
95
answered Nov 11 at 15:47
LMD4U
95
answered Nov 11 at 15:47
LMD4U
95
95
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243842%2fcannot-connect-to-mysql-unable-to-load-authentication-plugin-caching-sha2-pas%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try stackoverflow.com/questions/16556497/…
– Huseyin
Nov 11 at 8: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 at 14:02