2

While trying to change mysql password by following steps given in this solution, i got the following error- 

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('letsrock') WHERE User = 'root' AND Host = 'localhost';
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 '('letsrock') WHERE User = 'root' AND Host = 'localhost'' at line 1

Is there a new syntax for update command in mysql or what? I am using 8.0.12 version.

monster
  • 808
  • 10
  • 23

1 Answers1

0

Both 'user' and 'host' are reserved words and cannot be used as identifiers without enclosing them in backticks. Like so:

UPDATE mysql.user 
SET authentication_string = PASSWORD('letsrock') 
WHERE `User` = 'root' AND `Host` = 'localhost';

See: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

KIKO Software
  • 15,283
  • 3
  • 18
  • 33
  • _Reserved words_, to be specific. – jarlh Aug 09 '18 at 12:09
  • Ah, yes, of course. It would be better to have a (K) and (W) behind each word instead of only an (R) behind 'Reserved Keywords', because 'Reserved Words' also starts with an 'R'.... I changed the answer. – KIKO Software Aug 09 '18 at 12:11
  • That didn't work for me despite me copying the code while only replacing the string in `PASSWORD('mystring'). It still throws me` ERROR 1064 (42000): ...` any further ideas? – ilam engl May 17 '22 at 15:03
  • @KIKOSoftware This did it for me in the end: `UPDATE mysql.user SET authentication_string='letsrock' WHERE User='root';` so, without the `PASSWORD()` credits: https://stackoverflow.com/questions/51396981/error-on-trying-to-change-password-in-mysql/59059722#59059722 – ilam engl May 17 '22 at 15:08