Password expired for all user accounts

[Rein_vtv]

Hey guys, i’ve just done an upgrade from (percona) 5.6 to 5.7 and ran into troubles. Not regarding the upgrade, but regarding password policy of mysql 5.7 :-(.

All accounts on my test server have been marked as expired (about 50 accounts) and therefor websites are unable to use the database server. Does anyone know how I can upgrade without having all accounts expired? Funny enough none of the usernames have a Y at the password_expired field in mysql.user …

Any insights on the matter are greatly appreciated

[jrivera]

Did you upgrade from 5.6 to any version between 5.7.4 to 5.7.10? Based on the manual:

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 thedefault_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.

However, this is easy to miss for clients that automatically connect to the server, such as connections made from scripts. To avoid having such clients suddenly stop working due to a password expiring, make sure to change the password expiration settings for those clients, like this:
ALTER USER ‘script’@‘localhost’ PASSWORD EXPIRE NEVER
Alternatively, set the default_password_lifetime variable to 0, thus disabling automatic password expiration for all users.

[Rein_vtv]

Well, that’s just it. I’ve ran into this problem when testing some months ago, and reverted back to 5.6. Now I went from 5.6 to the latest 5.7.17-11 and expected not to run into it. I did run mysql_upgrade afterwards, but I don’t think that’s a bad thing?

[jrivera]

Upgrading major versions like in your case from 5.6 to 5.7, running mysql_upgrade is recommended if not required

Just make sure to check all variables pertaining to password expiry to NOT expire

[Rein_vtv]

SHOW VARIABLES LIKE “default_pass%”;
±--------------------------±------+
| Variable_name | Value |
±--------------------------±------+
| default_password_lifetime | 0 |
±--------------------------±------+
1 row in set (0.00 sec)

but it passwords are still marked as expired.
Is there a query I can run to unexpire them?

[jrivera]

Run ALTER USER ‘username’ PASSWORD EXPIRE NEVER