ranger-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pradeep Agrawal (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (RANGER-655) Review DB schema for max key length restrictions - MySQL
Date Mon, 23 Jan 2017 07:01:26 GMT

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

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

Issue can be resolved after [commit|https://github.com/apache/incubator-ranger/commit/c19ec875742665ab96577f5a27ebac2da036d3bc]
User need to refer below instructions to handle key length issue in UTF charset db environment.

Note : MySQL server 5.6 or higher and MySQL connector 5.1.13 or higher should be used.

=>To enable UTF support from MySQL DB following steps need to be done :

Create a backup of all the databases on the server you want to upgrade.
Add following properties in respective section of my.cnf
[mysqld]
character-set-client-handshake = 0
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_file_per_table=true
innodb_file_format=BARRACUDA
innodb_large_prefix

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

Restart MySQL Server.

=>If user is already using Ranger on MySQL 5.6 then apart from above steps following steps
need to be done to change charset of ranger db to UTF8.

Stop Ranger admin.
Modify databases, tables, and columns : Change the character set and collation properties
of the databases, tables, and columns to use utf8mb4 instead of utf8.
# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name ROW_FORMAT = DYNAMIC;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# Start Ranger admin


> 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)

Mime
View raw message