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 <viral.baja...@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 <viral.baja...@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 >> >> >