Current Environment :

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.13                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.13                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

Password Change command user :

mysql> update user set password=PASSWORD("XXXX") where user="root";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

Am I missing something?

  • try to start mysql under safe mode. then you can change the root password – Mongrel Jun 22 '16 at 8:02
  • To anyone reading this thread. MySQL 5.7 has substantial changes under the hood. Please read about them before upgrading. – Rui F Ribeiro Jun 22 '16 at 8:42
up vote 19 down vote accepted

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is authentication_string.

First choose the database:

mysql> use mysql;

And then show the tables:

mysql> show tables;

You will find the user table, and see its fields:

mysql> describe user;

You will realize there is no field named password, the password field is named authentication_string. So, just do this:

update user set authentication_string=password('XXXX') where user='root';

As suggested by @Rui F Ribeiro, alternatively you can run:

mysql> SET PASSWORD FOR 'root' = PASSWORD('new_password');
  • 1
    +1 for the authentication_string reference. I also recently find out about it after upgrading to 5.7 – Rui F Ribeiro Jun 22 '16 at 8:22
  • 3
    While it is not directed entirely to this answer, another alternative for 5.7 is SET PASSWORD FOR 'root' = PASSWORD('new_password'); – Rui F Ribeiro Jun 22 '16 at 8:38
  • 1
    @RuiFRibeiro thanks, updated my answer – Rahul Jun 22 '16 at 12:24
  • 1
    Rahul Initially I edited the thread to later than 5.7, but then changed it to using MySQL 5.7, to keep more to the spirit of the question. e.g. @Shivu did not know it is a change in 5.7. – Rui F Ribeiro Jun 22 '16 at 12:57
  • 1
    It is a way of framing the question. We are not editing questions and answers only for the benefit of the OP, but also for others later on. – Rui F Ribeiro Jun 22 '16 at 13:00

mysqladmin -u user-name password -p "oldpassword" "newpass"

if you can login then try this "" wont work try '' single quote

update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

  • While I do prefer the mysqladmin answer, see Rahul´s answer. Your SQL query is outdated. I corrected the mysqladmin, you were missing the password directive. – Rui F Ribeiro Jun 22 '16 at 8:22
  • 1
    Thanks Ribeiro. I missed authentication_string – Mongrel Jun 22 '16 at 8:32

The MySQL way of changing password is SET PASSWORD

SET PASSWORD FOR 'root' = PASSWORD('new_password');

see MySQL 5.7 Reference Manual / ... / SET PASSWORD Syntax

The SET PASSWORD statement assigns a password to a MySQL user account, specified as either a cleartext (unencrypted) or encrypted value:

'auth_string' represents a cleartext password.

'hash_string' represents an encrypted password.

The accepted answer from Rahul shows how to update password with DML statement.

update user set authentication_string=password('XXXX') where user='root';

Warning: that's not the official and supported way. It can cause troubles, if you don't know what you are doing. Don't forget FLUSH PRIVILEGES.

For most operations, like creating a user, changing its privileges, or changing its password, you will want to use the high-level statements. Not only they are easier to use and they are compatible with a larger number of MySQL versions, but they will also prevent you from making mistakes (of course, remember to setup the “NO_AUTO_CREATE_USER“ sql mode). They even usually work nicely in a MyISAM-hostile environment like a Galera cluster.

Stop using FLUSH PRIVILEGES

Please use GRANT, REVOKE, SET PASSWORD, or RENAME USER and not direct DML statements.

In my case

mysql.server start
$ mysql -uroot
mysql> update user set authentication_string=password('123456') where User='root';
mysql> exit;
mysql.server restart (if you not restart , connection will not work.)

So I think, your update command is right, but you need to restart your mysql server.

For this problem, I used a simple and rude method, rename the field name to password, the reason for this is that I use the mac navicat premium software in the visual operation error: Unknown column 'password' in 'field List ', the software itself uses password so that I can not easily operate. Therefore, I root into the database command line, run

Use mysql;

And then modify the field name:

ALTER TABLE user CHANGE authentication_string password text;

After all normal.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.