hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ashutosh Bapat (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-21404) MSSQL upgrade script alters the wrong column
Date Tue, 26 Mar 2019 15:26:00 GMT

    [ 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 <abapat@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 <daijyc@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)

Mime
View raw message