MySQL user DB does not have password columns - Ins

Question

I am trying to change MySql root password.

What I have done is below.

1. Install MySql-5.7.6 ~ .dmg(Community Server) and workbench.
2. Turn off the server on OSX System preferences.
3. Access MySql with console. The command was mysqld_safe --skip-grant
4. Execute update user set password=password('1111') where user='root'; and got an error message --> ERROR 1054 (42S22): Unknown column 'password' in 'field list'.

FYI, I did use mysql;. So I did select query on user table and found password column actually does not exist.

It is very weird. Is it possible that original user table does not have password column?

How can I change password, which does not exist?

Thanks for your answer :D

Answer 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, now let's see its fields:

mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

Surprise！There is no field named 'password', the password field is named ' authentication_string'. So, just do this:

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

Now, everything will be ok.

Compared to MySQL 5.6, the changes are quite extensive: What’s New in MySQL 5.7

Answer 2

Thank you for your help. Just in case if people are still having problems, try this.

For MySQL version 5.6 and under

Have you forgotten your Mac OS X 'ROOT' password  and need to reset it?  Follow these 4 simple steps:

1.  Stop the mysqld server.  Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
2.  Start the server in safe mode with privilege bypass      From a terminal:      sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3.  In a new terminal window:      sudo /usr/local/mysql/bin/mysql -u root      UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';      FLUSH PRIVILEGES;      \q
4.  Stop the mysqld server again and restart it in normal mode.

For MySQL version 5.7 and up

1.  Stop the mysqld server.  Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
2.  Start the server in safe mode with privilege bypass      From a terminal:      sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3.  In a new terminal window:            sudo /usr/local/mysql/bin/mysql -u root      UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root';      FLUSH PRIVILEGES;      \q      
4.  Stop the mysqld server again and restart it in normal mode.

Answer 3

remember password needs to be set further even after restarting mysql as below

SET PASSWORD = PASSWORD('root');

Answer 4

It only worked with me when I "flushed" after the commands mentioned here. Here's the full list of commands I used:

Previous answers might not work for later mysql versions. Try these steps if previous answers did not work for you:

1- Click on the wamp icon > mysql > mysql console

2- write following commands, one by one

use mysql;
update user set authentication_string=password('your_password') where user='root';
FLUSH PRIVILEGES;
quit

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

Answer 6

This error happens if you did not set the password on install, in this case the mysql using unix-socket plugin.

But if delete the plugin link from settings (table mysql.user) will other problem. This does not fix the problem and creates another problem. To fix the deleted link and set password ("PWD") do:

1) Run with --skip-grant-tables as said above.

If it doesnt works then add the string skip-grant-tables in section [mysqld] of /etc/mysql/mysql.conf.d/mysqld.cnf. Then do sudo service mysql restart.

2) Run mysql -u root -p, then (change "PWD"):

update mysql.user 
    set authentication_string=PASSWORD("PWD"), plugin="mysql_native_password" 
    where User='root' and Host='localhost';    
flush privileges;

quit

then sudo service mysql restart. Check: mysql -u root -p.

Before restart remove that string from file mysqld.cnf, if you set it there.