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

created at 01-26-2022 views: 15

problem

I've searched for a lot of solutions online, but nothing works.

No matter what you do, the error is as follows:

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

solution

There are several key commands here, the most important one being to turn off safe mode

# Query the safe mode switch
show variables like 'sql_safe_updates';

# Turn off safe mode
set sql_safe_updates = 0;

# Only when safe mode is turned off can the authentication_string be empty
update user set authentication_string='' where user='root';

# When authentication_string is empty, you can actually change the password

#Refresh the permission table
flush privileges;

# change Password
alter user 'root'@'%' identified by 'S32*sdf312@';
alter user 'root'@'localhost' identified by 'S32*sdf312@';

# Choose one of the two above, refer to the following for details, one-to-one correspondence
select user, host from user;

appendix:

# Modify password strength
show variables like 'validate%';
set global validate_password.length = 4;
set global validate_password.policy = LOW;
created at:01-26-2022
edited at: 01-26-2022: