[ https://issues.apache.org/jira/browse/SENTRY-2024?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16257795#comment-16257795 ]
Na Li commented on SENTRY-2024: ------------------------------- [~spena] This is test on 5.5.56-MariaDB, and it also failed to create the index. Without this fix, some of MySql servers will have this issue. MariaDB [sentryTest]> SELECT @@character_set_database, @@collation_database; +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8 | utf8_general_ci | +--------------------------+----------------------+ 1 row in set (0.00 sec) MariaDB [sentryTest]> SHOW GLOBAL VARIABLES LIKE 'innodb_large%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | OFF | +---------------------+-------+ 1 row in set (0.02 sec) MariaDB [sentryTest]> SELECT @@version; +----------------+ | @@version | +----------------+ | 5.5.56-MariaDB | +----------------+ 1 row in set (0.00 sec) MariaDB [sentryTest]> CREATE TABLE `AUTHZ_PATHS_MAPPING_NOBINARY` -> ( -> `AUTHZ_OBJ_ID` BIGINT NOT NULL AUTO_INCREMENT, -> `AUTHZ_OBJ_NAME` VARCHAR(384) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, -> `CREATE_TIME_MS` BIGINT NOT NULL, -> `AUTHZ_SNAPSHOT_ID` BIGINT NOT NULL, -> CONSTRAINT `AUTHZ_PATHS_MAPPING_PK` PRIMARY KEY (`AUTHZ_OBJ_ID`) -> ) ENGINE=INNODB; Query OK, 0 rows affected (0.09 sec) MariaDB [sentryTest]> CREATE UNIQUE INDEX `AUTHZOBJNAMEID` ON `AUTHZ_PATHS_MAPPING_NOBINARY` (`AUTHZ_OBJ_NAME`, `AUTHZ_SNAPSHOT_ID`); ERROR 1071 (42000): {color:red}Specified key was too long; max key length is 767 bytes{color} MariaDB [sentryTest]> > Specify Char Set for AUTHZ_OBJ_NAME > ----------------------------------- > > Key: SENTRY-2024 > URL: https://issues.apache.org/jira/browse/SENTRY-2024 > Project: Sentry > Issue Type: Bug > Components: Sentry > Affects Versions: 2.0.0 > Reporter: Na Li > Assignee: Na Li > Priority: Critical > Attachments: SENTRY-2024.001.patch, SENTRY-2024.001.patch, > SENTRY-2024.001.patch, SENTRY-2024.002.patch, SENTRY-2024.002.patch > > > AUTHZ_OBJ_NAME has (384) chars. It is constructed by sentry from notification > event as {color:red}DB_NAME + "." + TBL_NAME{color}. To be consistent with > hive, sentry should use the same char set as what's used by DB_NAME and > TBL_NAME > Besides, if its table char set is utf8, the constrain AUTHZOBJNAME will have > error "Specified key was too long; max key length is 767 bytes" for mysql. > The solution is to specify the char set for this field, so it works for mysql > regardless the char set of its table or DB. > Reference: > 1) CREATE TABLE AUTHZ_PATHS_MAPPING > ( > AUTHZ_OBJ_ID BIGINT NOT NULL generated always as identity (start with 1), > AUTHZ_OBJ_NAME VARCHAR({color:red}384{color}), > CREATE_TIME_MS BIGINT NOT NULL > ); > CREATE UNIQUE INDEX AUTHZOBJNAME ON AUTHZ_PATHS_MAPPING > ({color:red}AUTHZ_OBJ_NAME{color}); > 2) AUTHZ_OBJ_NAME is constructed by sentry from notification event as > DB_NAME + "." + TBL_NAME. To be consistent with hive, sentry should use the > same char set as what's used by DB_NAME and TBL_NAME in > NotificationProcessor.processAlterTable(). > 3) hive uses latin1 as default charset for notification event. > CREATE TABLE IF NOT EXISTS `NOTIFICATION_LOG` > ( > `NL_ID` BIGINT(20) NOT NULL, > `EVENT_ID` BIGINT(20) NOT NULL, > `EVENT_TIME` INT(11) NOT NULL, > `EVENT_TYPE` varchar(32) NOT NULL, > `DB_NAME` varchar(128), > `TBL_NAME` varchar(128), > `MESSAGE` mediumtext, > PRIMARY KEY (`NL_ID`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > 4) actual installation shows both fields are in latin1 char set. > SHOW FULL COLUMNS FROM NOTIFICATION_LOG; > +------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ > | Field | Type | Collation | Null | Key | Default | > Extra | Privileges | Comment | > +------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ > | NL_ID | bigint(20) | NULL | NO | PRI | NULL | > | select,insert,update,references | | > | EVENT_ID | bigint(20) | NULL | NO | | NULL | > | select,insert,update,references | | > | EVENT_TIME | int(11) | NULL | NO | | NULL | > | select,insert,update,references | | > | EVENT_TYPE | varchar(32) | latin1_swedish_ci | NO | | NULL | > | select,insert,update,references | | > | DB_NAME | varchar(128) | latin1_swedish_ci | YES | | NULL | > | select,insert,update,references | | > | TBL_NAME | varchar(128) | latin1_swedish_ci | YES | | NULL | > | select,insert,update,references | | > | MESSAGE | mediumtext | latin1_swedish_ci | YES | | NULL | > | select,insert,update,references | | > +------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ -- This message was sent by Atlassian JIRA (v6.4.14#64029)