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

1 Answer 1

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>
0

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .