[jira] [Comment Edited] (HIVE-26324) Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table

["Sourabh Badhya (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-26324?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17554033#comment-17554033 ] 

Sourabh Badhya edited comment on HIVE-26324 at 6/14/22 11:04 AM:
-----------------------------------------------------------------

Had an internal discussion and it was decided that MySQL must also use the CHECK constraint and not triggers even though it is not effective for MySQL 5.7. Mainly because triggers are stored procedures and Hive must not initiate making use of triggers/stored procedures in the backend DB.


was (Author: JIRAUSER287127):
Had an internal discussion and it was decided that MySQL must also use the CHECK constraint and not triggers even though it is not effective for MySQL 5.7.

> 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}



--
This message was sent by Atlassian Jira
(v8.20.7#820007)