DDL statement replicated as UTF16

[ssrlv]

mariaDB source has following DDL statement issued 

alter table idcdm.idcdm_transaction_snapshot
add column benefit_type_description varchar(500); 

The target mariaDB table ends up with this column definition for the new column:

  `benefit_type_description` varchar(500) CHARACTER SET utf16 DEFAULT NULL

   this characterset is causing issues and was not part of the column definition.  The characterset for the table is latin1 

[Steve_Nguyen · Accepted solution]

couple of option here:

1. create a test task with the same table, going to a test target, and check to see if the table get create, and what is the characterset for ? utf16 or latin?

2. or you can manually change the target table to math source characterset 

[john_wang]

Hello @ssrlv ,

If there is no explicit CHARACTER SET in use then the table's default charset inherits DATABASE's charset; column's default charset inherits table's charset. 

However in Replicate MariaDB/MySQL target endpoint syntax, the explicit CHARACTER SET is in use, for example the VARCHAR data type:

"provider_data_type":	"VARCHAR(${LENGTH}) CHARACTER SET utf16",

 it's same for other STRING data types. That's why you got the "CHARACTER SET utf16" definition in target table column(s).

If you want to use fixed charset "latin1" then you can  custom target endpoint syntax  by replacing all utf16 by latin1.

Another option is you remove the "CHARACTER SET utf16" definition from the syntax, then you create the database manually and set its default charset to LATIN1 then Replicate will inherit the default charset in both table level, and column level.

Hope this helps.

Regards,

John.