MySQL - Operation ALTER USER failed for 'root'

Error Message:

Operation ALTER USER failed for 'root'

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

or

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

The case:

root password was damaged and login with root user is failed.

Solution:

1. Connect to MySQL with another user
2. Update root set authentication_string=null
3. FLUSH PRIVILEGES;
4. Logout (quit)
5. Connect to MySQL using "mysql -u root"
6. Set root password
7. Logout (quit)

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -uusername -ppassword

 

mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.31 sec)

 

mysql> quit

 

Bye

 

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root

 

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'newrootpassword';

Query OK, 0 rows affected (0.26 sec)

mysql> quit

Bye