weird alter table commands running on hive metastore

[Viral Bajaria <vi...@gmail.com>]

Hi,

I am seeing a bunch of "ALTER TABLE" commands running on my hive metastore
(MySQL / MariaDB) and it happens whenever I run the Hive CLI.

Why would running a Hive CLI issue ALTER TABLE commands ?

Those ALTER TABLE commands lock the metastore and hence I find a lot of
DEADLOCK issues which I had raised in a separate ticket.

Thanks,
Viral

Re: weird alter table commands running on hive metastore

[Viral Bajaria <vi...@gmail.com>]

Just so that this helps someone else... I found that the issue is with
metastore running on a MariaDB 10.0 with TokuDB engine 7.1

TokuDB does not support foreign keys, it allows the execution of the ALTER
TABLE statements, rebuilds the table for some reason but the foreign key
constraint is not applied.

When hive cli is started, it checks for the constraints, which comes as a
surprise to me, and it applies the missing DDLs.

Because of our distributed infrastructure which runs hive queries via CLI
invocation, the problem was more exaggerated and a lot of queries would
fail.

We switched the engine to InnoDB and everything is working fine now.

Thanks,
Viral


On Thu, Dec 4, 2014 at 4:05 PM, Viral Bajaria <vi...@gmail.com>
wrote:

> Some of the commands that are running (I am ignoring the REFERENCES part
> of it)
>
> ALTER TABLE `PARTITIONS` ADD CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY
> (`SD_ID`)
> ALTER TABLE `COLUMNS_V2` ADD CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY
> (`CD_ID`)
> ALTER TABLE `SDS` ADD CONSTRAINT `SDS_FK1` FOREIGN KEY (`CD_ID`)
> ALTER TABLE `TAB_COL_STATS` ADD CONSTRAINT `TAB_COL_STATS_FK1` FOREIGN KEY
> (`TBL_ID`)
>
> Thanks,
> Viral
>
>
> On Thu, Dec 4, 2014 at 4:02 PM, Viral Bajaria <vi...@gmail.com>
> wrote:
>
>> Hi,
>>
>> I am seeing a bunch of "ALTER TABLE" commands running on my hive
>> metastore (MySQL / MariaDB) and it happens whenever I run the Hive CLI.
>>
>> Why would running a Hive CLI issue ALTER TABLE commands ?
>>
>> Those ALTER TABLE commands lock the metastore and hence I find a lot of
>> DEADLOCK issues which I had raised in a separate ticket.
>>
>> Thanks,
>> Viral
>>
>>
>

Re: weird alter table commands running on hive metastore

[Viral Bajaria <vi...@gmail.com>]

Some of the commands that are running (I am ignoring the REFERENCES part of
it)

ALTER TABLE `PARTITIONS` ADD CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY
(`SD_ID`)
ALTER TABLE `COLUMNS_V2` ADD CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY
(`CD_ID`)
ALTER TABLE `SDS` ADD CONSTRAINT `SDS_FK1` FOREIGN KEY (`CD_ID`)
ALTER TABLE `TAB_COL_STATS` ADD CONSTRAINT `TAB_COL_STATS_FK1` FOREIGN KEY
(`TBL_ID`)

Thanks,
Viral


On Thu, Dec 4, 2014 at 4:02 PM, Viral Bajaria <vi...@gmail.com>
wrote:

> Hi,
>
> I am seeing a bunch of "ALTER TABLE" commands running on my hive metastore
> (MySQL / MariaDB) and it happens whenever I run the Hive CLI.
>
> Why would running a Hive CLI issue ALTER TABLE commands ?
>
> Those ALTER TABLE commands lock the metastore and hence I find a lot of
> DEADLOCK issues which I had raised in a separate ticket.
>
> Thanks,
> Viral
>
>