Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ERROR 1064 (42000): You have an error in your SQL syntax; Want to configure a password as root being the user

Tags:

mysql

I have just downloaded WAMP. I want to configure a password for the MySQL root user using MySQL console. No password has been set previously.

The following is the input

    mysql-> use mysql     Database changed     mysql-> UPDATE user          -> SET Password=PASSWORD<'elephant7'>          -> 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 3

like image 616
Syed Md Ismail Avatar asked Mar 19 '16 07:03

Syed Md Ismail


People also ask

What does error 1064 mean in MySQL?

Error #1064 means that MySQL can't understand your command. To fix it: Read the error message. It tells you exactly where in your command MySQL got confused.

How do I change the root password in MySQL?

To change the root password, type the following at the MySQL/MariaDB command prompt: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd'; flush privileges; exit; Store the new password in a secure location.

What is you have an error in your SQL syntax?

During application update an error message containing "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 ..." appears in the log. It means your database is outdated and it can't work with the request our application sends to it.


2 Answers

I was using MySQL 8 and non of the above worked for me.

This is what I had to do:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; 
like image 92
Sahith Vibudhi Avatar answered Oct 02 '22 18:10

Sahith Vibudhi


You can use:

SET PASSWORD FOR 'root' = PASSWORD('elephant7'); 

or, in latest versions:

SET PASSWORD FOR root = 'elephant7'  

You can also use:

UPDATE user SET password=password('elephant7') WHERE user='root'; 

but in Mysql 5.7 the field password is no more there, and you have to use:

UPDATE user SET authentication_string=password('elephant7') WHERE user='root'; 

Regards

like image 32
White Feather Avatar answered Oct 02 '22 16:10

White Feather