MySQL user is not expired but asking for Password RESET

I am getting the below error for the user root@'%', but when I login through root@'localhost',I can see the password_expired = 'N' for root@'%'

Server version: 5.7.9-log MySQL Community Server (GPL)


ERROR 1820 (HY000): You must reset your password using ALTER USER
statement before executing this statement.

+------+------+------------------+----------------+-------------------+-----------------------+
| user | host | password_expired | account_locked | password_lifetime | password_last_changed |
+------+------+------------------+----------------+-------------------+-----------------------+
| root | %    | N                | N              |              NULL | 2015-12-16 04:50:23   |
+------+------+------------------+----------------+-------------------+-----------------------+

The password is expired -- implicitly -- after 360 days -- in MySQL Server 5.7 versions prior to 5.7.11.


From MySQL 5.7.4 to 5.7.10, the default default_password_lifetime value is 360 (passwords must be changed approximately once per year). For those versions, be aware that, if you make no changes to the default_password_lifetime variable or to individual user accounts, all user passwords will expire after 360 days, and all user accounts will start running in restricted mode when this happens. Clients (which are effectively users) connecting to the server will then get an error indicating that the password must be changed: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.


https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

Temporary workaround should be this:

mysql> SET GLOBAL DEFAULT_PASSWORD_LIFETIME = 0;

Better, add to the mysqld section of my.cnf:

default_password_lifetime = 0

...or another appropriate value for your site, then restart the server.

Even better still... upgrade. There were numerous bugs in early GA versions of 5.7, particularly 5.7.9, which was the first GA version of the release series.

Of course, you should be able to modify this account using ALTER USER ... PASSWORD EXPIRE NEVER which is apparently not the same as the NULL value shown in the table.

StackExchange Database Administrators Q#157873, answer score: 2