What Should I Do If an Error Is Reported During DDL Synchronization of New Columns in a Real-Time MySQL-to-DWS Synchronization Job?

Symptom

1. Run the real-time synchronization job of the migration mysql2dws link. During DDL synchronization, set the column adding operation to normal processing.

2. If the destination DWS table contains data, run the following DDL statement in the source MySQL database to add a column with a non-null constraint (the default value is an empty string): alter table test add column t_col varchar(30) not null default.

3. The migration job is abnormal, and an error message is displayed, indicating that the DDL statement fails to be executed. The failure cause is: column "t_col" contains null values.

Possible Cause

If the DWS database is compatible with Oracle, an empty string is processed as null. If there is data, a non-null column whose default value is an empty string cannot be added.

Solution

1. Modify the source DDL statement and set the default value of the new column to a non-null string.

2. If the DDL cannot be modified, replace the DWS database with a MySQL-compatible database, which can be created using the following statement: create database bigdata with encoding 'UTF-8' dbcompatibility 'mysql' template template0;