hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ryan Lowe (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-13214) Duplicate MySQL Indexes
Date Sat, 05 Mar 2016 22:33:40 GMT

     [ 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)

Mime
View raw message