You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Ryan Lowe (JIRA)" <ji...@apache.org> on 2016/03/05 23:33:40 UTC
[jira] [Updated] (HIVE-13214) Duplicate MySQL Indexes
[ https://issues.apache.org/jira/browse/HIVE-13214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ryan Lowe updated HIVE-13214:
-----------------------------
Component/s: Metastore
> Duplicate MySQL Indexes
> -----------------------
>
> Key: HIVE-13214
> URL: https://issues.apache.org/jira/browse/HIVE-13214
> Project: Hive
> Issue Type: Bug
> Components: Metastore
> Affects Versions: 2.1.0
> Reporter: Ryan Lowe
> Assignee: Ryan Lowe
> Priority: Minor
> Attachments: HIVE-13214.patch
>
>
> 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)