[
https://issues.apache.org/jira/browse/SENTRY-1648?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15905632#comment-15905632
]
BELUGA BEHR edited comment on SENTRY-1648 at 3/10/17 8:06 PM:
--------------------------------------------------------------
This is even more important when UTF-8 is being used. {{`URI`(250)}} in UTF-8
is 1000 bytes, way more than will be supported in MySQL or Oracle for index key
length.
Oracle: java.sql.SQLException: ORA-01450: maximum key length (6398) exceeded
was (Author: belugabehr):
This is even more important when UTF-8 is being used. {{`URI`(250)}} in UTF-8
is 1000 bytes, way more than will be supported in MySQL or Oracle for index key
length.
> Sentry MySQL "Unique" Index
> ---------------------------
>
> Key: SENTRY-1648
> URL: https://issues.apache.org/jira/browse/SENTRY-1648
> Project: Sentry
> Issue Type: Improvement
> Affects Versions: 1.8.0
> Reporter: BELUGA BEHR
> Priority: Minor
>
> {code:sql|title=sentry-mysql-1.8.0.sql}
> CREATE TABLE `SENTRY_DB_PRIVILEGE` (
> `DB_PRIVILEGE_ID` BIGINT NOT NULL,
> `PRIVILEGE_SCOPE` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
> `SERVER_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
> `DB_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT
> '__NULL__',
> `TABLE_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT
> '__NULL__',
> `COLUMN_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT
> '__NULL__',
> `URI` VARCHAR(4000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '__NULL__',
> `ACTION` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
> `CREATE_TIME` BIGINT NOT NULL,
> `WITH_GRANT_OPTION` CHAR(1) NOT NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> ALTER TABLE `SENTRY_DB_PRIVILEGE`
> ADD UNIQUE `SENTRY_DB_PRIV_PRIV_NAME_UNIQ`
> (`SERVER_NAME`,`DB_NAME`,`TABLE_NAME`,`COLUMN_NAME`,`URI`(250),`ACTION`,`WITH_GRANT_OPTION`);
> {code}
> As you can see, only the first 250 characters of URI is considered when
> determining "uniqueness". Typically, a second column would be added
> containing the hash value (MD5/SHA) of the URI and that column would instead
> be used as part of the unique index instead of the field itself.
> Oracle would also benefit from this.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
