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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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');
Method 2
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.
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.
Method 3
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';
Method 4
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.
Method 5
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0