From issues-return-154061-archive-asf-public=cust-asf.ponee.io@hive.apache.org Tue Mar 26 15:26:02 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 60FAD180627 for ; Tue, 26 Mar 2019 16:26:02 +0100 (CET) Received: (qmail 16158 invoked by uid 500); 26 Mar 2019 15:26:01 -0000 Mailing-List: contact issues-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@hive.apache.org Delivered-To: mailing list issues@hive.apache.org Received: (qmail 16149 invoked by uid 99); 26 Mar 2019 15:26:01 -0000 Received: from mailrelay1-us-west.apache.org (HELO mailrelay1-us-west.apache.org) (209.188.14.139) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 Mar 2019 15:26:01 +0000 Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 99BFCE2799 for ; Tue, 26 Mar 2019 15:26:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 31B6A2459A for ; Tue, 26 Mar 2019 15:26:00 +0000 (UTC) Date: Tue, 26 Mar 2019 15:26:00 +0000 (UTC) From: "Ashutosh Bapat (JIRA)" To: issues@hive.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (HIVE-21404) MSSQL upgrade script alters the wrong column MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/HIVE-21404?page=3Dcom.atlassian= .jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1680= 1848#comment-16801848 ]=20 Ashutosh Bapat commented on HIVE-21404: --------------------------------------- In a SQL served metastore db, I am observing that param_value column of par= tition_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 | TrimTr= ailingBlanks | 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_Gen= eral_CP1_CI_AS | | *PARAM_VALUE | nvarchar | no | 8000 | | | yes | (n/a) | (n/a) | SQL_Latin= 1_General_CP1_CI_AS |* +--------------+-----------+-----------+---------+--------+--------+-------= ----+---------------------+-----------------------+------------------------= -------+ --- clipped output | constraint_type | constraint_name | delete_action | update_action | statu= s_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 requir= e a small stored procedure for that. I have added that stored procedure in = file=C2=A0upgrade-2.1.0-to-2.2.0.mssql.sql through commit=C2=A0 commit e15781455aacf729c587b47d89d525d08eafb6b8 Author: Ashutosh Bapat Date: Thu Mar 21 23:34:34 2019 -0700 HIVE-21462: Upgrading SQL server backed metastore when changing data type o= f a column with constraints (Ashutosh Bapat, reviewed by Daniel Dai) Signed-off-by: Daniel Dai The scripts creates a temporary stored procedure so that the procedure vani= shes at the end of the session. I am not sure whether we use the same sessi= on 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 sc= ript which runs after=C2=A0upgrade-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 c= ase, 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 modifyin= g the wrong table: > {{ALTER TABLE "SERDE_PARAMS" ALTER COLUMN "PARAM_VALUE" nvarchar(MAX);}} > https://github.com/apache/hive/blob/d3b036920acde7bb04840697eb13038103b06= 2b4/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)