MDEV-26363 Passwords incorrectly expiring after MySQL5.7 -> MariaDB10.3 -> 10.4+ upgradesbb-10.4-MDEV-26363-danielblack-zero_last_password_changed

MySQL-5.7 mysql.user tables have a last_password_changed field.

Because before MariaDB-10.4 remained oblivious to this, the act of creating
users or otherwise changing a users row left the last_password_field with 0.

Running a MariaDB-10.4 instance on this would work correctly, until mysql_upgrade
is run, when this 0 value immediately translates to password expired
state.

MySQL-5.7 relied on the password_expired enum to indicate password
expiry so we aren't going to activate password that were expired in
MySQL-5.7.

Thanks Hans Borresen for the bug report and review of the fix.

3 files changed, 111 insertions, 1 deletions

diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result
index af3688dca6b..c4c0af1601c 100644
--- a/mysql-test/main/mysql_upgrade.result
+++ b/mysql-test/main/mysql_upgrade.result
@@ -842,4 +842,76 @@ count(*)
 5
 drop table mysql.global_priv;
 rename table mysql.global_priv_bak to mysql.global_priv;
+#
+# Ensure that mysql_upgrade accounted for 0 password_last_changed
+# and doesn't PASSWORD EXPIRE a user account because < 10.4 zeroed it.
+#
+# switching from mysql.global_priv to mysql.user
+drop view mysql.user_bak;
+drop table mysql.user;
+truncate table mysql.tables_priv;
+FLUSH TABLES mysql.user;
+FLUSH PRIVILEGES;
+CREATE USER mariadb_102;
+UPDATE mysql.user SET password_last_changed=0 WHERE user='mariadb_102';
+FLUSH PRIVILEGES;
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.global_priv_bak                              OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats                           OK
+mysql.innodb_table_stats                           OK
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.transaction_registry                         OK
+mysql.user                                         OK
+Upgrading from a version before MariaDB-10.1
+Phase 2/7: Installing used storage engines
+Checking for tables with unknown storage engine
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+SHOW CREATE USER mariadb_102;
+CREATE USER for mariadb_102@%
+CREATE USER `mariadb_102`@`%`
+connect con1,localhost,mariadb_102;
+select current_user();
+current_user()
+mariadb_102@%
+disconnect con1;
+connection default;
+drop table mysql.global_priv;
+rename table mysql.global_priv_bak to mysql.global_priv;
 # End of 10.4 tests
diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test
index 7d673d2bb4d..afc06dfc5ee 100644
--- a/mysql-test/main/mysql_upgrade.test
+++ b/mysql-test/main/mysql_upgrade.test
@@ -356,4 +356,42 @@ select count(*) from mysql.global_priv;
 drop table mysql.global_priv;
 rename table mysql.global_priv_bak to mysql.global_priv;
 
+#
+# MDEV-26363 Former mysql-5.7 tables have password_last_changed to 0
+#            on MariaDB updates, resulting in mysql_upgrade leaving them
+#            with password expired.
+#
+
+--echo #
+--echo # Ensure that mysql_upgrade accounted for 0 password_last_changed
+--echo # and doesn't PASSWORD EXPIRE a user account because < 10.4 zeroed it.
+--echo #
+
+--source include/switch_to_mysql_user.inc
+drop view mysql.user_bak;
+drop table mysql.user;
+truncate table mysql.tables_priv;
+--copy_file std_data/mysql57user.frm $MYSQLD_DATADIR/mysql/user.frm
+--copy_file std_data/mysql57user.MYI $MYSQLD_DATADIR/mysql/user.MYI
+--copy_file std_data/mysql57user.MYD $MYSQLD_DATADIR/mysql/user.MYD
+FLUSH TABLES mysql.user;
+FLUSH PRIVILEGES;
+
+CREATE USER mariadb_102;
+# manually set the value like <10.4 previously did for testing mysql_upgrade.
+UPDATE mysql.user SET password_last_changed=0 WHERE user='mariadb_102';
+FLUSH PRIVILEGES;
+
+--exec $MYSQL_UPGRADE --force 2>&1
+# Should not have "PASSWORD EXPIRED"
+SHOW CREATE USER mariadb_102;
+connect con1,localhost,mariadb_102;
+select current_user();
+disconnect con1;
+connection default;
+
+drop table mysql.global_priv;
+rename table mysql.global_priv_bak to mysql.global_priv;
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
 --echo # End of 10.4 tests
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index 9dd775aaf30..ec3d532ec11 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -815,7 +815,7 @@ IF 'BASE TABLE' = (select table_type from information_schema.tables where table_
                     'max_statement_time', max_statement_time,
                     'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_old_password','mysql_native_password')),
                     'authentication_string', if(plugin>'' and authentication_string>'',authentication_string,password),
-                    'password_last_changed', if(password_expired='Y', 0, UNIX_TIMESTAMP(password_last_changed)),
+                    'password_last_changed', if(password_expired='Y', 0, if(password_last_changed, UNIX_TIMESTAMP(password_last_changed), UNIX_TIMESTAMP())),
                     'password_lifetime', ifnull(password_lifetime, -1),
                     'account_locked', 'Y'=account_locked,
                     'default_role', default_role,