[
https://issues.apache.org/jira/browse/HIVE-21404?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16801848#comment-16801848
]
Ashutosh Bapat commented on HIVE-21404:
---------------------------------------
In a SQL served metastore db, I am observing that param_value column of
partition_params table has default constraint on it
> sp_help partition_params;
+-------------------+--------+-------------+--------------------------+
| Name | Owner | Type | Created_datetime |
+-------------------+--------+-------------+--------------------------+
| PARTITION_PARAMS | dbo | user table | 2019-03-19 10:07:16.697 |
+-------------------+--------+-------------+--------------------------+
1 row selected (0.098 seconds)
+--------------+-----------+-----------+---------+--------+--------+-----------+---------------------+-----------------------+-------------------------------+
| Column_name | Type | Computed | Length | Prec | Scale | Nullable |
TrimTrailingBlanks | FixedLenNullInSource | Collation |
+--------------+-----------+-----------+---------+--------+--------+-----------+---------------------+-----------------------+-------------------------------+
| PART_ID | bigint | no | 8 | 19 | 0 | no | (n/a) | (n/a) | NULL |
| PARAM_KEY | nvarchar | no | 512 | | | no | (n/a) | (n/a) |
SQL_Latin1_General_CP1_CI_AS |
| *PARAM_VALUE | nvarchar | no | 8000 | | | yes | (n/a) | (n/a) |
SQL_Latin1_General_CP1_CI_AS |*
+--------------+-----------+-----------+---------+--------+--------+-----------+---------------------+-----------------------+-------------------------------+
--- clipped output
| constraint_type | constraint_name | delete_action | update_action |
status_enabled | status_for_replication | constraint_keys |
+--------------------------------+---------------------------------+----------------+----------------+-----------------+-------------------------+----------------------------------------------------+
| *DEFAULT on column PARAM_VALUE | DF__PARTITION__PARAM__4F47C5E3* | (n/a) |
(n/a) | (n/a) | (n/a) | (NULL) |
--- clipped output
SQL server won't allow changing datatype of a column with a constraint (See
HIVE-21462 for details). So, we have to first drop this default constraint. But
the name of the default constraint can be anything and thus we require a small
stored procedure for that. I have added that stored procedure in file
upgrade-2.1.0-to-2.2.0.mssql.sql through commit
commit e15781455aacf729c587b47d89d525d08eafb6b8
Author: Ashutosh Bapat <aba...@cloudera.com>
Date: Thu Mar 21 23:34:34 2019 -0700
HIVE-21462: Upgrading SQL server backed metastore when changing data type of a
column with constraints (Ashutosh Bapat, reviewed by Daniel Dai)
Signed-off-by: Daniel Dai <dai...@gmail.com>
The scripts creates a temporary stored procedure so that the procedure vanishes
at the end of the session. I am not sure whether we use the same session to run
all upgrade scripts or create separate sessions for each of them. In the first
case the stored procedure will be available to any upgrade script which runs
after upgrade-2.1.0-to-2.2.0.mssql.sql, which means it will be available to the
script changed by your patch. If it's the other case, the stored procedure will
be required to be created in that script as well in the same manner. By default
a NOT NULL column has default value as NULL, so we don't need to recreate the
default constraint.
> MSSQL upgrade script alters the wrong column
> --------------------------------------------
>
> Key: HIVE-21404
> URL: https://issues.apache.org/jira/browse/HIVE-21404
> Project: Hive
> Issue Type: Bug
> Components: Metastore
> Affects Versions: 3.2.0
> Reporter: David Lavati
> Assignee: David Lavati
> Priority: Major
> Labels: pull-request-available
> Fix For: 3.2.0
>
> Attachments: HIVE-21404.1.patch, HIVE-21404.2.patch
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> HIVE-20221 changes PARTITION_PARAMS, so the following command is modifying
> the wrong table:
> {{ALTER TABLE "SERDE_PARAMS" ALTER COLUMN "PARAM_VALUE" nvarchar(MAX);}}
> https://github.com/apache/hive/blob/d3b036920acde7bb04840697eb13038103b062b4/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.1.0-to-3.2.0.mssql.sql#L21
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
