[
https://issues.apache.org/jira/browse/HIVE-26324?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sourabh Badhya updated HIVE-26324:
----------------------------------
Description:
NOTIFICATION_SEQUENCE table must have only one row however there have been
several reports of NOTIFICATION_SEQUENCE table having multiple rows. In order
to prevent this situation from happening, its best to enforce "one-row-table"
like constraints on NOTIFICATION_SEQUENCE table.
Queries tried on supported databases -
NOTIFICATION_SEQUENCE already has NNI_ID as the primary key. This will help us
in adding "one-row-table" like constraints.
MySQL - Assuming that the supported versions currently is 5.7+
{code:java}
DELIMITER //
CREATE TRIGGER `ONE_ROW_TRIGGER_ON_NOTIFICATION_SEQUENCE` BEFORE INSERT ON
`NOTIFICATION_SEQUENCE`
FOR EACH ROW
BEGIN
IF NEW.`NNI_ID` != 1 THEN
SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'CANNOT INSERT ROW';
END IF;
END;//
DELIMITER ; {code}
CHECK constraints are not effective in MySQL 5.7. It is introduced in 8.0.16.
Hence need to switch to using triggers.
Link - [https://dev.mysql.com/doc/refman/5.7/en/create-table.html]
Postgres -
Either change the definition of table like this -
{code:java}
CREATE TABLE "NOTIFICATION_SEQUENCE"
(
"NNI_ID" BIGINT NOT NULL CHECK ("NNI_ID" = 1),
"NEXT_EVENT_ID" BIGINT NOT NULL,
PRIMARY KEY ("NNI_ID")
); {code}
OR add explicit constraints like this -
{code:java}
ALTER TABLE "NOTIFICATION_SEQUENCE"
ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK ("NNI_ID" = 1); {code}
Derby -
{code:java}
ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT"
CHECK (NNI_ID = 1); {code}
Oracle -
Either change the definition of table like this -
{code:java}
CREATE TABLE NOTIFICATION_SEQUENCE
(
NNI_ID NUMBER NOT NULL CHECK (NNI_ID = 1),
NEXT_EVENT_ID NUMBER NOT NULL
); {code}
OR add explicit constraints like this -
{code:java}
ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
(NNI_ID = 1); {code}
Microsoft SQL Server -
{code:java}
ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
(NNI_ID = 1); {code}
was:
NOTIFICATION_SEQUENCE table must have only one row however there have been
several reports of NOTIFICATION_SEQUENCE table having multiple rows. In order
to prevent this situation from happening, its best to enforce "one-row-table"
like constraints on NOTIFICATION_SEQUENCE table.
Queries tried on supported databases -
NOTIFICATION_SEQUENCE already has NNI_ID as the primary key. This will help us
in adding "one-row-table" like constraints.
MySQL - Assuming that the supported versions currently is 5.7+
{code:java}
DELIMITER //
CREATE TRIGGER `ONE_ROW_TRIGGER_ON_NOTIFICATION_SEQUENCE` BEFORE INSERT ON
`NOTIFICATION_SEQUENCE`
FOR EACH ROW
BEGIN
IF NEW.`NNI_ID` != 1 THEN
SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'CANNOT INSERT ROW';
END IF;
END;//
DELIMITER ; {code}
CHECK constraints are not effective in MySQL 5.7. It is introduced in 8.0.16.
Hence need to switch to using triggers.
Link - [https://dev.mysql.com/doc/refman/5.7/en/create-table.html]
Postgres -
Either change the definition of table like this -
{code:java}
CREATE TABLE "NOTIFICATION_SEQUENCE"
(
"NNI_ID" BIGINT NOT NULL CHECK ("NNI_ID" = 1),
"NEXT_EVENT_ID" BIGINT NOT NULL,
PRIMARY KEY ("NNI_ID")
); {code}
OR add explicit constraints like this -
{code:java}
ALTER TABLE "NOTIFICATION_SEQUENCE"
ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK ("NNI_ID" = 1); {code}
Derby -
{code:java}
ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT"
CHECK (NNI_ID = 1); {code}
Oracle -
Either change the definition of table like this -
{code:java}
CREATE TABLE NOTIFICATION_SEQUENCE
(
NNI_ID NUMBER NOT NULL CHECK (NNI_ID = 1),
NEXT_EVENT_ID NUMBER NOT NULL
); {code}
OR add explicit constraints like this -
{code:java}
ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
(NNI_ID = 1); {code}
> Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table
> --------------------------------------------------------------
>
> Key: HIVE-26324
> URL: https://issues.apache.org/jira/browse/HIVE-26324
> Project: Hive
> Issue Type: Bug
> Reporter: Sourabh Badhya
> Assignee: Sourabh Badhya
> Priority: Major
>
> NOTIFICATION_SEQUENCE table must have only one row however there have been
> several reports of NOTIFICATION_SEQUENCE table having multiple rows. In order
> to prevent this situation from happening, its best to enforce "one-row-table"
> like constraints on NOTIFICATION_SEQUENCE table.
> Queries tried on supported databases -
> NOTIFICATION_SEQUENCE already has NNI_ID as the primary key. This will help
> us in adding "one-row-table" like constraints.
> MySQL - Assuming that the supported versions currently is 5.7+
> {code:java}
> DELIMITER //
> CREATE TRIGGER `ONE_ROW_TRIGGER_ON_NOTIFICATION_SEQUENCE` BEFORE INSERT ON
> `NOTIFICATION_SEQUENCE`
> FOR EACH ROW
> BEGIN
> IF NEW.`NNI_ID` != 1 THEN
> SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'CANNOT INSERT ROW';
> END IF;
> END;//
> DELIMITER ; {code}
> CHECK constraints are not effective in MySQL 5.7. It is introduced in 8.0.16.
> Hence need to switch to using triggers.
> Link - [https://dev.mysql.com/doc/refman/5.7/en/create-table.html]
> Postgres -
> Either change the definition of table like this -
> {code:java}
> CREATE TABLE "NOTIFICATION_SEQUENCE"
> (
> "NNI_ID" BIGINT NOT NULL CHECK ("NNI_ID" = 1),
> "NEXT_EVENT_ID" BIGINT NOT NULL,
> PRIMARY KEY ("NNI_ID")
> ); {code}
> OR add explicit constraints like this -
> {code:java}
> ALTER TABLE "NOTIFICATION_SEQUENCE"
> ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK ("NNI_ID" = 1); {code}
> Derby -
> {code:java}
> ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT"
> CHECK (NNI_ID = 1); {code}
> Oracle -
> Either change the definition of table like this -
> {code:java}
> CREATE TABLE NOTIFICATION_SEQUENCE
> (
> NNI_ID NUMBER NOT NULL CHECK (NNI_ID = 1),
> NEXT_EVENT_ID NUMBER NOT NULL
> ); {code}
> OR add explicit constraints like this -
> {code:java}
> ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
> (NNI_ID = 1); {code}
> Microsoft SQL Server -
> {code:java}
> ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
> (NNI_ID = 1); {code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
