Can't change user password on MySQL using UPDATE

Question

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 ;.

Answer 1

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;

Answer 2

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

flush privileges;