[
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/16/22 12:00 PM:
-----------------------------------------------------------------
Had an internal discussion and it was decided that MySQL must also use the
GENERATED columns and not triggers. 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. Mainly because triggers are stored procedures and Hive must not initiate
making use of triggers/stored procedures in the backend DB.
> 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: Task
> Reporter: Sourabh Badhya
> Assignee: Sourabh Badhya
> Priority: Minor
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> 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* -
> {code:java}
> ALTER TABLE `NOTIFICATION_SEQUENCE` MODIFY COLUMN `NNI_ID` BIGINT(20)
> GENERATED ALWAYS AS (1) STORED NOT NULL;{code}
> CHECK constraints are not effective in MySQL 5.7. Hence need to shift to
> using GENERATED columns. This is supported in MySQL 5.7.
> Similarly for MariaDB which uses the same schema script as that of MySQL,
> Generated columns with syntax compatible with MySQL are supported from 10.2.
> Link -
> [https://dev.mysql.com/doc/refman/5.7/en/alter-table-generated-columns.html]
> Link - [https://mariadb.com/kb/en/generated-columns/]
> *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)
