Password expired for all user accounts

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

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

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?

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

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

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?

Run ALTER USER ‘username’ PASSWORD EXPIRE NEVER