Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

同步Mysql一张千万级别数据量的表时报错中断当前作业，求助！！ #2255

Open
COLACHENXH opened this issue Dec 18, 2024 · 8 comments
Open

同步Mysql一张千万级别数据量的表时报错中断当前作业，求助！！ #2255

COLACHENXH opened this issue Dec 18, 2024 · 8 comments

Comments

@COLACHENXH
Copy link

COLACHENXH commented Dec 18, 2024 •
edited
Loading

我在同步Mysql一张千万级别数据量的表的过程中，报错中断当前作业，报错如下所示：
2024-12-18 15:01:09.221 [job-0] INFO StandAloneJobContainerCommunicator - Total 4997632 records, 681895645 bytes | Speed 982.81KB/s, 7372 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 646.353s | All Task WaitReaderTime 17.761s | Percentage 0.00%
2024-12-18 15:01:19.222 [job-0] INFO StandAloneJobContainerCommunicator - Total 5069312 records, 691674589 bytes | Speed 954.97KB/s, 7168 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 655.930s | All Task WaitReaderTime 18.029s | Percentage 0.00%
2024-12-18 15:01:29.223 [job-0] INFO StandAloneJobContainerCommunicator - Total 5145088 records, 702014443 bytes | Speed 1009.75KB/s, 7577 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 665.592s | All Task WaitReaderTime 18.305s | Percentage 0.00%
2024-12-18 15:01:39.224 [job-0] INFO StandAloneJobContainerCommunicator - Total 5220864 records, 712353534 bytes | Speed 1009.68KB/s, 7577 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 675.304s | All Task WaitReaderTime 18.567s | Percentage 0.00%
2024-12-18 15:01:41.005 [0-0-0-reader] ERROR ReaderRunner - Reader runner Received Exceptions:
com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-07], Description:[读取数据库数据失败. 请检查您的配置的 column/table/where/querySql或者向 DBA 寻求帮助.]. - 执行的SQL为: select …… 具体错误信息为：com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server. - com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3905)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1999)
at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:374)
at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:354)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6313)
at com.alibaba.datax.plugin.rdbms.reader.CommonRdbmsReader$Task.startRead(CommonRdbmsReader.java:207)
at com.alibaba.datax.plugin.reader.mysqlreader.MysqlReader$Task.startRead(MysqlReader.java:81)
at com.alibaba.datax.core.taskgroup.runner.ReaderRunner.run(ReaderRunner.java:57)
at java.lang.Thread.run(Thread.java:750)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3014)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3472)
... 11 more

    at com.alibaba.datax.common.exception.DataXException.asDataXException(DataXException.java:41) ~[datax-common-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.util.RdbmsException.asQueryException(RdbmsException.java:81) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.reader.CommonRdbmsReader$Task.startRead(CommonRdbmsReader.java:220) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.reader.mysqlreader.MysqlReader$Task.startRead(MysqlReader.java:81) ~[mysqlreader-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.taskgroup.runner.ReaderRunner.run(ReaderRunner.java:57) ~[datax-core-0.0.1-SNAPSHOT.jar:na]
    at java.lang.Thread.run(Thread.java:750) [na:1.8.0_392]

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_392]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_392]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_392]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_392]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3905) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1999) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:374) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:354) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6313) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.alibaba.datax.plugin.rdbms.reader.CommonRdbmsReader$Task.startRead(CommonRdbmsReader.java:207) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
... 3 common frames omitted
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3014) ~[mysql-connector-java-5.1.47.jar:5.1.47]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3472) ~[mysql-connector-java-5.1.47.jar:5.1.47]
... 11 common frames omitted

根据提示信息我在MysqlReader中的jdbcUrl后添加了net_write_timeout参数，但是再次执行发现并没有什么效果，求助！！
@COLACHENXH COLACHENXH changed the title 同步Mysql一张千万级别数据量的表时报错中断当前作业，麻烦看下原因 同步Mysql一张千万级别数据量的表时报错中断当前作业，求助！！ Dec 18, 2024
@COLACHENXH COLACHENXH reopened this Dec 18, 2024
@mylemons
Copy link

看着像网络超时，看看 SHOW VARIABLES LIKE 'net_write_timeout'; 这个是多少，试着把这个调大一些

@COLACHENXH
Copy link
Author

看着像网络超时，看看 SHOW VARIABLES LIKE 'net_write_timeout'; 这个是多少，试着把这个调大一些

这个参数调大10倍了，再次执行结果是一样的

@COLACHENXH
Copy link
Author

datax是否有作业本身的超时时间配置项可以配置？因为感觉每次从作业执行开始到报错结束的总时长基本上是一样的

@leiandpan
Copy link

这个问题我在使用Python向Mysql插入数据时也遇到过，在URl里配置没有效果，但是在执行插入操作前在会话级别执行
SET NET_WRITE_TIMEOUT = 3600
可以生效，
不太清楚DataX的pre_sql参数能不能支持会话级别的参数配置吗

@V-I-C-T-O-R
Copy link

微信截图_20241224151626
根据示例配置修改

@COLACHENXH
Copy link
Author

这个问题我在使用Python向Mysql插入数据时也遇到过，在URl里配置没有效果，但是在执行插入操作前在会话级别执行 SET NET_WRITE_TIMEOUT = 3600 可以生效， 不太清楚DataX的pre_sql参数能不能支持会话级别的参数配置吗

目前感觉不是这个参数的原因哦，因为已经将数据库的这个配置项全局放大10倍了，再次执行还是一样的效果

@zhang69
Copy link

zhang69 commented Dec 31, 2024

这个问题有没有解决，我也遇到了一样的问题，通过修改数据库的global参数、通过jdbcurl连接串这些都没法解决这个问题，在差不多10分钟的时候就会报这个错误

@COLACHENXH
Copy link
Author

这个问题有没有解决，我也遇到了一样的问题，通过修改数据库的global参数、通过jdbcurl连接串这些都没法解决这个问题，在差不多10分钟的时候就会报这个错误

还没解决哦，我目前的情况是在本地尝试同步是好的，在服务器上跑10来分钟就不行了

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@V-I-C-T-O-R @zhang69 @COLACHENXH @leiandpan @mylemons