[
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* -
{code:java}
ALTER TABLE "NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK
("NNI_ID" = 1);{code}
CHECK constraints are not effective in MySQL 5.7. It is introduced in 8.0.16.
Similarly for MariaDB which uses the same schema script as that of MySQL, CHECK
constraint is effective from 10.2.1.
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* -
{code:java}
ALTER TABLE "NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK
("NNI_ID" = 1);{code}
CHECK constraints are not effective in MySQL 5.7. It is introduced in 8.0.16.
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}
> 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: Minor
>
> 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" ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK
> ("NNI_ID" = 1);{code}
> CHECK constraints are not effective in MySQL 5.7. It is introduced in 8.0.16.
> Similarly for MariaDB which uses the same schema script as that of MySQL,
> CHECK constraint is effective from 10.2.1.
> 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)
