revision-id: cf4f26f103729c6d95ddbd8e96e0ad666a941c44 (mariadb-10.6.1-111-gcf4f26f1037) parent(s): 1bd845eb1e54635f203c1e9f0638650b07a7771a author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-08-31 17:46:07 +0300 message: Handle upgrades --- mysql-test/main/statistics_upgrade.result | 88 +++++++++++++++++++++++++++++++ mysql-test/main/statistics_upgrade.test | 68 ++++++++++++++++++++++++ scripts/mysql_system_tables_fix.sql | 8 +++ 3 files changed, 164 insertions(+) diff --git a/mysql-test/main/statistics_upgrade.result b/mysql-test/main/statistics_upgrade.result new file mode 100644 index 00000000000..323a5132b05 --- /dev/null +++ b/mysql-test/main/statistics_upgrade.result @@ -0,0 +1,88 @@ +call mtr.add_suppression("Incorrect definition of table mysql.column_stats:.*"); +set histogram_type=single_prec_hb; +create table t1 (a int); +insert into t1 select seq from seq_1_to_100; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +set histogram_type=double_prec_hb, histogram_size=20; +create table t2 (a int); +insert into t2 select seq from seq_1_to_100; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +CREATE TABLE IF NOT EXISTS t3 ( +db_name varchar(64) NOT NULL, +table_name varchar(64) NOT NULL, +column_name varchar(64) NOT NULL, +min_value varbinary(255) DEFAULT NULL, +max_value varbinary(255) DEFAULT NULL, +nulls_ratio decimal(12,4) DEFAULT NULL, +avg_length decimal(12,4) DEFAULT NULL, +avg_frequency decimal(12,4) DEFAULT NULL, +hist_size tinyint unsigned, +hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB'), +histogram varbinary(255), +PRIMARY KEY (db_name,table_name,column_name) +) engine=Aria transactional=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; +insert into t3 select * from mysql.column_stats; +create table t4 like t3; +insert into t4 select * from t3; +drop table mysql.column_stats; +alter table t3 rename mysql.column_stats; +# Run mysql_upgrade +/optane/dev-git2/10.7-gsoc/client/mysql_upgrade --defaults-file=/optane/dev-git2/10.7-gsoc/mysql-test/var/my.cnf +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.transaction_registry +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.transaction_registry +Error : Unknown storage engine 'InnoDB' +error : Corrupt +# +# Table definition after upgrade: +# +show create table mysql.column_stats; +Table Create Table +column_stats CREATE TABLE `column_stats` ( + `db_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, + `min_value` varbinary(255) DEFAULT NULL, + `max_value` varbinary(255) DEFAULT NULL, + `nulls_ratio` decimal(12,4) DEFAULT NULL, + `avg_length` decimal(12,4) DEFAULT NULL, + `avg_frequency` decimal(12,4) DEFAULT NULL, + `hist_size` tinyint(3) unsigned DEFAULT NULL, + `hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB') COLLATE utf8mb3_bin DEFAULT NULL, + `histogram` blob DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`column_name`) +) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=0 COMMENT='Statistics on Columns' +select +A.db_name, +A.table_name, +A.hist_type = B.hist_type as hist_type_equal, +A.histogram = B.histogram as histogram_equal +from +t4 A +left join +mysql.column_stats B on A.db_name=B.db_name and +A.table_name=B.table_name and +A.column_name=B.column_name; +db_name table_name hist_type_equal histogram_equal +test t1 1 1 +test t2 1 1 +drop table t1,t2,t4; diff --git a/mysql-test/main/statistics_upgrade.test b/mysql-test/main/statistics_upgrade.test new file mode 100644 index 00000000000..4d80518a171 --- /dev/null +++ b/mysql-test/main/statistics_upgrade.test @@ -0,0 +1,68 @@ +-- source include/mysql_upgrade_preparation.inc +--source include/have_sequence.inc + +call mtr.add_suppression("Incorrect definition of table mysql.column_stats:.*"); + +set histogram_type=single_prec_hb; +create table t1 (a int); +insert into t1 select seq from seq_1_to_100; +analyze table t1 persistent for all; + +set histogram_type=double_prec_hb, histogram_size=20; +create table t2 (a int); +insert into t2 select seq from seq_1_to_100; +analyze table t2 persistent for all; + +# Create a table with old definition +CREATE TABLE IF NOT EXISTS t3 ( + db_name varchar(64) NOT NULL, + table_name varchar(64) NOT NULL, + column_name varchar(64) NOT NULL, + min_value varbinary(255) DEFAULT NULL, + max_value varbinary(255) DEFAULT NULL, + nulls_ratio decimal(12,4) DEFAULT NULL, + avg_length decimal(12,4) DEFAULT NULL, + avg_frequency decimal(12,4) DEFAULT NULL, + hist_size tinyint unsigned, + hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB'), + histogram varbinary(255), + PRIMARY KEY (db_name,table_name,column_name) +) engine=Aria transactional=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +# Copy the data there +insert into t3 select * from mysql.column_stats; + +create table t4 like t3; +insert into t4 select * from t3; + +# Put the old table in place of mysql.column_stats + +drop table mysql.column_stats; +alter table t3 rename mysql.column_stats; +--echo # Run mysql_upgrade + +--echo $MYSQL_UPGRADE +--exec $MYSQL_UPGRADE --upgrade-system-tables --force --silent 2>&1 + +let $MYSQLD_DATADIR= `select @@datadir`; +--file_exists $MYSQLD_DATADIR/mysql_upgrade_info +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo # +--echo # Table definition after upgrade: +--echo # +show create table mysql.column_stats; + +select + A.db_name, + A.table_name, + A.hist_type = B.hist_type as hist_type_equal, + A.histogram = B.histogram as histogram_equal +from + t4 A + left join + mysql.column_stats B on A.db_name=B.db_name and + A.table_name=B.table_name and + A.column_name=B.column_name; + +drop table t1,t2,t4; diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index ed89cc1fd6c..410e1db2670 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -830,3 +830,11 @@ IF 1 = (SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def END IF// DELIMITER ; + +-- +-- Upgrade mysql.column_stats table +-- + +ALTER TABLE column_stats + modify hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), + modify histogram blob;