diff options
| -rw-r--r-- | mysql-test/main/mysql_upgrade.result | 72 | ||||
| -rw-r--r-- | mysql-test/main/mysql_upgrade.test | 38 | ||||
| -rw-r--r-- | scripts/mysql_system_tables_fix.sql | 2 |
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, |