[jira] [Commented] (RANGER-655) Review DB schema for max key length restrictions - MySQL

["Pradeep Agrawal (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/RANGER-655?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15293518#comment-15293518 ] 

Pradeep Agrawal commented on RANGER-655:
----------------------------------------

Usually in MySQL DB; index created on columns which are declared to contain more than 255 characters can cause this issue. 
After reviewing DB Schema of current version(0.6.0) following column length need to be reviewed : 
X_PORTAL_USER.login_id
X_PORTAL_USER.email
X_RESOURCE.policy_name (PS : X_RESOURCE is deprecated and will not be used in 0.6.0 version)




> Review DB schema for max key length restrictions - MySQL
> --------------------------------------------------------
>
>                 Key: RANGER-655
>                 URL: https://issues.apache.org/jira/browse/RANGER-655
>             Project: Ranger
>          Issue Type: Bug
>          Components: admin
>    Affects Versions: 0.5.0
>            Reporter: Madhan Neethiraj
>            Assignee: Pradeep Agrawal
>
> Ranger DB schema creation failed with error "Specified key was too long; max key length is 767 bytes", while installing ranger-0.5 against MySQL 5.5 with Tungsten Replication enabled. 
> {quote}
> 2015-09-10 14:03:43,123 I Executing patch on ranger from file: 009-updated_schema.sql 
> Error executing: CREATE TABLE `x_service` ( `id` bigint(20) NOT NULL AUTO_INCREMENT , `guid` varchar(1024) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, `added_by_id` bigint(20) DEFAULT NULL, `upd_by_id` bigint(20) DEFAULT NULL, `version` bigint(20) DEFAULT NULL, `type` bigint(20) DEFAULT NULL, `name` varchar(512) DEFAULT NULL, `policy_version` bigint(20) DEFAULT NULL, `policy_update_time`datetime DEFAULT NULL, `description` varchar(1024) DEFAULT NULL, `is_enabled` tinyint(1) NOT NULL DEFAULT '0', primary key (`id`), UNIQUE KEY `X_service_name` (`name`), KEY `x_service_added_by_id` (`added_by_id`), KEY `x_service_upd_by_id` (`upd_by_id`), KEY `x_service_cr_time` (`create_time`), KEY `x_service_up_time` (`update_time`), KEY `x_service_type` (`type`), CONSTRAINT `x_service_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), CONSTRAINT `x_service_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`), CONSTRAINT `x_service_FK_type` FOREIGN KEY (`type`) REFERENCES `x_service_def` (`id`) ); 
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes 
> SQLException : SQL state: 42000 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes ErrorCode: 1071 
> 2015-09-10 14:03:43,787 E 009-updated_schema.sql import failed!
> {quote}
> To get the installation to succeed, ranger databases were pre-created by explicitly specifying 'latin1' CHARACTER SET, as shown below:
> {quote}
> create database ranger CHARACTER SET=latin1;
> create database ranger_audit CHARACTER SET=latin1;
> {quote}
> Ranger DB schema (and/or schema documentation or creation scripts) should be reviewed to address this issue.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)