Mysql异常之Communications link failure

前天线上出现数据数据抖动，收到大量异常告警，都要疯了，数据库出现大量异常，肯定第一件事就是看下数据库监控数据，发现有一个从分片执行时间有一个50多秒的操作，这里肯定有问题了，第一时间找了dba，经过排查发现这个分片的磁盘出现了问题，dba加入了一个新的分片，有问题的分片作摘除处理，这件事才告一段落，但是通过查看服务器日志，发现抛出了大量的CommunicationsException , 关键描述信息：Communications link failure.......Last packet sent to the server was 3003 ms ago。

看到3003这个时间好熟悉啊，我们数据连接属性配置了socketTimeout=3000，mysql客户端与数据库的通信使用的socket，客户端向mysql server发送命令，mysql客户端有程序保护机制，如果由于网络原因或者数据库故障导致数据始终没有返回处理结果，为了防止客户端一直无限等待mysql server返回结果而造成线程阻塞浪费资源，那么socketTimeout参数就起作用了，超过socketTimeout还没有相应，那么就将请求结束，所以抛出了CommunicationsException。为了验证这个异常，在本地环境还原了这个场景。

1.原始数据库操作，构建一个慢写入操作

public class Test {
    private static Connection conn;
    private static Statement stmt;
    private static boolean rs;
    public static void main(String[] args) throws SQLException {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            // 配置socketTimeout=500，单位是ms
            conn= DriverManager.getConnection("jdbc:mysql://192.168.88.130:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT&socketTimeout=500","root","123456");
            stmt=conn.createStatement();
        }
        catch(SQLException e)
        {
      
            e.printStackTrace();
        }
        catch(ClassNotFoundException e)
        {
            
            e.printStackTrace();
        }
        StringBuilder sql = new StringBuilder("insert into test1(a) values");
        // 构建一个慢写入操作，使造作时间超过socketTimeout=500ms        
        for (int i = 0; i < 100000;i++) {
            sql.append("(" + i + "),");
        }
        sql.append("(10000000);");
        rs = stmt.execute(sql.toString());
    }
}

2.果然抛出了异常

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 537 milliseconds ago.  The last packet sent successfully to the server was 512 milliseconds ago.
	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:403)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3517)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3417)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3860)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:829)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:729)
	at com.jd.poporder.test.Test.main(Test.java:39)
Caused by: java.net.SocketTimeoutException: Read timed out
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
	at java.net.SocketInputStream.read(SocketInputStream.java:171)
	at java.net.SocketInputStream.read(SocketInputStream.java:141)
	at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
	at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
	at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2966)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3427)
	... 9 more

Process finished with exit code 1

3.从异常可以看出触发异常的原因是：Read timed out

今天先写到这里，改天有时间需要趴下mysql driver源码看下具体的策略是什么样的，等我吧！