[
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` 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/create-table.html]
Link - [https://mariadb.com/kb/en/constraint/]
*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` MODIFY COLUMN `NNI_ID` BIGINT(20) GENERATED
ALWAYS AS (1) STORED NOT NULL;{code}
CHECK constraints are not effective in MySQL 5.7. Hence
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]
Link - [https://mariadb.com/kb/en/constraint/]
*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: 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/create-table.html]
> Link - [https://mariadb.com/kb/en/constraint/]
> *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)
