check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD(“newpass”)'

Question

I'm using Mysql 8.0.12 version and I am currently facing a problem and I can't solve it even with the references around the internet. I have this error on phpmyadmin . By the I'm using High Sierra 10.13

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 'PASSWORD("newpass")' at line 1

I tried this command on the terminal

 sudo /user/local/mysql/bin/mysql -u root

then entered my password and then

use mysql;

update user set authentication_String = PASSWORD("newpass") where user='root';

then tried this also

update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';

and lastly this

UPDATE user SET authentication_string=PASSWORD("newpass") WHERE User='root';

But they all gave me the same error

Could someone please help me out . I'm stuck here.

Answer 1

The syntax you were using was close. It should be:

USE mysql;
UPDATE user SET Password=PASSWORD('NEW-ROOT-PASSWORD') WHERE USER='root';

However you don't need to directly edit the mysql tables, rather use the dedicated commands:

SET PASSWORD FOR 'root'@'localhost' = 'NEW-ROOT-PASSWORD';

Note that the manual says that the syntax below is 'preferred' without hinting why you might choose one over the other:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW-ROOT-PASSWORD';