Illegal mix of collations utf8mb4_general_ci,COERCIBLE and utf8mb4_0900_ai_ci,COERCIBLE for operation
search cancel

Illegal mix of collations utf8mb4_general_ci,COERCIBLE and utf8mb4_0900_ai_ci,COERCIBLE for operation

book

Article ID: 390230

calendar_today

Updated On:

Products

VMware Tanzu Spring Runtime

Issue/Introduction

This behavior is encountered after upgrading MySQL from v5.7 to v8.0. The service instance was created from Spring Cloud Data Flow Tile v1.14.3 (Dataflow v2.11.4)

The issue is caused by a view of tables created from an older version of MySQL (v5.x) using a string.

Environment

  • Tanzu Platform for Cloud Foundry 
  • Spring Cloud Data Flow for TPCF v1.14.3 and later

Cause

 

 

Resolution

To solve the issue, upgrade to SCDF 1.14.7.

In the meantime, you can execute the following steps as a workaround:

 

    1. Connect to the backend dataflow database (df-relational). Make sure to target the database with step 9: 
      mysql > use service_instance_db
      .
    2. Create the fix.sql script. Copy and paste (you may need to exit from the mysql prompt)
      *Note: The below script will make a file in the /tmp directory. 
      cat <<'EOF' > /tmp/fix.sql

/* In order to set a controlled default charset/collation for the string constants  */
/* ('boot2' and 'boot3') in the view select clause, we set a couple of variables    */
/* and then set them back to their original values after the views are updated.     */
SET @saved_character_set_client = @@character_set_client;
SET @saved_collation_connection = @@collation_connection;
SET character_set_client = utf8mb4;
SET collation_connection = utf8mb4_0900_ai_ci;


ALTER VIEW AGGREGATE_TASK_EXECUTION AS
SELECT TASK_EXECUTION_ID, START_TIME, END_TIME, TASK_NAME, EXIT_CODE, EXIT_MESSAGE, ERROR_MESSAGE, LAST_UPDATED, EXTERNAL_EXECUTION_ID, PARENT_EXECUTION_ID, 'boot2' AS SCHEMA_TARGET FROM TASK_EXECUTION
UNION ALL
SELECT TASK_EXECUTION_ID, START_TIME, END_TIME, TASK_NAME, EXIT_CODE, EXIT_MESSAGE, ERROR_MESSAGE, LAST_UPDATED, EXTERNAL_EXECUTION_ID, PARENT_EXECUTION_ID, 'boot3' AS SCHEMA_TARGET FROM BOOT3_TASK_EXECUTION;

ALTER VIEW AGGREGATE_TASK_EXECUTION_PARAMS AS
SELECT TASK_EXECUTION_ID, TASK_PARAM, 'boot2' AS SCHEMA_TARGET FROM TASK_EXECUTION_PARAMS
UNION ALL
SELECT TASK_EXECUTION_ID, TASK_PARAM, 'boot3' AS SCHEMA_TARGET FROM BOOT3_TASK_EXECUTION_PARAMS;

ALTER VIEW AGGREGATE_JOB_EXECUTION AS
SELECT JOB_EXECUTION_ID, VERSION, JOB_INSTANCE_ID, CREATE_TIME, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, LAST_UPDATED, 'boot2' AS SCHEMA_TARGET FROM BATCH_JOB_EXECUTION
UNION ALL
SELECT JOB_EXECUTION_ID, VERSION, JOB_INSTANCE_ID, CREATE_TIME, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, LAST_UPDATED, 'boot3' AS SCHEMA_TARGET FROM BOOT3_BATCH_JOB_EXECUTION;

ALTER VIEW AGGREGATE_JOB_INSTANCE AS
SELECT JOB_INSTANCE_ID, VERSION, JOB_NAME, JOB_KEY, 'boot2' AS SCHEMA_TARGET FROM BATCH_JOB_INSTANCE
UNION ALL
SELECT JOB_INSTANCE_ID, VERSION, JOB_NAME, JOB_KEY, 'boot3' AS SCHEMA_TARGET FROM BOOT3_BATCH_JOB_INSTANCE;

ALTER VIEW AGGREGATE_TASK_BATCH AS
SELECT TASK_EXECUTION_ID, JOB_EXECUTION_ID, 'boot2' AS SCHEMA_TARGET FROM TASK_TASK_BATCH
UNION ALL
SELECT TASK_EXECUTION_ID, JOB_EXECUTION_ID, 'boot3' AS SCHEMA_TARGET FROM BOOT3_TASK_TASK_BATCH;

ALTER VIEW AGGREGATE_STEP_EXECUTION AS
SELECT STEP_EXECUTION_ID, VERSION, STEP_NAME, JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, COMMIT_COUNT, READ_COUNT, FILTER_COUNT, WRITE_COUNT, READ_SKIP_COUNT, WRITE_SKIP_COUNT, PROCESS_SKIP_COUNT, ROLLBACK_COUNT, EXIT_CODE, EXIT_MESSAGE, LAST_UPDATED, 'boot2' AS SCHEMA_TARGET FROM BATCH_STEP_EXECUTION
UNION ALL
SELECT STEP_EXECUTION_ID, VERSION, STEP_NAME, JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, COMMIT_COUNT, READ_COUNT, FILTER_COUNT, WRITE_COUNT, READ_SKIP_COUNT, WRITE_SKIP_COUNT, PROCESS_SKIP_COUNT, ROLLBACK_COUNT, EXIT_CODE, EXIT_MESSAGE, LAST_UPDATED, 'boot3' AS SCHEMA_TARGET FROM BOOT3_BATCH_STEP_EXECUTION;


/* Restore the settings to their original values */
SET character_set_client = @saved_character_set_client;
SET collation_connection = @saved_collation_connection;

EOF
    3. Run the script
      • mysql> source /tmp/fix.sql
    4. Open SCDF dashboard an select a task to test . If error persist please contact Broadcom Support.


Note: This issue is resolved in Data Flow v2.11.7



Additional Information

It is recommended to capture a backup of mysql before proceeding with any changes.  This will allow to quickly restore any unintentional/undesired changes.

Powered by Wolken Software