2

Under MySql 5.7.17 the mentioned instruction do not work and always I get no feedback or the following error message:

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 'UPDATE mysql.user SET password=password("elephant7") where user="root"' at line ...

I tried the following UPDATE on the command line:

UPDATE mysql.user SET Password = PASSWORD('elephant7') WHERE User='root';

I really don't see anymore my mistake. I also tried without ;.

2
  • 4
    Where is your code for this command? Commented Dec 19, 2016 at 8:28
  • 1
    Your command (as specified in Question heading) is working on my machine...Please post your command for which you are receiving this error Commented Dec 19, 2016 at 8:38

2 Answers 2

6

It's not recommended to change the password in this way using UPDATE directly on the mysql.user table. You should use SET PASSWORD instead:

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

More information on MySQL: Assigning Account Passwords

Your UPDATE command perhaps doesn't work because the password column get replaced by authentication_string on MySQL 5.7.6.

The authentication_string column in the mysql.user table now stores credential information for all accounts. The password column, previously used to store password hash values for accounts authenticated with the mysql_native_password and mysql_old_password plugins, is removed.

In case you directly change the grant tables you also have to reload the tables by using the FLUSH PRIVILEGES statement:

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE (which is not recommended), the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it. Thus, if you change the grant tables directly but forget to reload them, the changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement.

source: When Privilege Changes Take Effect

So your UPDATE command to directly change the password on the grant tables have to look like this, using the correct column and the FLUSH PRIVILEGES statement:

UPDATE mysql.user SET authentication_string = PASSWORD('elephant7') WHERE User = 'root';
FLUSH PRIVILEGES;
Sign up to request clarification or add additional context in comments.

3 Comments

I agree but I think the OP is asking what is the issue with his command even though the syntax appears to be correct
Yes, I update my answer if I see a mistake on his query. but at the moment it is correct for me so I give an alternative to try and more information about this topic.
it works now perfect and I can go ahead. Thanks guys for the great support
2

Changing the password using this method requires a flush privileges for it to take effect.

flush privileges;

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.