2

I'm trying to create a MySQL user/password. And every time I create the user - mysql does not honor my password. It's just blank.

mysql> select Host, User, password_expired, Password from mysql.user;
+-----------+------------------+------------------+-------------------------    ------------------+
| Host      | User             | password_expired | Password                                      |
+-----------+------------------+------------------+-------------------------    ------------------+
| localhost | root             | N                | *AE446F256B93CD6D2BFE2D9DB06206920F3D3846 |
| localhost | work             | N                | *307FC2B1D9DC5939A75036B880097501D3D4AA05 |
| localhost | project          | N                | *9FB0DFB069CEF4E90F11D272FEE775130958E261 |
| localhost | other            | N                | *C5E7D39FE5C6A9A5B59579F693CF91094BB3321E |
+-----------+------------------+------------------+-------------------------------------------+

Then when I create a new user, it tells me that all was successful.

mysql> create user 'testing'@'localhost' identified by 'password';
    Query OK, 0 rows affected (0.00 sec)
mysql> select Host, User, password_expired, Password from mysql.user;
+-----------+------------------+------------------+-------------------------------------------+
| Host      | User             | password_expired | Password                                      |
+-----------+------------------+------------------+-------------------------------------------+
| localhost | root             | N                | *AE446F256B93CD6D2BFE2D9DB06206920F3D3846 |
| localhost | work             | N                | *307FC2B1D9DC5939A75036B880097501D3D4AA05 |
| localhost | project          | N                | *9FB0DFB069CEF4E90F11D272FEE775130958E261 |
| localhost | other            | N                | *C5E7D39FE5C6A9A5B59579F693CF91094BB3321E |
| localhost | testing          | N                |                                           |
+-----------+------------------+------------------+-------------------------------------------+
    5 rows in set (0.00 sec)

As you can see, there is no password. I've tried dropping, flushing privileges, and re-adding.

Here are the grant permissions for root.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
    2 rows in set (0.00 sec)

This is pretty frustrating, and any help would be greatly appreciated.

1

I am going to take a very wild guess on this one

My guess is that you upgraded to MySQL 5.7 but did not upgrade the the mysql schema.

I wrote about the mysql.user table having a different number of columns per version:

Now for the shocker

Did you know the following about mysql.user in MySQL 5.7 ?

At this point, you are probably asking : Why in the world did create user 'testing'@'localhost' identified by 'password'; work ?

Look in my Apr 12, 2012 post. Please note the the column layout for mysql.user. The column authentication_string column appears in MySQL 5.6 and MySQL 5.5.

Please run this query

select Host, User, password_expired,Password,authentication_string from mysql.user;

You should see the encrypted password in the authentication_string column

What makes me believe this to be the case was the clue you provided

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

The command SHOW GRANTS; no longer shows the encrypted password in MySQL 5.7. I ran in to this issue even with pt-show-grants.

What should you do ?

Perhaps you should run mysqld --upgrade to fix the column layout. It looks like this in 5.7:

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | 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)

mysql>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.