[RANGER-3680] mysql ErrorCode:1118 when Importing DB schema to database

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 2.2.0
• Fix Version/s: 3.0.0, 2.4.0
• Component/s: admin
• Environment: os: centos 7 ; mysql: 5.7.30 ; jdk: 1.8.0_251
• Assignee: Pradeep Agrawal
• Reporter: vesence
• Created: 24/Mar/22 03:23
• Updated: 21/Sep/22 07:14
• Resolved: 21/Sep/22 07:13

Description

ranger_core_db_mysql.sql file import failed when initializing admin database, here is the specific imformation below:

[I] Importing DB schema to database bdp_ranger from file: ranger_core_db_mysql.sql

Error executing: 

CREATE TABLE `x_portal_user` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   
`create_time` datetime DEFAULT NULL,   
`update_time` datetime DEFAULT NULL,   
`added_by_id` bigint(20) DEFAULT NULL,   
`upd_by_id` bigint(20) DEFAULT NULL,   
`first_name` varchar(1022) DEFAULT NULL,   
`last_name` varchar(1022) DEFAULT NULL,   
`pub_scr_name` varchar(2048) DEFAULT NULL,   
`login_id` varchar(767) DEFAULT NULL,   
`password` varchar(512) NOT NULL,   
`email` varchar(512) DEFAULT NULL,   
`status` int(11) NOT NULL DEFAULT '0',   
`user_src` int(11) NOT NULL DEFAULT '0',   
`notes` varchar(4000) DEFAULT NULL,   
`other_attributes` varchar(4000) DEFAULT NULL,   
`sync_source` varchar(4000) DEFAULT NULL,   
PRIMARY KEY (`id`),   
UNIQUE KEY `x_portal_user_UK_login_id` (`login_id`),   
UNIQUE KEY `x_portal_user_UK_email` (`email`),   
KEY `x_portal_user_FK_added_by_id` (`added_by_id`),   
KEY `x_portal_user_FK_upd_by_id` (`upd_by_id`),   
KEY `x_portal_user_cr_time` (`create_time`),   
KEY `x_portal_user_up_time` (`update_time`),   
KEY `x_portal_user_name` (`first_name`(767)),   
KEY `x_portal_user_email` (`email`),   
CONSTRAINT `x_portal_user_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),   
CONSTRAINT `x_portal_user_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) ) ROW_FORMAT=DYNAMIC; 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
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

SQLException : SQL state: 42000 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ErrorCode: 1118

 

[E] ranger_core_db_mysql.sql file import failed!

 

Issue Links

• duplicates: RANGER-3914 Change sync_source column's datatype from varchar to text
• is related to: RANGER-3394 Too much `varchar(4000)` causes table to exceed ROW SIZE limit in MySQL
• relates to: RANGER-3719 Can not create mysql table with charset utf8mb4.