This is an automated email from the ASF dual-hosted git repository.
gates pushed a commit to branch branch-3
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/branch-3 by this push:
new 3228f6e HIVE-21756 Backport HIVE-21404 to branch-3: MSSQL upgrade
script alters the wrong column (David Lavati via Alan Gates)
3228f6e is described below
commit 3228f6ee706e84578a73c7d368134a4a07caee13
Author: Alan Gates <[email protected]>
AuthorDate: Mon Jun 24 15:30:35 2019 -0700
HIVE-21756 Backport HIVE-21404 to branch-3: MSSQL upgrade script alters the
wrong column (David Lavati via Alan Gates)
---
.../sql/mssql/upgrade-3.1.0-to-3.2.0.mssql.sql | 41 +++++++++++++++++++++-
1 file changed, 40 insertions(+), 1 deletion(-)
diff --git
a/standalone-metastore/src/main/sql/mssql/upgrade-3.1.0-to-3.2.0.mssql.sql
b/standalone-metastore/src/main/sql/mssql/upgrade-3.1.0-to-3.2.0.mssql.sql
index f2031c8..cc3f263 100644
--- a/standalone-metastore/src/main/sql/mssql/upgrade-3.1.0-to-3.2.0.mssql.sql
+++ b/standalone-metastore/src/main/sql/mssql/upgrade-3.1.0-to-3.2.0.mssql.sql
@@ -21,7 +21,46 @@ ALTER TABLE DBS ADD CREATE_TIME INT;
ALTER TABLE CTLGS ADD CREATE_TIME INT;
-- HIVE-20221
-ALTER TABLE "SERDE_PARAMS" ALTER COLUMN "PARAM_VALUE" nvarchar(MAX);
+
+-- We can not change the datatype of a column with default value. Hence we
first drop the default constraint
+-- and then change the datatype. We wrap the code to drop the default
constraint in a stored procedure to avoid
+-- code duplicate. We create temporary stored procedures since we do not need
them during normal
+-- metastore operation.
+CREATE PROCEDURE #DROP_DEFAULT_CONSTRAINT @TBL_NAME sysname, @COL_NAME sysname
+AS
+BEGIN
+ DECLARE @constraintname sysname
+ SELECT @constraintname = default_constraints.name
+ FROM sys.all_columns INNER JOIN sys.tables ON
all_columns.object_id = tables.object_id
+ INNER JOIN sys.schemas ON tables.schema_id =
schemas.schema_id
+ INNER JOIN sys.default_constraints ON
all_columns.default_object_id = default_constraints.object_id
+ WHERE schemas.name = 'dbo' AND tables.name = @TBL_NAME AND
all_columns.name = @COL_NAME
+
+ IF (@constraintname IS NOT NULL)
+ BEGIN
+ DECLARE @sql nvarchar(max) = 'ALTER TABLE [dbo].' +
QUOTENAME(@TBL_NAME) + ' DROP CONSTRAINT ' + QUOTENAME(@constraintname)
+ EXEC(@sql)
+ END
+END;
+
+-- Similarly for primary key constraint
+CREATE PROCEDURE #DROP_PRIMARY_KEY_CONSTRAINT @TBL_NAME sysname
+AS
+BEGIN
+ DECLARE @constraintname sysname
+ SELECT @constraintname = constraint_name
+ FROM information_schema.table_constraints
+ WHERE constraint_type = 'PRIMARY KEY' AND table_schema = 'dbo'
AND table_name = @TBL_NAME
+ IF @constraintname IS NOT NULL
+ BEGIN
+ DECLARE @sql_pk nvarchar(max) = 'ALTER TABLE [dbo].' +
QUOTENAME(@TBL_NAME) + ' DROP CONSTRAINT ' + @constraintname
+ EXEC(@sql_pk)
+ end
+END;
+
+EXEC #DROP_DEFAULT_CONSTRAINT "PARTITION_PARAMS", "PARAM_VALUE";
+ALTER TABLE "PARTITION_PARAMS" ALTER COLUMN "PARAM_VALUE" varchar(max);
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='3.2.0', VERSION_COMMENT='Hive release
version 3.2.0' where VER_ID=1;
