admin管理员组文章数量:1618729
前天线上出现数据数据抖动,收到大量异常告警,都要疯了,数据库出现大量异常,肯定第一件事就是看下数据库监控数据,发现有一个从分片执行时间有一个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.SocketTimeoutException: Read timed out
at java.SocketInputStream.socketRead0(Native Method)
at java.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.SocketInputStream.read(SocketInputStream.java:171)
at java.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源码看下具体的策略是什么样的,等我吧!
本文标签: 异常mysqlCommunicationsfailureLINK
版权声明:本文标题:Mysql异常之Communications link failure 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/xitong/1728783494a1173025.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论