doris-flink-connector 进行整库同步时报错:Text '0000-00-00' could not be parsed: Invalid value for MonthOfYear

Viewed 92
CREATE TABLE IF NOT EXISTS xxx.asku (
    id BIGINT,
    date DATEV2 DEFAULT '0000-00-00',
    k VARCHAR(192) DEFAULT '',
    store_id BIGINT DEFAULT '0',
    sku_id BIGINT DEFAULT '0',
    product_id BIGINT DEFAULT '0',
    tenant_id VARCHAR(36) DEFAULT '',
    day_quantity_ordered INT DEFAULT '0',
    day7_quantity_ordered INT DEFAULT '0',
    day14_quantity_ordered INT DEFAULT '0',
    day30_quantity_ordered INT DEFAULT '0',
    week_quantity_ordered INT DEFAULT '0',
    month_quantity_ordered INT DEFAULT '0',
    today_total_price DECIMALV3(20,2) DEFAULT '0.00',
    day7_total_price DECIMALV3(20,2) DEFAULT '0.00',
    day14_total_price DECIMALV3(20,2) DEFAULT '0.00',
    day30_total_price DECIMALV3(20,2) DEFAULT '0.00',
    week_total_price DECIMALV3(20,2) DEFAULT '0.00',
    month_total_price DECIMALV3(20,2) DEFAULT '0.00',
    today_tax DECIMALV3(20,2) DEFAULT '0.00',
    shipping_price DECIMALV3(20,2) DEFAULT '0.00',
    shipping_tax DECIMALV3(20,2) DEFAULT '0.00',
    gift_wrap_price DECIMALV3(20,2) DEFAULT '0.00',
    gift_wrap_tax DECIMALV3(20,2) DEFAULT '0.00',
    promotion_discount DECIMALV3(20,2) DEFAULT '0.00',
    ship_promotion_discount DECIMALV3(20,2) DEFAULT '0.00',
    can_sale_quantity INT DEFAULT '0',
    fba_on_way_quantity INT DEFAULT '0',
    fba_contains_on_way_quantity INT DEFAULT '0',
    working_quantity INT DEFAULT '0',
    inv_age_three_months_less_days INT DEFAULT '0',
    inv_age_three_six_months_days INT DEFAULT '0',
    inv_age_six_nine_months_days INT DEFAULT '0',
    inv_age_nine_twelve_months_days INT DEFAULT '0',
    inv_age_twelve_months_plus_days INT DEFAULT '0',
    inv_age_earlier_six_month INT DEFAULT '0',
    afn_unsellable_quantity INT DEFAULT '0',
    mfn_fulfillable_quantity INT DEFAULT '0',
    afn_warehouse_quantity INT DEFAULT '0',
    afn_fulfillable_quantity INT DEFAULT '0',
    afn_reserved_quantity INT DEFAULT '0',
    afn_total_quantity INT DEFAULT '0',
    afn_inbound_receiving_quantity INT DEFAULT '0',
    reserved_qty INT DEFAULT '0',
    reserved_customer_orders INT DEFAULT '0',
    reserved_fc_transfers INT DEFAULT '0',
    reserved_fc_processing INT DEFAULT '0',
    UNIQUE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS AUTO PROPERTIES ('replication_num'='1','light_schema_change'='true');
);

('replication_num'='1','light_schema_change'='true');

at org.apache.doris.flink.catalog.doris.DorisSystem.execute(DorisSystem.java:131)
at org.apache.doris.flink.catalog.doris.DorisSystem.createTable(DorisSystem.java:122)
at org.apache.doris.flink.tools.cdc.DatabaseSync.build(DatabaseSync.java:145)
at org.apache.doris.flink.tools.cdc.CdcTools.syncDatabase(CdcTools.java:146)
at org.apache.doris.flink.tools.cdc.CdcTools.createMySQLSyncDatabase(CdcTools.java:74)
at org.apache.doris.flink.tools.cdc.CdcTools.main(CdcTools.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.flink.client.program.PackagedProgram.callMainMethod(PackagedProgram.java:355)
... 12 more
Caused by: java.sql.SQLException: errCode = 2, detailMessage = date literal [0000-00-00] is invalid: Text '0000-00-00' could not be parsed: Invalid value for MonthOfYear (valid values 1 - 12): 0
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at org.apache.doris.flink.catalog.doris.DorisSystem.execute(DorisSystem.java:128)
... 22 more

1 Answers

解决方法:

  1. flink-connector-doris-1.5.2 新增同步mysql,pg,oracle,mssql默认值到Doris, 本身default '0000-00-00'这种默认值格式Apache Doris不支持,flink-connector-doris之后版本已经在修复这个问题,会拦截这种格式,所以当前可以使用flink-connector-1.5.1 去同步。
  2. 更改mysql中default ''格式

预计Flink-connector-doris-1.6将会适配更多的mysql,pg,oracle,mssql的默认值格式,并且映射到Doris. 对于不符合Doris默认值格式的,将会置为null.