MySQL超时参数以及相关数据集成、DataX数据同步案例分享

2021-09-30 20:00:58

一、背景

MySQL系统变量提供关于服务器的一些配置和能力信息，大部分变量可在mysqld服务进程启动时设置，部分变量可在mysqld服务进程运行时设置。合理的系统变量设值范围，是保障MySQL稳定提供服务的重要因素。本文主要描述MySQL数据库的超时timeout相关的一些系统变量，部分参数同程序应用中常见到的CommunicationsException: Communications link failure异常息息相关。

本文也结合数据同步的场景，对使用DataX3进行MySQL数据同步时遇到的几个经典案例进行阐述，期望对遇到类似问题的同学有一定的参考作用。

安利一下：DataX 是一个异构数据源离线同步工具，致力于实现包括关系型数据库(MySQL、Oracle等)、HDFS、Hive、ODPS、HBase、FTP等各种异构数据源之间稳定高效的数据同步功能。详细介绍可见：阿里云开源离线同步工具DataX3.0介绍、开源代码仓库WIKI地址

数据集成是阿里集团对外提供的稳定高效、弹性伸缩的数据同步平台，为阿里云大数据计算引擎(包括 MaxCompute、AnalyticDB等)提供离线(批量)的数据进出通道。DataX是阿里云数加数据集成对外的开源版本，如何大家需要使用一站式的大数据集成方案，可以参见数据集成产品首页、数据集成产品帮助文档

二、超时系统变量介绍

MySQL同超时相关的系统变量名大都包含timeout关键字，使用MySQL show variables 即可查询当前变量值状态，查询SQL以及结果如下：

show variables like '%timeout%';

下面具体介绍各个超时系统变量的含义，内容主要翻译和参考了MySQL官方文档。

1.connect_timeout

connect_timeout是MySQL服务端进程mysqld等待连接建立完成的时间，单位为秒。如果超过connect_timeout时间范围内，仍然无法完成协议握手话，MySQL客户端会收到异常，异常消息类似于: Lost connection to MySQL server at 'XXX', system error: errno. 遇到此类错误，一般可尝试性增加connect_timeout时间的大小。

顺便介绍下MySQL应用协议握手发包过程: MySQL协议握手

以Plain Handshake为例，在TCP三次握手建立后，进入MySQL应用层协议握手阶段，过程如下:

server sending Initial Handshake Packet
client replying with Handshake Response Packet

MySQL服务端会有监听线程循环接收请求，当有客户端请求过来时，会创建线程（或者从线程池中取）来处理这个客户端请求（一般基于网络的应用都是这个套路，监听线程不具体处理客户请求）。由于MySQL连接基于TCP协议，在MySQL应用层协议建立前，需要完成TCP三次握手，在TCP三次握手成功之后，客户端会进入阻塞，等待服务端的消息，进入应用层协议握手阶段。后续服务端会创建线程（或者从线程池中取）来处理请求，这里会进行权限相关的验证，比如host、用户名、密码验证等。host验证即用grant命令授权用户的时候是有指定host的（比如设置了仅允许指定ip的客户端连接数据库）；用户名密码认证则是服务端先生成一个随机数发送给客户端，客户端用该随机数和密码进行多次sha1加密后发送给服务端验证。如果在connect_timeout时间范围内无法完成握手，则报告建立连接异常。

相对于MySQL数据查询Query来说，连接握手相对耗时较少，系统默认的10秒一般足够了，不太会出现需要修改的场景。

2.delayed_insert_timeout

delayed_insert_timeout用于控制MySQL的延迟插入（DELAYED inserts）超时时间，由于在MySQL 5.6.7后，延迟插入不在支持，此系统变量也不建议修改或使用。

3.innodb_lock_wait_timeout

innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用事务失败。参数的时间单位是秒，最小可设置为1s(一般不会设置得这么小)，最大可设置1073741824秒（34年），默认值是50秒。当锁等待超过设置时间的时候，类似会报如下的错误； ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。

