[HIVE-23074] SchemaTool sql script execution errors when updating the metadata's schema

• Type: Bug
• Status: Patch Available
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.2
• Component/s: SQL
• Labels: pull-request-available

Environment

running machine: centos7.2 

metadata db: PostgreSQL 11.3 on x86_64-pc-linux-gnu

hive version: upgrade from version 3.0.0 to 3.1.2

• Target Version/s: 3.1.2
• Release Note: add "IF NOT EXISTS" for create & "IF EXISTS" for drop to keep consistency in the sql script
• Assignee: John1Tang
• Reporter: John1Tang
• Due: 25/Mar/20
• Created: 25/Mar/20 09:48
• Updated: 21/Oct/22 07:31

Description

SchemaTool sql script executed with conflicts on indices and columns and missed " for avoiding keywords when updating the metadata's schema

bin/schematool -dbType postgres -upgradeSchemaFrom 3.0.0

went like this:

ALTER TABLE "GLOBAL_PRIVS" ADD COLUMN "AUTHORIZER" character varying(128) DEFAULT NULL::character varying
Error: ERROR: column "AUTHORIZER" of relation "GLOBAL_PRIVS" already exists (state=42701,code=0)

ALTER TABLE COMPLETED_TXN_COMPONENTS ADD COLUMN IF NOT EXISTS CTC_UPDATE_DELETE char(1) NULL
Error: ERROR: relation "completed_txn_components" does not exist (state=42P01,code=0)

I've already come up with a solution and created a pull request for this issue.

Issue Links

• links to: GitHub Pull Request #967