[HIVE-27748] NUCLEUS_TABLES DDLs are missing from MSSQL metastore installation scripts

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0-beta-1
• Component/s: Standalone Metastore
• Assignee: Unassigned
• Reporter: Stamatis Zampetakis
• Created: 27/Sep/23 18:04
• Updated: 27/Sep/23 18:04

Description

The DDL statements creating/altering the NUCLEUS_TABLES technical table is present in the installation scripts of:

• Postgres (https://github.com/apache/hive/blob/57f096c9a73eb92806f2a7cc97f87fabf5d546fe/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0-beta-2.postgres.sql#L178)
• MySQL/MariaDB(https://github.com/apache/hive/blob/57f096c9a73eb92806f2a7cc97f87fabf5d546fe/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0-beta-2.mysql.sql#L211)
• Oracle (https://github.com/apache/hive/blob/57f096c9a73eb92806f2a7cc97f87fabf5d546fe/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.0.0-beta-2.oracle.sql#L22)
• Derby  (https://github.com/apache/hive/blob/57f096c9a73eb92806f2a7cc97f87fabf5d546fe/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0-beta-2.derby.sql#L127)

but is missing from those of the Microsoft SQL Server (https://github.com/apache/hive/blob/57f096c9a73eb92806f2a7cc97f87fabf5d546fe/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.0.0-beta-2.mssql.sql).

According to the comments in the code this table is required if datanucleus.autoStartMechanism=SchemaTable and it may not always be generated automatically.

I haven't checked to what extend this is problematic for the HMS when running with MSSQL but it certainly creates inconsistencies on the functionality of the SchemaTool that should behave the same on every database.