Return-Path: X-Original-To: apmail-hive-dev-archive@www.apache.org Delivered-To: apmail-hive-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id BFB861812D for ; Sat, 5 Mar 2016 21:19:41 +0000 (UTC) Received: (qmail 66585 invoked by uid 500); 5 Mar 2016 21:19:41 -0000 Delivered-To: apmail-hive-dev-archive@hive.apache.org Received: (qmail 66505 invoked by uid 500); 5 Mar 2016 21:19:41 -0000 Mailing-List: contact dev-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@hive.apache.org Delivered-To: mailing list dev@hive.apache.org Received: (qmail 66493 invoked by uid 99); 5 Mar 2016 21:19:41 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 05 Mar 2016 21:19:41 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id A690F2C1F55 for ; Sat, 5 Mar 2016 21:19:40 +0000 (UTC) Date: Sat, 5 Mar 2016 21:19:40 +0000 (UTC) From: "Ryan Lowe (JIRA)" To: dev@hive.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (HIVE-13214) Duplicate MySQL Indexes MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 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)