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

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

kirby zhou created RANGER-3719:
----------------------------------

             Summary: 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


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.1#820001)