innodb_lock_wait_timeout只针对InnoDB存储引擎下的行锁，不影响或限制表锁，可以通过SET GLOBAL 或者 SET SESSION语句在运行时修改此值。

在数据同步场景下，MySQL写出使用了批量batch提交模式，每个batch会开启一个事务的，innodb引擎下，会受到此参数的影响。

4.innodb_rollback_on_timeout

innodb_rollback_on_timeout只针对InnoDB存储引擎，默认为为OFF。ON表示回滚整个事务，OFF表示只回滚最后一条报错的sql。如果事务因为加锁超时，根据innodb_rollback_on_timeout进行回滚（完整事务或最后一条出错SQL），客户端会收到报错异常，用户可以选择是继续提交，或者回滚之前的操作，由用户选择是否进一步提交或者回滚事务。

此系统变量是一个布尔状态的参量，不是超时时间。

5.interactive_timeout

interactive_timeout表示服务器关闭交互式连接前等待活动的秒数，参数默认值28800秒（8小时）。交互式客户端（如mysql命令行）定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。如果某个交互式客户连接在interactive_timeout秒内没有操作动作，MySQL服务器就认为该客户连接不再有保留的必要并自动关闭这个连接。对于非交互式的客户连接，MySQL服务器将使用wait_timeout变量的值作为这种超时等待时间。

一个交互式客户连接断开并重连的例子（mysql命令行客户端）如下：

DataX进行MySQL数据同步时，使用的MySQL驱动是mysql-connector-java 5.1.34版本，属于非交互式客户端。

6.wait_timeout

wait_timeout表示服务器关闭非交互连接之前等待活动的秒数，参数默认值：28800秒（8小时）。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。如果某个非交互的客户连接在wait_timeout秒内没有操作动作，MySQL服务器就将认为该客户连接不再有保留的必要并自动关闭这个连接。对于交互式的客户连接，MySQL服务器使用interactive_timeout变量的值作为这种超时等待的秒数。这个变量仅使用于TCP/IP连接和Unix系统上的套接字文件连接。

DataX进行MySQL数据同步时，使用的MySQL驱动是mysql-connector-java 5.1.34版本，属于非交互式客户端。

这里介绍和wait_timeout相关的2个场景。

6.1MySQL经典的8小时连接超时问题

假设你的数据库是MySQL，如果数据源配置不当，将可能发生经典的8小时问题。原因是MySQL在默认情况下，如果发现一个连接的空闲时间超过8小时，将会在数据库端自动关闭这个连接。而数据源并不知道这个连接已经关闭了，当它将这个无用的连接返回给应用时，应用在此连接上执行的SQL Statement都会失败。一般数据库连接池(如c3p0、dbcp)都有保活机制、或者返回连接前检查连接的可用性。

此问题的相关异常栈一般如下所示：

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.] with root cause
 java.net.SocketException: Software caused connection abort: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3852)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:5484)
at com.mysql.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:5477)
at sun.reflect.GeneratedMethodAccessor145.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)

6.2DataX MySQL数据读取连接断开问题

此系统变量对于DataX数据同步有较大影响。使用DataX进行MySQL数据同步时，DataX是通过JDBC 相关接口发送数据读取SELECT SQL语句，进而读取MySQL数据库表数据的。如果MySQL表过大、或者有不合适的数据抽取控制where条件、或者数据表缺少相关的索引机制，导致数据读取SQL长时间不返回，有可能到达wait_timeout时间限制值，导致连接被MySQL服务端关闭，数据同步任务失败。

此时需要增大wait_timeout大小或者尝试优化数据读取SQL（表索引、有索引列的where条件、减少不必要的函数列等）的性能。

7.lock_wait_timeout

