Ryan Lowe created HIVE-13214:
--------------------------------
Summary: Duplicate MySQL Indexes
Key: HIVE-13214
URL: https://issues.apache.org/jira/browse/HIVE-13214
Project: Hive
Issue Type: Bug
Affects Versions: 2.1.0
Reporter: Ryan Lowe
Assignee: Ryan Lowe
Priority: Minor
Running pt-duplicate-key-checker
(https://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html)
against the schema generated from
metastore/scripts/upgrade/mysql/hive-schema-2.1.0.mysql.sql, the following
duplicate indexes are found:
{code}
# ########################################################################
# test.BUCKETING_COLS
# ########################################################################
# BUCKETING_COLS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `BUCKETING_COLS_N49` (`SD_ID`),
# PRIMARY KEY (`SD_ID`,`INTEGER_IDX`),
# Column types:
# `sd_id` bigint(20) not null
# `integer_idx` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`BUCKETING_COLS` DROP INDEX `BUCKETING_COLS_N49`;
# ########################################################################
# test.COLUMNS_V2
# ########################################################################
# COLUMNS_V2_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `COLUMNS_V2_N49` (`CD_ID`),
# PRIMARY KEY (`CD_ID`,`COLUMN_NAME`),
# Column types:
# `cd_id` bigint(20) not null
# `column_name` varchar(767) character set latin1 collate latin1_bin
not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`COLUMNS_V2` DROP INDEX `COLUMNS_V2_N49`;
# ########################################################################
# test.DATABASE_PARAMS
# ########################################################################
# DATABASE_PARAMS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `DATABASE_PARAMS_N49` (`DB_ID`),
# PRIMARY KEY (`DB_ID`,`PARAM_KEY`),
# Column types:
# `db_id` bigint(20) not null
# `param_key` varchar(180) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`DATABASE_PARAMS` DROP INDEX `DATABASE_PARAMS_N49`;
# ########################################################################
# test.DB_PRIVS
# ########################################################################
# DB_PRIVS_N49 is a left-prefix of DBPRIVILEGEINDEX
# Key definitions:
# KEY `DB_PRIVS_N49` (`DB_ID`),
# UNIQUE KEY `DBPRIVILEGEINDEX`
(`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
# Column types:
# `db_id` bigint(20) default null
# `principal_name` varchar(128) character set latin1 collate latin1_bin
default null
# `principal_type` varchar(128) character set latin1 collate latin1_bin
default null
# `db_priv` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor_type` varchar(128) character set latin1 collate latin1_bin
default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`DB_PRIVS` DROP INDEX `DB_PRIVS_N49`;
# ########################################################################
# test.INDEX_PARAMS
# ########################################################################
# INDEX_PARAMS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `INDEX_PARAMS_N49` (`INDEX_ID`),
# PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`),
# Column types:
# `index_id` bigint(20) not null
# `param_key` varchar(256) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`INDEX_PARAMS` DROP INDEX `INDEX_PARAMS_N49`;
# ########################################################################
# test.PARTITION_KEYS
# ########################################################################
# PARTITION_KEYS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `PARTITION_KEYS_N49` (`TBL_ID`),
# PRIMARY KEY (`TBL_ID`,`PKEY_NAME`),
# Column types:
# `tbl_id` bigint(20) not null
# `pkey_name` varchar(128) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`PARTITION_KEYS` DROP INDEX `PARTITION_KEYS_N49`;
# ########################################################################
# test.PARTITION_KEY_VALS
# ########################################################################
# PARTITION_KEY_VALS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `PARTITION_KEY_VALS_N49` (`PART_ID`),
# PRIMARY KEY (`PART_ID`,`INTEGER_IDX`),
# Column types:
# `part_id` bigint(20) not null
# `integer_idx` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`PARTITION_KEY_VALS` DROP INDEX `PARTITION_KEY_VALS_N49`;
# ########################################################################
# test.PARTITION_PARAMS
# ########################################################################
# PARTITION_PARAMS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `PARTITION_PARAMS_N49` (`PART_ID`),
# PRIMARY KEY (`PART_ID`,`PARAM_KEY`),
# Column types:
# `part_id` bigint(20) not null
# `param_key` varchar(256) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`PARTITION_PARAMS` DROP INDEX `PARTITION_PARAMS_N49`;
# ########################################################################
# test.PART_COL_PRIVS
# ########################################################################
# PART_COL_PRIVS_N49 is a left-prefix of PARTITIONCOLUMNPRIVILEGEINDEX
# Key definitions:
# KEY `PART_COL_PRIVS_N49` (`PART_ID`),
# KEY `PARTITIONCOLUMNPRIVILEGEINDEX`
(`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
# Column types:
# `part_id` bigint(20) default null
# `column_name` varchar(128) character set latin1 collate latin1_bin
default null
# `principal_name` varchar(128) character set latin1 collate latin1_bin
default null
# `principal_type` varchar(128) character set latin1 collate latin1_bin
default null
# `part_col_priv` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor_type` varchar(128) character set latin1 collate latin1_bin
default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`PART_COL_PRIVS` DROP INDEX `PART_COL_PRIVS_N49`;
# ########################################################################
# test.PART_PRIVS
# ########################################################################
# PART_PRIVS_N49 is a left-prefix of PARTPRIVILEGEINDEX
# Key definitions:
# KEY `PART_PRIVS_N49` (`PART_ID`),
# KEY `PARTPRIVILEGEINDEX`
(`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
# Column types:
# `part_id` bigint(20) default null
# `principal_name` varchar(128) character set latin1 collate latin1_bin
default null
# `principal_type` varchar(128) character set latin1 collate latin1_bin
default null
# `part_priv` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor_type` varchar(128) character set latin1 collate latin1_bin
default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`PART_PRIVS` DROP INDEX `PART_PRIVS_N49`;
# ########################################################################
# test.SD_PARAMS
# ########################################################################
# SD_PARAMS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `SD_PARAMS_N49` (`SD_ID`),
# PRIMARY KEY (`SD_ID`,`PARAM_KEY`),
# Column types:
# `sd_id` bigint(20) not null
# `param_key` varchar(256) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`SD_PARAMS` DROP INDEX `SD_PARAMS_N49`;
# ########################################################################
# test.SERDE_PARAMS
# ########################################################################
# SERDE_PARAMS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `SERDE_PARAMS_N49` (`SERDE_ID`),
# PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`),
# Column types:
# `serde_id` bigint(20) not null
# `param_key` varchar(256) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`SERDE_PARAMS` DROP INDEX `SERDE_PARAMS_N49`;
# ########################################################################
# test.SKEWED_COL_NAMES
# ########################################################################
# SKEWED_COL_NAMES_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `SKEWED_COL_NAMES_N49` (`SD_ID`),
# PRIMARY KEY (`SD_ID`,`INTEGER_IDX`),
# Column types:
# `sd_id` bigint(20) not null
# `integer_idx` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`SKEWED_COL_NAMES` DROP INDEX `SKEWED_COL_NAMES_N49`;
# ########################################################################
# test.SKEWED_COL_VALUE_LOC_MAP
# ########################################################################
# SKEWED_COL_VALUE_LOC_MAP_N50 is a left-prefix of PRIMARY
# Key definitions:
# KEY `SKEWED_COL_VALUE_LOC_MAP_N50` (`SD_ID`),
# PRIMARY KEY (`SD_ID`,`STRING_LIST_ID_KID`),
# Column types:
# `sd_id` bigint(20) not null
# `string_list_id_kid` bigint(20) not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`SKEWED_COL_VALUE_LOC_MAP` DROP INDEX
`SKEWED_COL_VALUE_LOC_MAP_N50`;
# ########################################################################
# test.SKEWED_STRING_LIST_VALUES
# ########################################################################
# SKEWED_STRING_LIST_VALUES_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `SKEWED_STRING_LIST_VALUES_N49` (`STRING_LIST_ID`),
# PRIMARY KEY (`STRING_LIST_ID`,`INTEGER_IDX`),
# Column types:
# `string_list_id` bigint(20) not null
# `integer_idx` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`SKEWED_STRING_LIST_VALUES` DROP INDEX
`SKEWED_STRING_LIST_VALUES_N49`;
# ########################################################################
# test.SKEWED_VALUES
# ########################################################################
# SKEWED_VALUES_N50 is a left-prefix of PRIMARY
# Key definitions:
# KEY `SKEWED_VALUES_N50` (`SD_ID_OID`),
# PRIMARY KEY (`SD_ID_OID`,`INTEGER_IDX`),
# Column types:
# `sd_id_oid` bigint(20) not null
# `integer_idx` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`SKEWED_VALUES` DROP INDEX `SKEWED_VALUES_N50`;
# ########################################################################
# test.SORT_COLS
# ########################################################################
# SORT_COLS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `SORT_COLS_N49` (`SD_ID`),
# PRIMARY KEY (`SD_ID`,`INTEGER_IDX`),
# Column types:
# `sd_id` bigint(20) not null
# `integer_idx` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`SORT_COLS` DROP INDEX `SORT_COLS_N49`;
# ########################################################################
# test.TABLE_PARAMS
# ########################################################################
# TABLE_PARAMS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `TABLE_PARAMS_N49` (`TBL_ID`),
# PRIMARY KEY (`TBL_ID`,`PARAM_KEY`),
# Column types:
# `tbl_id` bigint(20) not null
# `param_key` varchar(256) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`TABLE_PARAMS` DROP INDEX `TABLE_PARAMS_N49`;
# ########################################################################
# test.TBL_COL_PRIVS
# ########################################################################
# TBL_COL_PRIVS_N49 is a left-prefix of TABLECOLUMNPRIVILEGEINDEX
# Key definitions:
# KEY `TBL_COL_PRIVS_N49` (`TBL_ID`),
# KEY `TABLECOLUMNPRIVILEGEINDEX`
(`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
# Column types:
# `tbl_id` bigint(20) default null
# `column_name` varchar(128) character set latin1 collate latin1_bin
default null
# `principal_name` varchar(128) character set latin1 collate latin1_bin
default null
# `principal_type` varchar(128) character set latin1 collate latin1_bin
default null
# `tbl_col_priv` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor_type` varchar(128) character set latin1 collate latin1_bin
default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`TBL_COL_PRIVS` DROP INDEX `TBL_COL_PRIVS_N49`;
# ########################################################################
# test.TBL_PRIVS
# ########################################################################
# TBL_PRIVS_N49 is a left-prefix of TABLEPRIVILEGEINDEX
# Key definitions:
# KEY `TBL_PRIVS_N49` (`TBL_ID`),
# KEY `TABLEPRIVILEGEINDEX`
(`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
# Column types:
# `tbl_id` bigint(20) default null
# `principal_name` varchar(128) character set latin1 collate latin1_bin
default null
# `principal_type` varchar(128) character set latin1 collate latin1_bin
default null
# `tbl_priv` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor` varchar(128) character set latin1 collate latin1_bin
default null
# `grantor_type` varchar(128) character set latin1 collate latin1_bin
default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`TBL_PRIVS` DROP INDEX `TBL_PRIVS_N49`;
# ########################################################################
# test.TYPE_FIELDS
# ########################################################################
# TYPE_FIELDS_N49 is a left-prefix of PRIMARY
# Key definitions:
# KEY `TYPE_FIELDS_N49` (`TYPE_NAME`),
# PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`),
# Column types:
# `type_name` bigint(20) not null
# `field_name` varchar(128) character set latin1 collate latin1_bin not
null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`TYPE_FIELDS` DROP INDEX `TYPE_FIELDS_N49`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 173
# Total Duplicate Indexes 21
# Total Indexes 135
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
