set sql_mode="";
Run mysql_upgrade once
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
Run it again - should say already completed
This installation of MariaDB is already upgraded to VERSION, use --force if you still need to run mysql_upgrade
Force should run it regardless of whether it has been run before
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
CREATE USER mysqltest1@'%' IDENTIFIED by 'sakila';
GRANT ALL ON *.* TO mysqltest1@'%';
Run mysql_upgrade with password protected account
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
DROP USER mysqltest1@'%';
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'mysqltest1'@'localhost' (using password: YES)
FATAL ERROR: Upgrade failed
Run mysql_upgrade with a non existing server socket
mysqlcheck: Got error: 2005: Unknown MySQL server host 'not_existing_host' (errno) when trying to connect
FATAL ERROR: Upgrade failed
set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE';
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
set GLOBAL sql_mode=default;
#
# Bug #41569 mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table 
# but does not set values.
#
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';
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
CALL testproc();
DROP PROCEDURE testproc;
WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (latin1). Please verify if necessary.
WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (latin1_swedish_ci). Please verify if necessary.
WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.
#
# Bug #53613: mysql_upgrade incorrectly revokes 
#   TRIGGER privilege on given table
#
GRANT USAGE ON *.* TO 'user3'@'%';
GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%';
Run mysql_upgrade with all privileges on a user
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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 GRANTS FOR 'user3'@'%';
Grants for user3@%
GRANT USAGE ON *.* TO `user3`@`%`
GRANT ALL PRIVILEGES ON `roelt`.`test2` TO `user3`@`%`
DROP USER 'user3'@'%';
# End of 5.1 tests
The --upgrade-system-tables option was used, user tables won't be touched.
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views... Skipped
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names ... Skipped
Phase 6/7: Checking and upgrading tables... Skipped
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK
#
# Bug#11827359 60223: MYSQL_UPGRADE PROBLEM WITH OPTION
#                     SKIP-WRITE-BINLOG
#
# Droping the previously created mysql_upgrade_info file..
# Running mysql_upgrade with --skip-write-binlog..
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
#
# Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR
#
Run mysql_upgrade with unauthorized access
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
FATAL ERROR: Upgrade failed
#
# MDEV-4332 Increase username length from 16 characters
# MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names
#
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;
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;
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr';
definer
even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost
SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user';
grantor
even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost
even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost
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;
use test;
call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be');
alter table mysql.innodb_table_stats modify last_update int not null;
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)
);
select length(table_name) from mysql.innodb_table_stats;
length(table_name)
79
79
79
79
drop table extralongname_extralongname_extralongname_extralongname_ext;
# End of 10.0 tests
set sql_mode=default;
# Droping the previously created mysql_upgrade_info file..
create table test.t1(a int) engine=MyISAM;
# Trying to enforce InnoDB for all tables
SET GLOBAL enforce_storage_engine=InnoDB;
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                                  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
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
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
test.t1                                            OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK
# Should return 2
SELECT count(*) FROM information_schema.tables where ENGINE="InnoDB";
count(*)
3
SHOW CREATE TABLE test.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE test.t1;
SET GLOBAL enforce_storage_engine=NULL;
# End of 10.1 tests
# End of 10.2 tests
#
# Ensure that mysql_upgrade correctly sets truncate_versioning_priv
# on upgrade from 10.2
#
# switching from mysql.global_priv to mysql.user
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;
# restart
Run mysql_upgrade with all privileges on a user
flush privileges;
SHOW GRANTS FOR 'user3'@'%';
Grants for user3@%
GRANT USAGE ON *.* TO `user3`@`%`
GRANT ALL PRIVILEGES ON `roelt`.`test2` TO `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;
# End of 10.3 tests
# switching from mysql.global_priv to mysql.user
drop view mysql.user_bak;
create user 'user3'@'localhost' identified with mysql_native_password as password('a_password');
show create user user3@localhost;
CREATE USER for user3@localhost
CREATE USER `user3`@`localhost` IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA'
update mysql.user set password=authentication_string, authentication_string='' where user='user3';
select password,plugin,authentication_string from mysql.user where user='user3';
password	plugin	authentication_string
*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA	mysql_native_password	
flush privileges;
show create user user3@localhost;
CREATE USER for user3@localhost
CREATE USER `user3`@`localhost` IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' PASSWORD EXPIRE NEVER
connect con1,localhost,user3,a_password;
select current_user();
current_user()
user3@localhost
disconnect con1;
connection default;
# mysql_upgrade --force --silent 2>&1
show create user user3@localhost;
CREATE USER for user3@localhost
CREATE USER `user3`@`localhost` IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA'
connect con1,localhost,user3,a_password;
select current_user();
current_user()
user3@localhost
disconnect con1;
connection default;
drop user user3@localhost;
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
# switching from mysql.global_priv to mysql.user
drop view mysql.user_bak;
drop table mysql.innodb_index_stats, mysql.innodb_table_stats;
# mysql_upgrade --force --silent 2>&1
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
# switching from mysql.global_priv to mysql.user
drop view mysql.user_bak;
alter table mysql.user change authentication_string auth_string text collate utf8_bin not null;
# mysql_upgrade --force --silent 2>&1
select count(*) from mysql.global_priv;
count(*)
5
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
# End of 10.4 tests