1

I am unable reset mysql password using below query:

UPDATE mysql.user 
SET authentication_string=PASSWORD('root') 
WHERE user='root' 
  and host='localhost';

i am getting below 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 '('root') WHERE User = 'root' AND Host = 'localhost'' at line 1

Akina
  • 39,301
  • 5
  • 14
  • 25
robinder singh
  • 27
  • 4
  • 1
    Use [`SET PASSWORD`](https://dev.mysql.com/doc/refman/8.0/en/set-password.html), not this manual hackery. – tadman Feb 23 '20 at 19:53
  • 3
    `PASSWORD` function was deprecated in MySQL 5.7, removed in MySQL 8.0 – spencer7593 Feb 23 '20 at 19:54
  • @spencer7593 SET PASSWORD statement and PASSWORD() function are slightly different things... – Akina Feb 23 '20 at 19:59
  • 1
    @Akina I think Spencer is pointing out that the original query fails because `PASSWORD()` is toast. That's where the syntax error is coming from. – tadman Feb 23 '20 at 20:35
  • 1
    @tadman This is possible. But, unfortunately, his comment is too ambiguous. – Akina Feb 23 '20 at 20:37
  • @Akina It could be made more clear, but I think the point stands. – tadman Feb 23 '20 at 20:37
  • @tadman *the original query fails because PASSWORD() is toast. That's where the syntax error is coming from.* This is a mistake. Replace `PASSWORD` with any really non-existent function - and look like the error message will be changed. The function is deprecated/removed and the function name is not parsed - this is not the same. – Akina Feb 23 '20 at 20:43
  • @Akina Maybe I'm reading this all wrong. If you've got an alternate answer it's worth posting. – tadman Feb 23 '20 at 20:52
  • @tadman The source of the problem is identified correctly. But what is wrong is what led to the error. I think it is not interesting to OP, it makes sense for specialists only. – Akina Feb 23 '20 at 21:02
  • @Akina: Yes, the PASSWORD() function and the SET PASSWORD statement are two different things. OP didn't ask a question; OP just gave a status report. Looks to me like the behavior reported by OP is the behavior we would expect running that statement in MySQL 8.0, which differs from the behavior we would expect in MySQL 5.7. If the question from OP is "how do I get this statement to not error", then one possible answer is to execute the statement in a MySQL 5.7 instance. There's ambiguity here, but its not found in my previous comment. – spencer7593 Feb 24 '20 at 04:04
  • @Akina: try google search "PASSWORD function was deprecated in MySQL 5.7, removed in MySQL 8.0" Based on the results returned by Google, there doesn't appear to be much ambiguity. – spencer7593 Feb 24 '20 at 04:13
  • @spencer7593 You don't understand me. I tell that server message reports that PASSWORD keyword context is wrong, not that the PASSWORD() function not exists. I.e. PASSWORD keyword is parsed before syntax checking. – Akina Feb 24 '20 at 07:31
  • @Akina: Again, I didn't see that OP asked an actual question. OP gave a status report, OP is unable to 'X', OP tried 'Y' and OP observed behavior 'Z'. And, again, it looks to me like OP is trying to execute a MySQL statement that exhibits different behavior under MySQL 5.x and MySQL 8.0. And the primary reason for the difference in behavior is that the PASSWORD function was *removed* in MySQL 8.0. And again, I don't see that the comment I provided is at all ambiguous. – spencer7593 Feb 24 '20 at 17:26

1 Answers1

1

As @spencer7593 points out, the PASSWORD() function was removed in MySQL 8.0 so this code won't work any longer even if it did in previous versions. The syntax error arises because, not recognizing that as a function, the ( character following it is unexpected and a syntax error.

This is because in addition to (previously) being a function it still a keyword.

The way you should be adjusting passwords is either through the mysqladmin command-line tool, or via the SET PASSWORD statement:

SET PASSWORD FOR 'root' = '...'
tadman
  • 208,517
  • 23
  • 234
  • 262
  • 1
    +10 we can use `SET PASSWORD` statement to change password. Most likely explanation for observed behavior is OP is executing statement in MySQL 8.0 and the `PASSWORD()` function is removed in MySQL 8.0. (If OP question is "what SQL statement can I use in MySQL 8.0 to change password", then this answer fits the bill.) – spencer7593 Feb 26 '20 at 19:49
  • 1
    According to https://stackoverflow.com/questions/6474775/setting-the-mysql-root-user-password-on-os-x/6474890#6474890 you should use `mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';` for mysql version 8 and higer... – ilam engl May 18 '22 at 13:45