How to fix ERROR 1064 (42000) syntax error?

by Jessie   Last Updated August 14, 2019 05:26 AM - source

I change my root password using update user set authentication_string=password("!QAZxsw2") where user="root"; this syntax but the error 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 '("!QAZxsw2") where user="root"' at line 1 keeps pop up. I don't know why please help!

I've google this question and most of the articles tell me to use update user set authentication_string=password("!QAZxsw2")where user ="root"; this syntax to change my root password. I can't find where did I make it wrong. By the way, I have also tried this code set password for [email protected] = password('!QAZxsw2'); However, still get the same result.

mysql> update user set authentication_string=password("!QAZxsw2") 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 '("!QAZxsw2") where user="root"' at line 1
Tags : mysql sql


Answers 2


Try to use string value in single quotes, for example,


update user set authentication_string=password('!QAZxsw2') where user='root';
see link - https://www.geeksforgeeks.org/mysql-change-user-password/

For MySQL version 5.7.6 or greater use command

SET PASSWORD FOR 'root'@'localhost' = PASSWORD("!QAZxsw2");
user1408541
user1408541
August 14, 2019 04:34 AM

MySQL 5.7.5 and earlier

mysql> update user set password=PASSWORD("!QAZxsw2") where User='root';

MySQL 5.7.6 and newer

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("!QAZxsw2");

OR Try with the below command, it works for me in mysql8:

mysql> SET PASSWORD FOR 'root'='!QAZxsw2';

Ponni
Ponni
August 14, 2019 04:46 AM

Related Questions