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