Home / ubuntu / Questions / 1133664
Accepted
user9371654
Asked: 2019-04-14 14:09:41 +0800 CST

Cannot connect to MySQL database after upgrade to version 8.0

  • 772

I upgraded both MySQL work-bench to version 8.0 and MySQL server to version 8.0.15. I restarted my computer and I opened the workbench. I found my old connection. When I click test connection I get successful result.

enter image description here

When I double click on my connection I get this error message.

enter image description here

I tried to login to the MySQL server using and reset the password:

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

Please, what could be the problem?
I forgot what was the port number when I set up the connection in the old... version.

EDIT:

In the terminal, this is an attempt and it seems working. Just the workbench which I used to work from. Note that I use Ubunut 18.04:

$ mysql -u root -p -h 127.0.0.1 -P 3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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>
networking server upgrade mysql mysql-workbench

2 Answers

  1. The SQL v8.0 has changed the authentification protocol:
    On v8.0 it's using a caching_sha2_password.
    Before it was mysql_native_password.

    To create new user on previous version it was:

    GRANT ALL PRIVILEGES ON *.* TO 'rootV5'@'localhost' WITH GRANT OPTION;

    To create new user on v8.0 running this commands below (replace root and password by you're own of course !) :

    CREATE USER 'rootV8'@'%' IDENTIFIED BY 'password'; 
GRANT ALL PRIVILEGES ON *.* TO 'rootV8'@'%' WITH GRANT OPTION;

    To change the password encryption from old version user: 

    ALTER USER 'user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

    I hope you fix your problem with that :)

    • 2
  2. Best Answer

    From Commentz:

    The error message you're getting is:

    The user specified as a definer ('mysql.infoschema'@'localhost') doesn't exist.

    To solve this, run,

    mysql_upgrade -u root -p
mysql_install_db

    in terminal. If doesn't solve the problem, run

    > create user 'mysql.infoschema'@'localhost';

    in MySQL.

    • 1