lock_wait_timeout是元数据锁等待超时，任意锁元数据的语句都会用到这个超时参数，默认为一年。为了保证事务可串行化，不管是myisam还是innodb引擎的表，只要是先在一个session里面开启一个事务，就会获取操作表的元数据锁，这时候如果另一个session要对表的元数据进行修改，则会阻塞直到超时。这个超时会影响对表DML/DDL操作、视图、存储过程、存储函数、LOCK TABLES、FLUSH TABLES WITH READ LOCK、HANDLER 语句等。

8.net_read_timeout

在MySQL服务器接收客户数据的场合，如果MySQL服务器在等待了net_read_timeout秒之后仍未收到来自客户连接的数据，就会产生一个读操作超时错误。这种倒计时只适用于TCP/IP连接。

这里介绍一个net_read_timeout相关的异常情景。

8.1超时问题案例

另一个数据同步任务，在数据写DRDS（分布式关系型数据库服务）的过程中，出现连接被关闭异常：

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
        at com.mysql.jdbc.Util.getInstance(Util.java:360)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
        at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1232)
        at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1225)
        at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4568)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.doBatchInsert(CommonRdbmsWriter.java:361)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.startWriteWithConnection(CommonRdbmsWriter.java:291)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.startWrite(CommonRdbmsWriter.java:319)
        at com.alibaba.datax.plugin.writer.mysqlwriter.MysqlWriter$Task.startWrite(MysqlWriter.java:78)
        at com.alibaba.datax.core.taskgroup.runner.WriterRunner.run(WriterRunner.java:56)
        at java.lang.Thread.run(Thread.java:662)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 302,332 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3427)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3327)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4846)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.doBatchInsert(CommonRdbmsWriter.java:348)
        ... 5 more
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:2914)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3337)
        ... 12 more

这里分析判断出现问题的原因是net_read_timeout过小导致的，MySQL服务端没有在net_read_timeout时间范围内读取到客户端MySQL驱动发送过来的数据，导致连接中断。

另外，这类错误不一定是net_read_timeout、net_write_timeout、net_retry_count设置值不合适导致的，也可能是网络基础环境、数据库负载压力、客户端负载压力等有异常情况，遇到问题时需要具体分析找到rootCase。

9. net_write_timeout

在MySQL服务器往客户发送数据的场合，如果MySQL服务器在经过了net_write_timeout秒之后仍未收到来自客户端的响应，就将产生一个写操作超时错误。这种倒计时只适用于TCP/IP连接。

这里介绍一个net_write_timeout相关的异常情景。

9.1超时问题案例

一个数据同步任务，在数据读MySQL的数据过程中，出现连接中断异常，问题报错关键异常信息如下:

