MySQL user DB does not have password columns – Installing MySQL on OSX

by

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

More Related Contents:

  1. Setting the MySQL root user password on OS X
  2. mysql root password forgotten
  3. How to reset the root password in MySQL 8.0.11?
  4. What data type to use for hashed password field and what length?
  5. Location of my.cnf file on macOS
  6. brew install mysql on macOS
  7. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  8. MySQL – ERROR 1045 – Access denied
  9. How do you uninstall MySQL from Mac OS X?
  10. Restoring deleted ‘root’ user and password for MySQL
  11. Ruby on Rails 3 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ on OSX
  12. MySQL root password change
  13. Resetting ROOT password in MySQL 5.6
  14. Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT
  15. MySQL Install: ERROR: Failed to build gem native extension
  16. MySQL password function
  17. libmysqlclient15-dev on macs?
  18. rails + MySQL on OSX: Library not loaded: libmysqlclient.18.dylib
  19. phpMyAdmin – can’t connect – invalid setings – ever since I added a root password – locked out
  20. Connect to Docker MySQL container from localhost?
  21. How to change the mysql root password
  22. How do you stop MySQL on a Mac OS install?
  23. Reset MySQL root password using ALTER USER statement after install on Mac
  24. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ [duplicate]
  25. PID error on mysql.server start?
  26. How to start MySQL server from command line on Mac OS Lion?
  27. How to auto-load MySQL on startup on OS X Yosemite / El Capitan
  28. OSX ld: library not found for -lssl
  29. How to get back Lost phpMyAdmin Password, XAMPP
  30. MySQL data directory location

Leave a Comment