Cloudera Docs

Ranger MySQL collation

Upgrading from HDP 2.6.5 to CDP 7.1.7 in some of the environments, the Ranger upgrade may fail. Such a failure can be due to MySQL collation used from the client-side or collation configuration enforced for all the clients from the server-side.

Error message during the upgrade failure: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' SQLException : SQL state: HY000 java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' ErrorCode: 1267

The MySQL collation issue occurs in those DB patches which contain dynamic SQL generation to perform the required DML operations. Some of the database patches that can fail in some environments are:
  • 035-update-schema-for-x-policy.sql
  • 036-denormalize-tag-tables.sql
  • 047-sortorder-column-size.sql

Workaround: You must use the static SQL statements directly rather than generating them dynamically at the runtime.

Solution for DB patch 035-update-schema-for-x-policy.sql:
  1. Open the 035-update-schema-for-x-policy.sql file in any editor.
  2. From the SQL file, remove the code from line 65 to Line 110. For example, see the image below.
  3. Copy the below code and paste the code from line number 65 in the SQL file: ALTER TABLE x_policy_item DROP FOREIGN KEY x_policy_item_FK_added_by_id; ALTER TABLE x_policy_item DROP FOREIGN KEY x_policy_item_FK_policy_id; ALTER TABLE x_policy_item DROP FOREIGN KEY x_policy_item_FK_upd_by_id; ALTER TABLE x_policy_item_access DROP FOREIGN KEY x_policy_item_access_FK_added_by_id; ALTER TABLE x_policy_item_access DROP FOREIGN KEY x_policy_item_access_FK_atd_id; ALTER TABLE x_policy_item_access DROP FOREIGN KEY x_policy_item_access_FK_pi_id; ALTER TABLE x_policy_item_access DROP FOREIGN KEY x_policy_item_access_FK_upd_by_id; ALTER TABLE x_policy_item_condition DROP FOREIGN KEY x_policy_item_condition_FK_added_by_id; ALTER TABLE x_policy_item_condition DROP FOREIGN KEY x_policy_item_condition_FK_pcd_id; ALTER TABLE x_policy_item_condition DROP FOREIGN KEY x_policy_item_condition_FK_pi_id; ALTER TABLE x_policy_item_condition DROP FOREIGN KEY x_policy_item_condition_FK_upd_by_id; ALTER TABLE x_policy_item_datamask DROP FOREIGN KEY x_policy_item_datamask_FK_added_by_id; ALTER TABLE x_policy_item_datamask DROP FOREIGN KEY x_policy_item_datamask_FK_policy_item_id; ALTER TABLE x_policy_item_datamask DROP FOREIGN KEY x_policy_item_datamask_FK_type; ALTER TABLE x_policy_item_datamask DROP FOREIGN KEY x_policy_item_datamask_FK_upd_by_id; ALTER TABLE x_policy_item_group_perm DROP FOREIGN KEY x_policy_item_group_perm_FK_added_by_id; ALTER TABLE x_policy_item_group_perm DROP FOREIGN KEY x_policy_item_group_perm_FK_group_id; ALTER TABLE x_policy_item_group_perm DROP FOREIGN KEY x_policy_item_group_perm_FK_pi_id; ALTER TABLE x_policy_item_group_perm DROP FOREIGN KEY x_policy_item_group_perm_FK_upd_by_id; ALTER TABLE x_policy_item_rowfilter DROP FOREIGN KEY x_policy_item_rowfilter_FK_added_by_id; ALTER TABLE x_policy_item_rowfilter DROP FOREIGN KEY x_policy_item_rowfilter_FK_policy_item_id; ALTER TABLE x_policy_item_rowfilter DROP FOREIGN KEY x_policy_item_rowfilter_FK_upd_by_id; ALTER TABLE x_policy_item_user_perm DROP FOREIGN KEY x_policy_item_user_perm_FK_added_by_id; ALTER TABLE x_policy_item_user_perm DROP FOREIGN KEY x_policy_item_user_perm_FK_pi_id; ALTER TABLE x_policy_item_user_perm DROP FOREIGN KEY x_policy_item_user_perm_FK_upd_by_id; ALTER TABLE x_policy_item_user_perm DROP FOREIGN KEY x_policy_item_user_perm_FK_user_id; ALTER TABLE x_policy_resource DROP FOREIGN KEY x_policy_resource_FK_added_by_id; ALTER TABLE x_policy_resource DROP FOREIGN KEY x_policy_resource_FK_policy_id; ALTER TABLE x_policy_resource DROP FOREIGN KEY x_policy_resource_FK_res_def_id; ALTER TABLE x_policy_resource DROP FOREIGN KEY x_policy_resource_FK_upd_by_id; ALTER TABLE x_policy_resource_map DROP FOREIGN KEY x_policy_resource_map_FK_added_by_id; ALTER TABLE x_policy_resource_map DROP FOREIGN KEY x_policy_resource_map_FK_resource_id; ALTER TABLE x_policy_resource_map DROP FOREIGN KEY x_policy_resource_map_FK_upd_by_id;
  4. Save and close the file.
