You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ranger.apache.org by "Pradeep Agrawal (Jira)" <ji...@apache.org> on 2022/09/15 16:03:00 UTC
[jira] [Assigned] (RANGER-3719) Can not create mysql table with charset utf8mb4.
[ https://issues.apache.org/jira/browse/RANGER-3719?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pradeep Agrawal reassigned RANGER-3719:
---------------------------------------
Assignee: Pradeep Agrawal
> 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
> Assignee: Pradeep Agrawal
> Priority: Major
> Attachments: 0001-Add-UnitTest-for-mysql-create-database.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.10#820010)