9

I am trying to reset my root password using following command:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test');

and it gives me following 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 'PASSWORD('test')' at line 1

Please let me know what is it that I am doing wrong here.

Tiny
  • 27,221
  • 105
  • 339
  • 599
ddesai
  • 499
  • 1
  • 6
  • 17

2 Answers2

16

I know it is old but it can help.

I hade this error when i used PASSWORD() function:

SET PASSWORD FOR 'myuser@localhost' = PASSWORD('my_new_password');

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('basket8')' at line 1

Because in MySQL 5.7 and later it is not necessary to use the PASSWORD() function:

SET PASSWORD FOR 'myuser'@'localhost' = 'my_new_password';
Query OK, 0 rows affected (0.01 sec)
Yacine Rouizi
  • 1,360
  • 2
  • 15
  • 31
5

You do not need to quote the user name and it should be as

mysql> set password for root@'localhost' = password('test');
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges ;
Query OK, 0 rows affected (0.00 sec)

The other way of re-setting the password is Login to mysql as root and then update the mysql database User table

abhik@-N4010:~$ mysql -u root -p
Enter password: 

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set Password = password('test') where host='localhost' and User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges ;
Query OK, 0 rows affected (0.00 sec)
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • 1
    I am getting the same error when I use your command. Did it work for you? – ddesai Apr 25 '15 at 15:00
  • Yes the answer that I added is after executing the command on mysql cli. Make sure you login as `root` to `mysql` before applying the above commands. – Abhik Chakraborty Apr 25 '15 at 15:02
  • with mysql -u root -p I can login using initial root password which I found using sudo cat /var/root/.mysql_secret but when I try to run use mysql then it gives me following error: ERROR 1820 (HY000): You must SET PASSWORD before executing this statement. – ddesai Apr 25 '15 at 15:13
  • In that case you need to just use `SET PASSWORD = PASSWORD('test');` it does not need the username and local host, the password that you have used is encrypted. – Abhik Chakraborty Apr 25 '15 at 15:19
  • Abhik- Thanks for your help but I think something is wrong with my SQL installation itself. when I use command like update user set Password = password('test') where host='localhost' and User='root'; it ask me to select database and when I run SET PASSWORD command it gives me weird error. Do you think I should try to re-install SQL server and can there be any compatibility issue? Please let me know and thanks for your reply and help. – ddesai Apr 25 '15 at 15:20
  • mysql> SET PASSWORD = PASSWORD('test'); 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('test')' at line 1 - Same error – ddesai Apr 25 '15 at 15:21
  • Also, I just noticed that i cannot stop my SQL server via system preferences or command line. What I mean is it will start itself up again in few seconds which is really weird. So basically its like restarting itself when I hit Stop MySQL server button. – ddesai Apr 25 '15 at 15:24
  • try restarting mysql once `sudo /etc/init.d/mysql restart` and see if its fixes the issue. However while doing the installation it will ask you to set the password for root which you can provide or leave it. You can change the root password when you need. Not sure why you are having this issue.Here are some articles http://mysqlyogi.blogspot.in/2013/03/error-1820-hy000-you-must-set-password.html http://technologyrnd.blogspot.in/2013/04/error-1820-hy000-you-must-set-password.html – Abhik Chakraborty Apr 25 '15 at 15:24
  • Here another error when I try to restart: sudo /usr/local/mysql/support-files/mysql.server restart Shutting down MySQL .... ERROR! The server quit without updating PID file (/usr/local/mysql/data/devangdesai.home.pid). ERROR! Failed to stop running server, so refusing to try to start. – ddesai Apr 25 '15 at 15:31
  • Also when I do cd /usr/local/mysql/data it says -bash: cd: /usr/local/mysql/data: Permission denied – ddesai Apr 25 '15 at 15:32
  • looks like some serious issues, i am running out of words. Usually for root related files you need to use `sudo` for access. – Abhik Chakraborty Apr 25 '15 at 15:35
  • haha true. Thanks for all your help. Do you have any link which will walk me through detail steps of installing SQL Server on MAC along which what versions are compatible with Yosemite 10.10.3 (14D136). – ddesai Apr 25 '15 at 15:39
  • Here is one such from SO http://stackoverflow.com/questions/25954230/mysql-does-not-start-in-osx-yosemite-10-10 – Abhik Chakraborty Apr 25 '15 at 15:43