[jira] [Work logged] (HIVE-26324) Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table
[
https://issues.apache.org/jira/browse/HIVE-26324?focusedWorklogId=782297=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-782297
]
ASF GitHub Bot logged work on HIVE-26324:
-
Author: ASF GitHub Bot
Created on: 17/Jun/22 07:57
Start Date: 17/Jun/22 07:57
Worklog Time Spent: 10m
Work Description: deniskuzZ merged PR #3369:
URL: https://github.com/apache/hive/pull/3369
Issue Time Tracking
---
Worklog Id: (was: 782297)
Time Spent: 40m (was: 0.5h)
> 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: 40m
> 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)
[jira] [Work logged] (HIVE-26324) Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table
[
https://issues.apache.org/jira/browse/HIVE-26324?focusedWorklogId=781962=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-781962
]
ASF GitHub Bot logged work on HIVE-26324:
-
Author: ASF GitHub Bot
Created on: 16/Jun/22 08:45
Start Date: 16/Jun/22 08:45
Worklog Time Spent: 10m
Work Description: SourabhBadhya commented on PR #3369:
URL: https://github.com/apache/hive/pull/3369#issuecomment-1157398601
@deniskuzZ Yes I agree. `NNI_ID` if used anywhere (which I don't think its
used) is always `1`. No point in having that column.
Issue Time Tracking
---
Worklog Id: (was: 781962)
Time Spent: 0.5h (was: 20m)
> 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` 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]
> 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)
[jira] [Work logged] (HIVE-26324) Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table
[
https://issues.apache.org/jira/browse/HIVE-26324?focusedWorklogId=781149=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-781149
]
ASF GitHub Bot logged work on HIVE-26324:
-
Author: ASF GitHub Bot
Created on: 14/Jun/22 12:38
Start Date: 14/Jun/22 12:38
Worklog Time Spent: 10m
Work Description: github-actions[bot] commented on PR #3369:
URL: https://github.com/apache/hive/pull/3369#issuecomment-1155130056
# @check-spelling-bot Report
### :red_circle: Please review
See the [files](3369/files/) view or the [action
log](https://github.com/apache/hive/actions/runs/2495234084) for details.
Unrecognized words (3)
api
esri
wkid
Previously acknowledged words that are now absent
aarry timestamplocal
Available dictionaries could cover words not in the
dictionary
[cspell:cpp/cpp.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/cpp/cpp.txt)
(104293) covers 81 of them
[cspell:django/django.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/django/django.txt)
(2342) covers 14 of them
[cspell:golang/go.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/golang/go.txt)
(7745) covers 12 of them
[cspell:java/java.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/java/java.txt)
(33524) covers 11 of them
[cspell:filetypes/filetypes.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/filetypes/filetypes.txt)
(337) covers 10 of them
[cspell:aws/aws.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/aws/aws.txt)
(1485) covers 10 of them
[cspell:css/css.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/css/css.txt)
(993) covers 9 of them
[cspell:rust/rust.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/rust/rust.txt)
(112) covers 8 of them
[cspell:npm/npm.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/npm/npm.txt)
(671) covers 8 of them
[cspell:html/html.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/html/html.txt)
(542) covers 8 of them
[cspell:scala/scala.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/scala/scala.txt)
(2752) covers 7 of them
[cspell:php/php.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/php/php.txt)
(9785) covers 6 of them
[cspell:fullstack/fullstack.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/fullstack/fullstack.txt)
(181) covers 5 of them
[cspell:csharp/csharp.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/csharp/csharp.txt)
(123) covers 5 of them
[cspell:python/python.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/python/python.txt)
(364) covers 3 of them
[cspell:lua/lua.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/lua/lua.txt)
(391) covers 3 of them
[cspell:dotnet/dotnet.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/dotnet/dotnet.txt)
(9824) covers 2 of them
[cspell:ruby/ruby.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/ruby/ruby.txt)
(354) covers 1 of them
[cspell:bash/bash-words.txt](https://raw.githubusercontent.com/check-spelling/cspell-dicts/HEAD/dictionaries/bash/bash-words.txt)
(22) covers 1 of them
Consider adding them using:
```
with:
extra_dictionaries:
cspell:cpp/cpp.txt
cspell:django/django.txt
cspell:golang/go.txt
cspell:java/java.txt
cspell:filetypes/filetypes.txt
cspell:aws/aws.txt
cspell:css/css.txt
cspell:rust/rust.txt
cspell:npm/npm.txt
cspell:html/html.txt
cspell:scala/scala.txt
cspell:php/php.txt
cspell:fullstack/fullstack.txt
cspell:csharp/csharp.txt
cspell:python/python.txt
cspell:lua/lua.txt
cspell:dotnet/dotnet.txt
cspell:ruby/ruby.txt
cspell:bash/bash-words.txt
```
To stop checking additional dictionaries, add:
```
with:
check_extra_dictionaries: ''
```
To accept these unrecognized words as correct (and remove
the previously acknowledged and now absent words),
run the following commands
... in a clone of the
[g...@github.com:SourabhBadhya/hive.git](https://github.com/SourabhBadhya/hive.git)
repository
on the
[jira] [Work logged] (HIVE-26324) Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table
[
https://issues.apache.org/jira/browse/HIVE-26324?focusedWorklogId=781147=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-781147
]
ASF GitHub Bot logged work on HIVE-26324:
-
Author: ASF GitHub Bot
Created on: 14/Jun/22 12:35
Start Date: 14/Jun/22 12:35
Worklog Time Spent: 10m
Work Description: SourabhBadhya opened a new pull request, #3369:
URL: https://github.com/apache/hive/pull/3369
### What changes were proposed in this pull request?
Adding a "one-row-table" constraint on NOTIFICATION_SEQUENCE table.
### Why are the changes needed?
So that NOTIFICATION_SEQUENCE table has only one row.
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
Individually checked the queries on all supported databases.
Issue Time Tracking
---
Worklog Id: (was: 781147)
Remaining Estimate: 0h
Time Spent: 10m
> 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
> Time Spent: 10m
> 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` 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]
> 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)
