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. Service instance was created from Spring Cloud Data Flow Tile v1.14.3 (Dataflow v2.11.4)

The issue is cause by a view of tables create 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