Passwords incorrectly expiring after MySQL5.7 -> MariaDB10.3 -> 10.4+ upgrades.

Filing this per the discussion in zulip chat (https://mariadb.zulipchat.com/#narrow/stream/118759-general/topic/Password.20issues.20after.205.2E7.20-.3E.2010.2E3.20-.3E.2010.2E5.20upgrade.20path)

Password expiration existed in MySQL 5.7, but not in MariaDB 10.3.
On servers that upgraded from 5.7 to 10.3, there are leftover "cruft" fields in mysql.user relating to password expiry that do not get used.
When creating users on a 10.3 system, the "password_last_changed" field is initialized as 0000-00-00 00:00:00

However, password expiration was added in 10.4 – and on such systems, users who had 0000-00-00 00:00:00 as their password_last_changed get their passwords expired – even if password expiration is not enabled.

I've attached a sample affected 10.3 datadir to this case.

The basic premise is to start on MySQL 5.7, upgrade to 10.3, create a user, then upgrade to 10.4+.

However, I have a sample affected datadir ready.

1. Extract the contents of maria_103_datadir.tar.xz to your datadir.

tar -xf maria_103_datadir.tar.xz

2. Start up MariaDB 10.4+

3. Run mysql_upgrade

4. Try to authenticate as the sql103 user, observe error:

[root@10-1-33-101 ~]# mysql -u sql103 -p'TestPassPleaseIgnore' -e 'SHOW DATABASES;'

ERROR 1820 (HY000) at line 1: You must SET PASSWORD before executing this statement

Just for additional info, here is the global priv entry for the user post-upgrade:

[root@10-1-33-101 ~]# mysql -BNe 'SELECT Priv from mysql.global_priv WHERE User="sql103" AND Host="localhost";' | python -m json.tool

{

    "access": 0,

    "account_locked": false,

    "authentication_string": "*CA8C7DC8B54EDC492104531EA6612FC243D816C3",

    "default_role": "",

    "is_role": false,

    "max_connections": 0,

    "max_questions": 0,

    "max_statement_time": 0.0,

    "max_updates": 0,

    "max_user_connections": 0,

    "password_last_changed": 0,

    "password_lifetime": -1,

    "plugin": "mysql_native_password",

    "ssl_cipher": "",

    "ssl_type": 0,

    "x509_issuer": "",

    "x509_subject": ""

}

In this specific use case (where password expiration was not configured to occur), I would not expect the user's password to expire after upgrading to MariaDB 10.4+.

Edit: a better workaround fix:

mysql> update mysql.global_priv set Priv=JSON_SET(Priv, '$.password_last_changed', UNIX_TIMESTAMP()) WHERE JSON_VALUE(Priv, '$.password_last_changed') = '0';

Query OK, 25 rows affected (0.01 sec)

Rows matched: 25  Changed: 25  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)