"Primary Key needed Error" when creating Hive Metastore on Percona XtraDB Cluster(MySQL Compatible)

[#1]

Dear team,

We are trying to build Hive Metastore on Percona XtraDB Cluster ,which is MySQL Compatible.

https://www.percona.com/software/mysql-database/percona-xtradb-cluster

However, got error when run initialize SQL scripts on Percona XtraDB.

Error:

> desc NEXT_COMPACTION_QUEUE_ID;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| NCQ_NEXT | bigint(20) | NO   |     | NULL    |       |
+----------+------------+------+-----+---------+-------+ 
> INSERT INTO NEXT_COMPACTION_QUEUE_ID VALUES(1);ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (metastore.NEXT_COMPACTION_QUEUE_ID) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

I think we can resolve this problem with changing pxc_strict_mode to other values such as DISABLED, however our Database platform doesn't allow us to do that.

It means that when initializing some Hive metastore tables with out PK, it will fail in Percona XtraDB Cluster.

Does anybody met the same situation or is there any way to avoid this problem without changing pxc_strict_mode?

[#2 · Accepted solution]

it may be simpler to modify your table to add a primary key to NEXT_COMPACTION_QUEUE - any synthetic PK will work.

[#3]

Yes, but I don't know the impact of adding a PK to NEXT_COMPACTION_QUEUE table,

because this table belongs to Hive metastore.

I can add a PK to this table, but I am not sure all the other functions of Hive will work correctly without a full test, so I asked this question.

[#4]

I'm a developer of this system - I believe this is safe (though I've not ran a full test to prove it)

[#5]

There are actually several tables missing Primary Keys all related to transactions. I've submitted HIVE-17306 to address this as currently I don't know the impact of adding Primary Keys to all of these tables. On HDP 2.6 it appears that the following tables don't have Primary Keys. On another note I'm not sure you can add surrogate keys as it looks like we have some unqualified inserts in Transaction Metastore that don't explicity list what columns are being inserted. This prevents just adding an extra column.

completed_txn_components

next_compaction_queue_id

next_lock_id

next_txn_id

txn_components

write_set