macos – MySQL user DB does not have password columns – Installing MySQL on OSX
macos – MySQL user DB does not have password columns – Installing MySQL on OSX
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 lets 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
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.
macos – MySQL user DB does not have password columns – Installing MySQL on OSX
One pitfall I fell into is there is no password field now, it has been renamed so:
update user set password=PASSWORD(YOURPASSWORDHERE) where user=root;
Should now be:
update user set authentication_string=password(YOURPASSWORDHERE) where user=root;