SQL syntax error for update or alter table root user

Question

after new installation of ubuntu 20.04 i get error to connect to mysql with root user. for below commands after using

sudo mysqld_safe --skip-grant-tables --skip-networking
mysql -u root

i get syntax error and i can't update root password:

Mysql version:

mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

command:

update `user` set `authentication_string`=PASSWORD("") where `User`='root';
    >ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '("") where `User`='root'' at line 1

and when i try to use Alter command i get this message:

ALTER USER 'root'@'localhost' IDENTIFIED BY '';
    >ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Answer 1

Try to use single quote, not double or set password don't use empty string

update user set `authentication_string`=PASSWORD('') where user='root';

or

update user set `authentication_string`=PASSWORD('yourpassword') where user='root';

then flush!

flush privileges;

Answer 2

The syntax error you got on the first statement was because MySQL 8.0 doesn't recognize PASSWORD as a built-in function anymore.

The PASSWORD() function was deprecated in MySQL 5.7 and removed in MySQL 8.0. See documentation here: https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password

The proper way to reset the root password in MySQL 8.0 is documented here: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

You restart mysqld with skip-grant-tables as you have done. Then open the MySQL client as you have done, without giving a password.

Then reload the grant tables so account-management statements work. I think this is the step you're missing.

mysql> FLUSH PRIVILEGES;

Then you can do the ALTER USER statement:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';