[
https://issues.apache.org/jira/browse/RANGER-3719?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17524752#comment-17524752
]
kirby zhou commented on RANGER-3719:
------------------------------------
[~bhavikpatel] [[email protected]] [~pradeep]
As discussed in issue https://issues.apache.org/jira/browse/RANGER-3687
Use TEXT to replace big VARCHAR(4000) seems the better way to resolve our
problem.
I checked all VARCHAR(4000) and VARCHAR(2000) columns,
All of them are not indexed.
It seems we can safely convert them to TEXT when create new DB.
But I suggest DO NOT automatically alter schema when we upgrade ranger.
* x_portal_user
** `notes` varchar(4000)
** `other_attributes` varchar(4000)
** `sync_source` varchar(4000)
* xa_access_audit
** `action` varchar(2000)
** `request_data` varchar(2000)
** `resource_path` varchar(2000)
* x_asset
** `descr` varchar(4000)
* x_cred_store
** `descr` varchar(4000)
* x_group
** `descr` varchar(4000)
** `other_attributes` varchar(4000)
** `sync_source` varchar(4000)
* x_user
** `descr` varchar(4000)
** `other_attributes` varchar(4000)
** `sync_source` varchar(4000)
* x_resource
** `res_name` varchar(4000)
** `parent_path` varchar(4000)
** `descr` varchar(4000)
* x_policy
** `policy_options` varchar(4000)
* x_service_config_map
** `config_value` varchar(4000)
* x_tag
** `policy_options` varchar(4000)
* x_policy_ref_resource
** `resource_name` varchar(4000)
* x_policy_ref_access_type
** `access_type_name` varchar(4000)
* x_policy_ref_condition
** `condition_name` varchar(4000)
* x_policy_ref_datamask_type
** `datamask_type_name` varchar(4000)
* x_policy_ref_user
** `user_name` varchar(4000)
* x_policy_ref_group
** `group_name` varchar(4000)
* x_role
** `role_options` varchar(4000)
> Can not create mysql table with charset utf8mb4.
> ------------------------------------------------
>
> Key: RANGER-3719
> URL: https://issues.apache.org/jira/browse/RANGER-3719
> Project: Ranger
> Issue Type: Bug
> Components: admin
> Reporter: kirby zhou
> Priority: Major
>
> Mysql have a limitation - "{*}The maximum row size for the used table type,
> not counting BLOBs, is 65535"{*}
> Our mysql table use too many "VARCHAR(4000)", if mysql charset is "utf8mb4",
> it will exceed the limitation of mysql.
>
> For example
> {code:java}
> ]# mysql
> MariaDB [(none)]> create database ranger_utf8mb4 default charset utf8mb4;
> Query OK, 1 row affected (0.000 sec)
> MariaDB [(none)]> use ranger_utf8mb4
> Database changed
> MariaDB [ranger_utf8mb4]> source optimized/current/ranger_core_db_mysql.sql
> ...
> ERROR 1118 (42000) at line 104 in file:
> 'optimized/current/ranger_core_db_mysql.sql': Row size too large. The maximum
> row size for the used table type, not counting BLOBs, is 65535. This includes
> storage overhead, check the manual. You have to change some columns to TEXT
> or BLOBs
> ...
> 104 CREATE TABLE `x_portal_user` (
> 105 `id` bigint(20) NOT NULL AUTO_INCREMENT,
> 106 `create_time` datetime DEFAULT NULL,
> 107 `update_time` datetime DEFAULT NULL,
> 108 `added_by_id` bigint(20) DEFAULT NULL,
> 109 `upd_by_id` bigint(20) DEFAULT NULL,
> 110 `first_name` varchar(1022) DEFAULT NULL,
> 111 `last_name` varchar(1022) DEFAULT NULL,
> 112 `pub_scr_name` varchar(2048) DEFAULT NULL,
> 113 `login_id` varchar(767) DEFAULT NULL,
> 114 `password` varchar(512) NOT NULL,
> 115 `email` varchar(512) DEFAULT NULL,
> 116 `status` int(11) NOT NULL DEFAULT '0',
> 117 `user_src` int(11) NOT NULL DEFAULT '0',
> 118 `notes` varchar(4000) DEFAULT NULL,
> 119 `other_attributes` varchar(4000) DEFAULT NULL,
> 120 `sync_source` varchar(4000) DEFAULT NULL,
> 121 PRIMARY KEY (`id`),
> 122 UNIQUE KEY `x_portal_user_UK_login_id` (`login_id`),
> 123 UNIQUE KEY `x_portal_user_UK_email` (`email`),
> 124 KEY `x_portal_user_FK_added_by_id` (`added_by_id`),
> 125 KEY `x_portal_user_FK_upd_by_id` (`upd_by_id`),
> 126 KEY `x_portal_user_cr_time` (`create_time`),
> 127 KEY `x_portal_user_up_time` (`update_time`),
> 128 KEY `x_portal_user_name` (`first_name`(767)),
> 129 KEY `x_portal_user_email` (`email`),
> 130 CONSTRAINT `x_portal_user_FK_added_by_id` FOREIGN KEY (`added_by_id`)
> REFERENCES `x_portal_user` (`id`),
> 131 CONSTRAINT `x_portal_user_FK_upd_by_id` FOREIGN KEY (`upd_by_id`)
> REFERENCES `x_portal_user` (`id`)
> 132 ) ROW_FORMAT=DYNAMIC;
> {code}
> My suggestion is to either change all fields that are not indexed to TEXT, or
> fix the character set of 'create database' to utf8mb3.
>
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
