[
https://issues.apache.org/jira/browse/RANGER-3719?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17527448#comment-17527448
]
kirby zhou commented on RANGER-3719:
------------------------------------
I created a unittest for test create database with mariadb4j.
Any body can try it with your own env?
> 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
> Attachments: createdb-test.patch
>
>
> 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)
