[jira] [Commented] (RANGER-3719) Can not create mysql table with charset utf8mb4.

["kirby zhou (Jira)" <ji...@apache.org>]

    [ 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]  [~madhan@apache.org] [~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)