Description:
password expire and password expire interval when create user is not both work.
How to repeat:
drop user test;create user test;
select user,password_expired,password_last_changed,password_lifetime from mysql.user where user='test';
+------+------------------+-----------------------+-------------------+
| user | password_expired | password_last_changed | password_lifetime |
+------+------------------+-----------------------+-------------------+
| test | N | 2025-05-29 10:31:27 | NULL |
+------+------------------+-----------------------+-------------------+
-- result is correct.
drop user test;create user test password expire;
select user,password_expired,password_last_changed,password_lifetime from mysql.user where user='test';
+------+------------------+-----------------------+-------------------+
| user | password_expired | password_last_changed | password_lifetime |
+------+------------------+-----------------------+-------------------+
| test | Y | 2025-05-29 10:32:47 | NULL |
+------+------------------+-----------------------+-------------------+
-- result is correct.
drop user test;create user test password expire interval 30 day;
select user,password_expired,password_last_changed,password_lifetime from mysql.user where user='test';
+------+------------------+-----------------------+-------------------+
| user | password_expired | password_last_changed | password_lifetime |
+------+------------------+-----------------------+-------------------+
| test | N | 2025-05-29 10:33:19 | 30 |
+------+------------------+-----------------------+-------------------+
-- result is correct.
drop user test;create user test password expire password expire interval 30 day;
select user,password_expired,password_last_changed,password_lifetime from mysql.user where user='test';
+------+------------------+-----------------------+-------------------+
| user | password_expired | password_last_changed | password_lifetime |
+------+------------------+-----------------------+-------------------+
| test | N | 2025-05-29 10:34:32 | 30 |
+------+------------------+-----------------------+-------------------+
-- result is not correct.password expire is not working, we need another alter user to make it work.
drop user test;create user test password expire interval 30 day password expire;
select user,password_expired,password_last_changed,password_lifetime from mysql.user where user='test';
+------+------------------+-----------------------+-------------------+
| user | password_expired | password_last_changed | password_lifetime |
+------+------------------+-----------------------+-------------------+
| test | Y | 2025-05-29 10:37:00 | NULL |
+------+------------------+-----------------------+-------------------+
-- result is not correct.password expire interval is not working, we need another alter user to make it work.
Suggested fix:
we can use password expire and password expire interval simultaneously when create user.
