mysql 8 不兼容zeroDateTimeBehavior=convertToNull的设置

gaslly commented 6 years ago

2018-07-23 19:43:33.566 [job-0] ERROR RetryUtil - Exception when calling callable, 异常Msg:Code:[DBUtilErrorCode-10], Description:[连接数据库失败. 请检查您的账号、密码、数据库名称、IP、Port或者向 DBA 寻求帮助(注意网络环境).]. - 具体错误信息为：java.sql.SQLException: The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'. The value 'convertToNull' is not acceptable. com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-10], Description:[连接数据库失败. 请检查您的账号、密码、数据库名称、IP、Port或者向 DBA 寻求帮助(注意网络环境).]. - 具体错误信息为：java.sql.SQLException: The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'. The value 'convertToNull' is not acceptable. at com.alibaba.datax.common.exception.DataXException.asDataXException(DataXException.java:26) ~[datax-common-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.RdbmsException.asConnException(RdbmsException.java:23) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.DBUtil.connect(DBUtil.java:394) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.DBUtil.connect(DBUtil.java:384) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.DBUtil.access$000(DBUtil.java:22) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.DBUtil$3.call(DBUtil.java:322) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.DBUtil$3.call(DBUtil.java:319) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.common.util.RetryUtil$Retry.call(RetryUtil.java:164) ~[datax-common-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.common.util.RetryUtil$Retry.doRetry(RetryUtil.java:111) ~[datax-common-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.common.util.RetryUtil.executeWithRetry(RetryUtil.java:30) [datax-common-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.DBUtil.getConnection(DBUtil.java:319) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.util.DBUtil.getConnection(DBUtil.java:303) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.reader.util.SingleTableSplitUtil.getPkRange(SingleTableSplitUtil.java:151) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.reader.util.SingleTableSplitUtil.splitSingleTable(SingleTableSplitUtil.java:50) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.reader.util.ReaderSplitUtil.doSplit(ReaderSplitUtil.java:78) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.rdbms.reader.CommonRdbmsReader$Job.split(CommonRdbmsReader.java:103) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.plugin.reader.mysqlreader.MysqlReader$Job.split(MysqlReader.java:49) [mysqlreader-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.core.job.JobContainer.doReaderSplit(JobContainer.java:732) [datax-core-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.core.job.JobContainer.split(JobContainer.java:393) [datax-core-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.core.job.JobContainer.start(JobContainer.java:117) [datax-core-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.core.Engine.start(Engine.java:92) [datax-core-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.core.Engine.entry(Engine.java:171) [datax-core-0.0.1-SNAPSHOT.jar:na] at com.alibaba.datax.core.Engine.main(Engine.java:204) [datax-core-0.0.1-SNAPSHOT.jar:na]

DataBaseType.java复写了jdbc连接参数zeroDateTimeBehavior=convertToNull: public String appendJDBCSuffixForReader(String jdbc) { String result = jdbc; String suffix = null; switch (this) { case MySql: case DRDS: suffix = "yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true"; if (jdbc.contains("?")) { result = jdbc + "&" + suffix; } else { result = jdbc + "?" + suffix; } break; case Oracle: break; case SQLServer: break; case DB2: break; case PostgreSQL: break; case RDBMS: break; default: throw DataXException.asDataXException(DBUtilErrorCode.UNSUPPORTED_TYPE, "unsupported database type."); }

    return result;
}

public String appendJDBCSuffixForWriter(String jdbc) {
    String result = jdbc;
    String suffix = null;
    switch (this) {
        case MySql:
            _suffix = "yearIsDateType=false&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&tinyInt1isBit=false";_
            if (jdbc.contains("?")) {
                result = jdbc + "&" + suffix;
            } else {
                result = jdbc + "?" + suffix;
            }
            break;
        case DRDS:
            suffix = "yearIsDateType=false&zeroDateTimeBehavior=convertToNull";
            if (jdbc.contains("?")) {
                result = jdbc + "&" + suffix;
            } else {
                result = jdbc + "?" + suffix;
            }
            break;
        case Oracle:
            break;
        case SQLServer:
            break;
        case DB2:
            break;
        case PostgreSQL:
            break;
        case RDBMS:
            break;
        default:
            throw DataXException.asDataXException(DBUtilErrorCode.UNSUPPORTED_TYPE, "unsupported database type.");
    }

    return result;
}

mysql 8 zeroDateTimeBehavior的有效值为CONVERT_TO_NULL

bugmeng commented 6 years ago

@gaslly 解决了么？感觉这个东西商业之后就没人管了

maliqiang commented 6 years ago

兄弟，一路爬过来了 @bugmeng

nemosail commented 6 years ago

兄弟，换了mysql connector 驱动后好了吧?

Sod-Momas commented 6 years ago

@nemosail
兄弟，换了mysql connector 驱动后好了吧?

请问怎么更换驱动呢?换个jar包后不需要更换驱动类吗? 5.1的驱动用 com.mysql.jdbc.Driver能用吗?

wangsuiche commented 5 years ago

@nemosail 兄弟，换了mysql connector 驱动后好了吧?

请问怎么更换驱动呢?换个jar包后不需要更换驱动类吗? 5.1的驱动用 com.mysql.jdbc.Driver能用吗?

能用，就是报警告

xmsqh commented 5 years ago

mysql8.0 读和写连不上的情况: 在代码里搜索"mysql-connector-java", 搜出来的pom.xml文件里, "mysql-connector-java"下面version字段里, 从5.1.34改成8.0.15. 大概有 mysqlwriter/pom.xml , mysqlreader/pom.xml 等文件需要改(如果只有mysql 的reader和writer,就只用改这两个就可以了.) 如下:

 <artifactId>mysql-connector-java</artifactId>
  <version>5.1.34</version>

修改成:

 <artifactId>mysql-connector-java</artifactId>
  <version>8.0.15</version>

修改完毕后, 自己执行:

$ cd  {DataX_source_code_home}
$ mvn -U clean package assembly:assembly -Dmaven.test.skip=true

编译完成后, 到 {DataX_source_code_home}/target/datax/datax/ 取编好的datax使用.

xmsqh commented 5 years ago

mysql8.0 读和写连不上的情况: 在代码里搜索"mysql-connector-java", 搜出来的pom.xml文件里, "mysql-connector-java"下面version字段里, 从5.1.34改成8.0.15. 大概有 mysqlwriter/pom.xml , mysqlreader/pom.xml 等文件需要改(如果只有mysql 的reader和writer,就只用改这两个就可以了.) 如下:
 <artifactId>mysql-connector-java</artifactId>
  <version>5.1.34</version>
修改成:
 <artifactId>mysql-connector-java</artifactId>
  <version>8.0.15</version>
修改完毕后, 自己执行:
$ cd  {DataX_source_code_home}
$ mvn -U clean package assembly:assembly -Dmaven.test.skip=true
编译完成后, 到 {DataX_source_code_home}/target/datax/datax/ 取编好的datax使用.

另外还有个编译错误, 说tablestore-streamclient找不到之类的, 修改"otsstreamreader/pom.xml"文件里, 将:

<artifactId>tablestore-streamclient</artifactId>
<version>1.0.0-SNAPSHOT</version>

修改为:

<artifactId>tablestore-streamclient</artifactId>
 <version>1.0.0</version>

HeyChiang commented 3 years ago

哎，这个mysql 8连接补上的问题都搞多久，还没解决。。

779144625 commented 1 year ago

2023.4.11 还没修复

alibaba / DataX

mysql 8 不兼容zeroDateTimeBehavior=convertToNull的设置 #146