14

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?

2
  • try to start mysql under safe mode. then you can change the root password
    – AReddy
    Jun 22, 2016 at 8:02
  • To anyone reading this thread. MySQL 5.7 has substantial changes under the hood. Please read about them before upgrading. Jun 22, 2016 at 8:42

5 Answers 5

25

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');
19
  • 1
    +1 for the authentication_string reference. I also recently find out about it after upgrading to 5.7 Jun 22, 2016 at 8:22
  • 5
    While it is not directed entirely to this answer, another alternative for 5.7 is SET PASSWORD FOR 'root' = PASSWORD('new_password'); Jun 22, 2016 at 8:38
  • 1
    @RuiFRibeiro thanks, updated my answer
    – Rahul
    Jun 22, 2016 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. Jun 22, 2016 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. Jun 22, 2016 at 13:00
3

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.

Update: SET PASSWORD ... = PASSWORD('auth_string') syntax is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

1

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

2
  • 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. Jun 22, 2016 at 8:22
  • 1
    Thanks Ribeiro. I missed authentication_string
    – AReddy
    Jun 22, 2016 at 8:32
0

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.

-1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .