dengzhhu653 commented on code in PR #6521: URL: https://github.com/apache/hive/pull/6521#discussion_r3629411139
########## standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.2.0-to-4.3.0.mysql.sql: ########## @@ -3,8 +3,98 @@ SELECT 'Upgrading MetaStore schema from 4.2.0 to 4.3.0' AS MESSAGE; ALTER TABLE HIVE_LOCKS ADD HL_CATALOG varchar(128) NOT NULL DEFAULT 'hive'; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD MRL_CAT_NAME varchar(128) NOT NULL DEFAULT 'hive'; +-- Add surrogate primary keys for HA database replication. Adding AUTO_INCREMENT +-- PRIMARY KEY via ALTER TABLE rebuilds large InnoDB tables and blocks writes for +-- the duration. Rebuild each populated table via a TMP_* copy/swap instead. +-- Counter tables contain a single row and keep the one-step ALTER form. Plan a +-- maintenance window for busy metastores; duration scales with row counts. + +CREATE TABLE TMP_TXN_COMPONENTS ( + TC_ID bigint NOT NULL AUTO_INCREMENT, + TC_TXNID bigint NOT NULL, + TC_DATABASE varchar(128) NOT NULL, + TC_TABLE varchar(256), + TC_PARTITION varchar(767), + TC_OPERATION_TYPE char(1) NOT NULL, + TC_WRITEID bigint, + PRIMARY KEY (TC_ID), + FOREIGN KEY (TC_TXNID) REFERENCES TXNS (TXN_ID) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO TMP_TXN_COMPONENTS (TC_TXNID, TC_DATABASE, TC_TABLE, TC_PARTITION, TC_OPERATION_TYPE, TC_WRITEID) + SELECT TC_TXNID, TC_DATABASE, TC_TABLE, TC_PARTITION, TC_OPERATION_TYPE, TC_WRITEID FROM TXN_COMPONENTS; +RENAME TABLE TXN_COMPONENTS TO TXN_COMPONENTS_OLD, TMP_TXN_COMPONENTS TO TXN_COMPONENTS; +DROP TABLE TXN_COMPONENTS_OLD; Review Comment: How about creating a new type of upgrade file, say post-upgrade-4.2.0-to-4.3.0.mysql.sql to drop the old table if configured to, so we can rollback quickly if something is wrong -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
