diff options
Diffstat (limited to 'mysql-test/main/mysql_upgrade.test')
| -rw-r--r-- | mysql-test/main/mysql_upgrade.test | 531 |
1 files changed, 531 insertions, 0 deletions
diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test new file mode 100644 index 00000000..9bc809d6 --- /dev/null +++ b/mysql-test/main/mysql_upgrade.test @@ -0,0 +1,531 @@ +-- source include/mysql_upgrade_preparation.inc +-- source include/have_working_dns.inc +-- source include/have_innodb.inc +-- source include/have_partition.inc +-- source include/no_valgrind_without_big.inc + +set sql_mode=""; + +call mtr.add_suppression("Incorrect definition of table mysql.column_stats:.*"); +# +# Basic test that we can run mysql_upgrde and that it finds the +# expected binaries it uses. +# +--echo Run mysql_upgrade once +--exec $MYSQL_UPGRADE --force 2>&1 + +# It should have created a file in the MySQL Servers datadir +let $MYSQLD_DATADIR= `select @@datadir`; +file_exists $MYSQLD_DATADIR/mysql_upgrade_info; + +--echo Run it again - should say already completed +--replace_regex /upgraded to [^\n].*/upgraded to VERSION./ /again for [^\n]*/again for VERSION./ +--exec $MYSQL_UPGRADE 2>&1 + +# It should have created a file in the MySQL Servers datadir +file_exists $MYSQLD_DATADIR/mysql_upgrade_info; + +--echo Force should run it regardless of whether it has been run before +--exec $MYSQL_UPGRADE --force 2>&1 + +# It should have created a file in the MySQL Servers datadir +file_exists $MYSQLD_DATADIR/mysql_upgrade_info; + + +# +# Bug #25452 mysql_upgrade access denied. +# + +# Password protect a root account and run mysql_upgrade + +CREATE USER mysqltest1@'%' IDENTIFIED by 'sakila'; +GRANT ALL ON *.* TO mysqltest1@'%'; +--echo Run mysql_upgrade with password protected account +--exec $MYSQL_UPGRADE --force --user=mysqltest1 --password=sakila 2>&1 + +DROP USER mysqltest1@'%'; + +# +# check that we get proper error messages if wrong user + +--error 1 +--exec $MYSQL_UPGRADE --force --user=mysqltest1 --password=sakila 2>&1 + +# +# Bug #26639 mysql_upgrade exits successfully even if external command failed +# + +--echo Run mysql_upgrade with a non existing server socket +--replace_result $MYSQLTEST_VARDIR var +--replace_regex /.*mariadb-check.*: Got/mariadb-check: Got/ /\([0-9|-]*\)/(errno)/ +--error 1 +# NC: Added --skip-version-check, as the version check would fail when +# mysql_upgrade tries to get the server version. +--exec $MYSQL_UPGRADE --verbose --force --host=not_existing_host --skip-version-check 2>&1 + +# +# Bug #28401 mysql_upgrade Failed with STRICT_ALL_TABLES, ANSI_QUOTES and NO_ZERO_DATE +# + +# The SQL commands used by mysql_upgrade are written to be run +# with sql_mode set to '' - thus the scripts should change sql_mode +# for the session to make sure the SQL is legal. + +# Test by setting sql_mode before running mysql_upgrade +set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; +--exec $MYSQL_UPGRADE --force 2>&1 +eval set GLOBAL sql_mode=default; + +--echo # +--echo # Bug #41569 mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table +--echo # but does not set values. +--echo # + +# Create a stored procedure and set the fields in question to null. +# When running mysql_upgrade, a warning should be written. + +CREATE PROCEDURE testproc() BEGIN END; +UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc'; +UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc'; +UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc'; +--exec $MYSQL_UPGRADE --force 2> $MYSQLTEST_VARDIR/tmp/41569.txt +CALL testproc(); +DROP PROCEDURE testproc; +--cat_file $MYSQLTEST_VARDIR/tmp/41569.txt +--remove_file $MYSQLTEST_VARDIR/tmp/41569.txt + + +--echo # +--echo # Bug #53613: mysql_upgrade incorrectly revokes +--echo # TRIGGER privilege on given table +--echo # + +GRANT USAGE ON *.* TO 'user3'@'%'; +GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'; +--echo Run mysql_upgrade with all privileges on a user +--exec $MYSQL_UPGRADE --force 2>&1 +SHOW GRANTS FOR 'user3'@'%'; + +DROP USER 'user3'@'%'; + +--echo # End of 5.1 tests + +# +# Test the --upgrade-system-tables option +# +--replace_result $MYSQLTEST_VARDIR var +--exec $MYSQL_UPGRADE --force --upgrade-system-tables + +--echo # +--echo # Bug#11827359 60223: MYSQL_UPGRADE PROBLEM WITH OPTION +--echo # SKIP-WRITE-BINLOG +--echo # + +let $MYSQLD_DATADIR= `select @@datadir`; + +--echo # Droping the previously created mysql_upgrade_info file.. +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo # Running mysql_upgrade with --skip-write-binlog.. +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ +--exec $MYSQL_UPGRADE --skip-write-binlog + +# mysql_upgrade must have created mysql_upgrade_info file, +# so the following command should never fail. +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo # +--echo # Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR +--echo # + +--echo Run mysql_upgrade with unauthorized access +--error 1 +--exec $MYSQL_UPGRADE --skip-verbose --user=root --password=wrong_password 2>&1 +--replace_regex /.*mariadb-check.*: Got/mariadb-check: Got/ /\([0-9|-]*\)/(errno)/ +--error 1 +--exec $MYSQL_UPGRADE --skip-verbose --skip-version-check --user=root --password=wrong_password 2>&1 + +--echo # +--echo # MDEV-4332 Increase username length from 16 characters +--echo # MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names +--echo # +--disable_service_connection +connection default; +GRANT SELECT ON mysql.* TO very_long_user_name_number_1; +GRANT SELECT ON mysql.* TO very_long_user_name_number_2; +GRANT ALL ON *.* TO even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost WITH GRANT OPTION; +--change_user even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length + +GRANT INSERT ON mysql.user TO very_long_user_name_number_1; +GRANT INSERT ON mysql.user TO very_long_user_name_number_2; +GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_1; +GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_2; + +CREATE PROCEDURE test.pr() BEGIN END; + +--change_user root + +--replace_result $MYSQLTEST_VARDIR var +--exec $MYSQL_UPGRADE --force 2>&1 + +SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr'; +SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user'; +DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; +DROP PROCEDURE test.pr; +--enable_service_connection +# +# MDEV-13274 mysql_upgrade fails if dbname+tablename+partioname > 64 chars +# +use test; + +create table extralongname_extralongname_extralongname_extralongname_ext ( + id int(10) unsigned not null, + created_date date not null, + created timestamp not null, + primary key (created,id,created_date) +) engine=innodb stats_persistent=1 default charset=latin1 + partition by range (year(created_date)) + subpartition by hash (month(created_date)) + subpartitions 2 ( + partition p2007 values less than (2008), + partition p2008 values less than (2009) + ); +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 +select length(table_name) from mysql.innodb_table_stats; +drop table extralongname_extralongname_extralongname_extralongname_ext; + +--echo # End of 10.0 tests + +set sql_mode=default; + +# +# Enforce storage engine option should not effect mysql_upgrade +# +--echo # Droping the previously created mysql_upgrade_info file.. +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +create table test.t1(a int) engine=MyISAM; +--echo # Trying to enforce InnoDB for all tables +SET GLOBAL enforce_storage_engine=InnoDB; + +--replace_result $MYSQLTEST_VARDIR var +--exec $MYSQL_UPGRADE --force 2>&1 + +--echo # Should return 2 +SELECT count(*) FROM information_schema.tables where ENGINE="InnoDB"; +SHOW CREATE TABLE test.t1; +DROP TABLE test.t1; +# mysql_upgrade must have created mysql_upgrade_info file, +# so the following command should never fail. +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +SET GLOBAL enforce_storage_engine=NULL; + +--echo # End of 10.1 tests + +--echo # +--echo # MDEV-23201 'Roles' are created incorrectly on an install that was previously MySQL 5.7 +--echo # +--echo # + +--exec $MYSQL_DUMP --result-file $MYSQLTEST_VARDIR/tmp/user.sql mysql user +DROP VIEW mysql.user; + +--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; +SHOW CREATE TABLE mysql.user; + +--replace_result $MYSQLTEST_VARDIR var +--exec $MYSQL_UPGRADE --force 2>&1 +SHOW CREATE TABLE mysql.user; + +CREATE ROLE `aRole`; +SET ROLE `aRole`; + +FLUSH PRIVILEGES; + +SET ROLE `aRole`; +SELECT `User`, `is_role` FROM `mysql`.`user`; + +DROP ROLE aRole; + +--echo # +--echo # MDEV-24122 Fix previously MySQL-5.7 data directories that upgraded prior to MDEV-23201 +--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; + +# What prior to MDEV-23201 would of done: +ALTER TABLE mysql.user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; +ALTER TABLE mysql.user ADD default_role char(80) binary DEFAULT '' NOT NULL; +ALTER TABLE mysql.user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL; +FLUSH PRIVILEGES; + +--exec $MYSQL_UPGRADE --force 2>&1 +SHOW CREATE TABLE mysql.user; + +CREATE ROLE `aRole`; +SET DEFAULT ROLE aRole; +SHOW GRANTS; +SET DEFAULT ROLE NONE; +SHOW GRANTS; + +DROP ROLE `aRole`; +--exec $MYSQL mysql < $MYSQLTEST_VARDIR/tmp/user.sql +FLUSH PRIVILEGES; + +--echo # +--echo # MDEV-27279: mariadb_upgrade add --check-if-upgrade-is-needed +--echo # + +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mariadb.* as:[^\n]*/'mariadb' as: mariadb/ +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade absence is do it +--echo # + +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed + +--replace_regex /'mariadb.* as:[^\n]*/'mariadb' as: mariadb/ /open .* Assuming/open XXX. Assuming/ /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with minor version change +--echo # + +# take 3rd number of version and change to 0 + +let DATADIR= $MYSQLD_DATADIR; + +perl; + my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set"; + my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set"; + $ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.$2.0$4/; + open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file"; + print FILE "$ver\n"; + close(FILE); +EOF + +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mariadb.* as:[^\n]*/'mariadb' as: mariadb/ +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--exec $MYSQL_UPGRADE +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with major version change +--echo # + +# take 2rd number of version and change to 0 + +let DATADIR= $MYSQLD_DATADIR; + +perl; + my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set"; + my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set"; + $ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.0.$3$4/; + open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file"; + print FILE "$ver\n"; + close(FILE); +EOF + +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mariadb.* as:[^\n]*/'mysql' as: mysql/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +drop table mysql.global_priv; +rename table mysql.global_priv_bak to mysql.global_priv; + +--echo # End of 10.2 tests + +--echo # +--echo # Ensure that mysql_upgrade correctly sets truncate_versioning_priv +--echo # on upgrade from 10.2 +--echo # + +--source include/switch_to_mysql_user.inc +drop view mysql.user_bak; +CREATE USER 'user3'@'%'; +GRANT USAGE ON *.* TO 'user3'@'%'; +GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'; +alter table mysql.user drop column Delete_history_priv; +alter table mysql.db drop column Delete_history_priv; +--source include/restart_mysqld.inc +--echo Run mysql_upgrade with all privileges on a user +--exec $MYSQL_UPGRADE --force --silent 2>&1 +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +flush privileges; +SHOW GRANTS FOR 'user3'@'%'; +DROP USER 'user3'@'%'; +update mysql.db set Delete_history_priv='Y' where db like 'test%'; +drop table mysql.global_priv; +rename table mysql.global_priv_bak to mysql.global_priv; + +--echo # +--echo # MDEV-16735 Upgrades only work if 'alter_algorithm' is 'DEFAULT' +--echo # or 'COPY'. Test that the session value 'DEFAULT' in mysql_upgrade +--echo # properly overrides the potentially incompatible global value. +--echo # + +SET GLOBAL alter_algorithm='INPLACE'; +SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +--exec $MYSQL_UPGRADE --force 2>&1 +SET GLOBAL alter_algorithm=DEFAULT; +SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo End of 10.3 tests + +--source include/switch_to_mysql_user.inc +drop view mysql.user_bak; +create user 'user3'@'localhost' identified with mysql_native_password as password('a_password'); +show create user user3@localhost; +update mysql.user set password=authentication_string, authentication_string='' where user='user3'; +select password,plugin,authentication_string from mysql.user where user='user3'; +flush privileges; +show create user user3@localhost; +connect con1,localhost,user3,a_password; +select current_user(); +disconnect con1; +connection default; +--echo # mysql_upgrade --force --silent 2>&1 +--exec $MYSQL_UPGRADE --force --silent 2>&1 +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +show create user user3@localhost; +connect con1,localhost,user3,a_password; +select current_user(); +disconnect con1; +connection default; +drop user user3@localhost; +drop table mysql.global_priv; +rename table mysql.global_priv_bak to mysql.global_priv; + +# +# MDEV-18275 Live upgrade from 5.5 does not work: InnoDB stat tables are used before creation +# +--source include/switch_to_mysql_user.inc +drop view mysql.user_bak; +drop table mysql.innodb_index_stats, mysql.innodb_table_stats; +--echo # mysql_upgrade --force --silent 2>&1 +--exec $MYSQL_UPGRADE --force --silent 2>&1 +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +drop table mysql.global_priv; +rename table mysql.global_priv_bak to mysql.global_priv; + +# +# MDEV-21244 mysql_upgrade creating empty global_priv table +# +--source include/switch_to_mysql_user.inc +drop view mysql.user_bak; +alter table mysql.user change authentication_string auth_string text collate utf8_bin not null; +--echo # mysql_upgrade --force --silent 2>&1 +--exec $MYSQL_UPGRADE --force --silent 2>&1 +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +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 # +--echo # mariadb-upgrade fails with sql_safe_updates = on +--echo # + +set @orig_sql_safe_updates = @@GLOBAL.sql_safe_updates; +set global sql_safe_updates=ON; +--exec $MYSQL_UPGRADE --force 2>&1 +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +set global sql_safe_updates=@orig_sql_safe_updates; + +--echo # End of 10.4 tests + +# +# MDEV-22249 Upgrade testing between major versions in MTR +# +--echo # +--echo # Check that mysql_upgrade can be run on mysqldump +--echo # of mysql schema from previous versions +--echo # + +# The warning appears during mysql_upgrade, before the schema becomes consistent +call mtr.add_suppression("innodb_(table|index)_stats has length mismatch in the column name table_name"); +# This comes from opening 10.6 sys.host_summary view that uses sys.format_time function, +# on still inconsistent mysql.proc, in older versions +call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 21, found 20."); + +--let $old_version= 5.5 +--source include/load_dump_and_upgrade.inc + +--let $old_version= 10.0 +--source include/load_dump_and_upgrade.inc + +--let $old_version= 10.1 +--source include/load_dump_and_upgrade.inc + +--let $old_version= 10.2 +--source include/load_dump_and_upgrade.inc + +--let $old_version= 10.3 +--source include/load_dump_and_upgrade.inc + +--let $old_version= 10.4 +--source include/load_dump_and_upgrade.inc |