2016-08-23 16:16:21.113 [9322429-0-0-reader] INFO  CommonRdbmsReader$Task - Begin to read record by Sql: [select hashcode,address from geohash6_address 
] jdbcUrl:[jdbc:mysql://ip:3306/database?yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].

...

2016-08-23 16:22:54.894 [job-9322429] INFO  LocalJobContainerCommunicator - Total 429248 records, 371587338 bytes | Speed 343.11KB/s, 329 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 44.462s |  All Task WaitReaderTime 333.361s | Percentage 0.00%
2016-08-23 16:22:57.904 [9322429-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 hashcode,address from geohash6_address  具体错误信息为：com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 10 milliseconds ago.  The last packet sent successfully to the server was 396,755 milliseconds ago.
    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.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:662) [na:1.6.0_32]

此DataX任务数据读取SQL为：select hashcode,address from geohash6_address
此DataX任务已经运行一段时间，通过日志可以看到MySQL读取数据情况： Total 429248 records, 371587338 bytes | Speed 343.11KB/s, 329 records/s
在2016-08-23 16:22:57.904时（距离开始发起读取SQL 390秒左右）出现了CommunicationsException异常，上次从服务端获取到数据为10毫秒前，上次驱动发送数据到服务端（即服务端收到确认）396,755毫秒（396秒）前
MySQL服务和超时相关的系统变量如下图：

这里分析判断出现问题的原因是net_write_timeout过小导致的，MySQL服务端没有在net_write_timeout时间范围内获取MySQL驱动的ACK，连接中断。

10.net_retry_count

如果MySQL服务端在读写数据时，出现连接中断，会重试net_retry_count指定的次数。

11.slave-net-timeout

如果从服务器在经过slave-net-timeout秒之后仍未接收到来自主服务器的数据，就将产生一个超时错误，这种倒计时仅仅使用TCP/IP连接。在超时连接错误后，从服务器会尝试重连，第一次尝试重连发生在slave-net-timeout超时触发时，后续的重连间隔、重连次数都有相关的配置可自定义。

三、在数加Data IDE中测试连通性

大数据开发套件（Data IDE），提供了可视化开发界面、离线任务调度运维、快速数据集成、多人协同工作等功能，为您提供一个高效、安全的离线数据开发环境。并且拥有强大的Open API为数据应用开发者提供良好的再创作生态。用户可在其中添加数据源，配置数据同步任务，数据源添加时可进行连通性测试，具体效果如下：

如果您在数据源测试联通时遇到一些问题，大致遇到的情况有：

用户名、或密码错误
由于白名单、或者MySQL数据库host访问限制，不允许连通测试机器访问您的数据库
网络或数据库相关问题，没有在指定时间内3秒联通测试成功并返回
您的数据库连接格式有误，不符合MySQL JDBC URL格式要求
您配置的数据库IP、端口、有防火墙限制、是否路由可达等，此类错误一般异常消息大致如下:

Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

针对数据库连接超时相关，可能遇到的情况有：

1.socket超时

套接字或插座(socket)是一种软件形式的抽象，用于表达两台机器间一个连接的“终端”。针对一个特定的连接，每台机器上都有一个“套接字”，可以想象它们之间有一条虚拟的“线缆”。JAVA有两个基于数据流的套接字类：ServerSocket，服务器用它“侦听”进入的连接；Socket，客户端用它初始一次连接。侦听套接字只能接收新的连接请求，不能接收实际的数据包。
以基于TCP/IP协议栈为例介绍，每一次网络交互的sync和ack都有等待时间的上限。假如建立连接是设置socket超时时间为10s，那么当客户端发送sync之后在10s内没有收到服务端的ack信息就会超时。通常java会抛SocketTimeoutException。socket通信失效后下面除login timeout的其他超时机制均会失效。

socket超时时间可以在连接的url中指定。对于MySQL，可以在JDBC URL中指定socket超时时间，如将设置socket超时为10s：

jdbc:mysql:host:port/database?socketTimeout=10000

2.login超时

login timeout是jdbc尝试连接database最长等待时间。在jdbc和database握手过程中每个阶段都会检查是否超时，如果超时会抛SocketException异常，退出登录连接。可以通过DriverManager.setLoginTimeout(10)设置。

目前阿里云数加平台中，数据源连通性检查MySQL的登录超时时间是3秒。

3. connection超时

对应上面MySQL系统变量的connect_timeout参数

客户端和服务器建立的应用层连接，与服务器端无交互状态的连接，直到被服务器端强制关闭而等待的时间，此参数只对基于TCP/IP或基于Socket通信协议建立的连接才有效，单位为秒。连接的建立涉及到资源的分配，如连接池资源。可以通过连接串设置。如：

jdbc:mysql:host:port/database?connectTimeout=10000

4. statement超时

客户端发送一个sql等待服务器返回最大等待时间，客户端在发送sql之前会设置一个timer，如果在指定等待时间内还未收到服务器的执行结果，则会向服务器发出取消sql执行命令。并抛出SQLException。设置方法：statement.setQueryTimeout(100);注意：如果socket通信阻塞，这个异常是抛不出来的，方法调用会阻塞在向服务器发送取消命令。

码农公寓