MYSQL refuses to set password for new user

Question

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.

• ypercubeᵀᴹ: Which version of MySQL?

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

• Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
• Aug 07, 2013 : MySQL - ERROR 1045 (28000): Access denied for user : permission issue
• May 24, 2013 : How often should MySQL (stock, Percona, etc.) be upgraded?
• May 01, 2013 : How often should MySQL (stock, Percona, etc.) be upgraded?
• Apr 12, 2012 : Cannot GRANT privileges as root (I actually show the differences in the column layouts in MySQL 5.6, 5.5, 5.1, 5.0, 4.x)

Now for the shocker

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

• There are 45 columns
• There is no longer any column named password
• The column authentication_string stores the password now
• I mentioned this Oct 28, 2015 : Install MySQL for Windows from .zip and reset root password

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>