Solution for DB patch 036-denormalize-tag-tables.sql:
  1. Open the 036-denormalize-tag-tables.sql file in any editor.
  2. From the SQL file, remove the code from line 40 to Line 82. For example, see the image
  3. Copy the below code and paste the code from line number 40 in the SQL file: ALTER TABLE x_service_resource_element DROP FOREIGN KEY x_srvc_res_el_FK_added_by_id; ALTER TABLE x_service_resource_element DROP FOREIGN KEY x_srvc_res_el_FK_res_def_id; ALTER TABLE x_service_resource_element DROP FOREIGN KEY x_srvc_res_el_FK_res_id; ALTER TABLE x_service_resource_element DROP FOREIGN KEY x_srvc_res_el_FK_upd_by_id; ALTER TABLE x_service_resource_element_val DROP FOREIGN KEY x_srvc_res_el_val_FK_add_by_id; ALTER TABLE x_service_resource_element_val DROP FOREIGN KEY x_srvc_res_el_val_FK_res_el_id; ALTER TABLE x_service_resource_element_val DROP FOREIGN KEY x_srvc_res_el_val_FK_upd_by_id; ALTER TABLE x_tag_attr DROP FOREIGN KEY x_tag_attr_FK_added_by_id; ALTER TABLE x_tag_attr DROP FOREIGN KEY x_tag_attr_FK_tag_id; ALTER TABLE x_tag_attr DROP FOREIGN KEY x_tag_attr_FK_upd_by_id; ALTER TABLE x_tag_attr_def DROP FOREIGN KEY x_tag_attr_def_FK_added_by_id; ALTER TABLE x_tag_attr_def DROP FOREIGN KEY x_tag_attr_def_FK_tag_def_id; ALTER TABLE x_tag_attr_def DROP FOREIGN KEY x_tag_attr_def_FK_upd_by_id;
  4. Save and close the file.
Solution for DB patch 047-sortorder-column-size.sql:
  1. Open the 047-sortorder-column-size.sql file in any editor
  2. From the SQL file, remove the code from line 16 to line 44. For example, see the image below.
  3. Copy the below code and paste the code from line number 15 in the SQL file: ALTER TABLE x_access_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_access_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_access_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_access_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_context_enricher_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_context_enricher_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_context_enricher_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_context_enricher_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_datamask_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_datamask_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_datamask_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_datamask_type_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_enum_element_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_enum_element_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_enum_element_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_enum_element_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_condition_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_condition_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_condition_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_condition_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_access MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_access MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_access MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_access MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_access MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_condition MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_condition MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_condition MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_condition MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_condition MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_group_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_group_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_group_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_group_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_group_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_user_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_user_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_user_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_user_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_item_user_perm MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_resource_map MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_resource_map MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_resource_map MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_policy_resource_map MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_resource_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_resource_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_resource_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_resource_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_resource_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_service_config_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_service_config_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_service_config_def MODIFY COLUMN `sort_order` INT DEFAULT 0; ALTER TABLE x_service_config_def MODIFY COLUMN `sort_order` INT DEFAULT 0;
  4. Save and close the file.

    What next: You must continue the upgrade process.