解决“通信链路故障"使用 JDBC 和 MySQL [英] Solving a "communications link failure" with JDBC and MySQL
问题描述
我正在尝试连接到本地 MySQL 服务器，但一直出现错误.
这是代码.
公共类连接{公共静态无效主(字符串 [] args){连接 conn = null;尝试 {String 用户名 = "我的用户名";字符串密码 = "myPassword";String url = "jdbc:mysql://localhost:3306/myDatabaseName";Class.forName("com.mysql.jdbc.Driver").newInstance();conn = DriverManager.getConnection(url, userName, password);System.out.println("数据库连接建立");} 捕获(异常 e){System.err.println("无法连接数据库服务器");System.err.println(e.getMessage());e.printStackTrace();} 最后 {如果(连接！= null){尝试 {连接.关闭()；System.out.println("数据库连接终止");} 捕获(异常 e){}}}}}和错误:
无法连接到数据库服务器通讯链路故障最后一个成功发送到服务器的数据包是 0 毫秒前.驱动程序没有收到来自服务器的任何数据包.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:通信链接失败最后一个成功发送到服务器的数据包是 0 毫秒前.驱动程序没有收到来自服务器的任何数据包.在 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)在 sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)在 java.lang.reflect.Constructor.newInstance(Constructor.java:513)在 com.mysql.jdbc.Util.handleNewInstance(Util.java:411)在 com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)在 com.mysql.jdbc.MysqlIO.(MysqlIO.java:344)在 com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2333)在 com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)在 com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)在 com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:792)在 com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)在 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)在 sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)在 java.lang.reflect.Constructor.newInstance(Constructor.java:513)在 com.mysql.jdbc.Util.handleNewInstance(Util.java:411)在 com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)在 com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)在 java.sql.DriverManager.getConnection(DriverManager.java:582)在 java.sql.DriverManager.getConnection(DriverManager.java:185)在 Connect.main(Connect.java:16)引起:java.net.ConnectException:连接被拒绝在 java.net.PlainSocketImpl.socketConnect(Native Method)在 java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:351)在 java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:213)在 java.net.PlainSocketImpl.connect(PlainSocketImpl.java:200)在 java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)在 java.net.Socket.connect(Socket.java:529)在 java.net.Socket.connect(Socket.java:478)在 java.net.Socket.(Socket.java:375)在 java.net.Socket.(Socket.java:218)在 com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)在 com.mysql.jdbc.MysqlIO.(MysqlIO.java:294)……还有 15 个 我已经设置了类路径，确保 my.cnf 已注释掉跳过网络选项.
java 版本是 1.2.0_26(64 位)mysql 5.5.14mysql 连接器 5.1.17
我确保用户可以访问我的数据库.
我在我的两个程序中遇到了同样的问题.我的错误是这样的:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:通信链接失败最后一个成功发送到服务器的数据包是 0 毫秒前.驱动程序没有收到来自服务器的任何数据包.我花了几天时间来解决这个问题.我已经测试了不同网站中提到的许多方法，但没有一个有效.最后我更改了我的代码并找出了问题所在.我将尝试向您介绍不同的方法并在此总结.
当我在互联网上寻找此错误的解决方案时，我发现有许多解决方案至少适用于一个人，但其他人说它对他们不起作用！strong> 为什么有很多方法可以解决这个错误?似乎这个错误通常会在连接到服务器出现问题时发生.问题可能是因为查询字符串错误或数据库连接过多.
所以我建议你一个一个地尝试所有的解决方案，不要放弃！
以下是我在互联网上找到的解决方案，对于每个解决方案，至少有一个人通过该解决方案解决了他的问题.
提示:需要更改MySQL设置的解决方法可以参考以下文件:
Linux:
/etc/mysql/my.cnf或/etc/my.cnf(取决于 Linux 发行版和使用的 MySQL 包)>Windows:
C:\**ProgramData**\MySQL\MySQL Server 5.6\my.ini(注意是 ProgramData，不是 Program Files)
以下是解决方案:
改变
bind-address属性:取消注释
bind-address属性或将其更改为以下 IP 之一:bind-address="127.0.0.1";或
绑定地址=0.0.0.0"；注释掉skip-networking"
如果您的 MySQL 配置文件中有
skip-networking行，请在该行的开头添加#符号以使其成为注释.更改wait_timeout"；和interactive_timeout"
将这些行添加到 MySQL 配置文件中:
[wait_timeout][1] = *number*互动超时 = *数字*连接超时 = *数字*确保 Java 没有将localhost"转换为 [:::1] 而不是 [127.0.0.1]
因为 MySQL 识别
127.0.0.1(IPv4) 但不识别:::1(IPv6)这可以通过使用以下两种方法之一来避免:
在连接字符串中使用
127.0.0.1而不是localhost以避免localhost被转换为:::1使用选项
-Djava.net.preferIPv4Stack=true运行 java 以强制 java 使用IPv4而不是IPv6.在 Linux 上，这也可以通过运行(或将其放在/etc/profile中:export _JAVA_OPTIONS="-Djava.net.preferIPv4Stack=true";
检查操作系统代理设置、防火墙和防病毒程序
确保防火墙或防病毒软件没有阻止 MySQL 服务.
在 linux 上暂时停止 iptables.如果 iptables 配置错误，它们可能允许将 tcp 数据包发送到 mysql 端口，但会阻止 tcp 数据包从同一连接返回.
# Redhat Enterprise 和 CentOSsystemctl 停止 iptables.service# 其他 linux 发行版服务 iptables 停止停止 Windows 上的防病毒软件.
更改连接字符串
检查您的查询字符串.您的连接字符串应该是这样的:
dbName = "my_database";dbUserName = "root";dbPassword = ";String connectionString = "jdbc:mysql://localhost/";+ dbName + ?user="+ dbUserName + &password="+ dbPassword + "&useUnicode=true&characterEncoding=UTF-8";
确保字符串中没有空格.所有的连接字符串都应该继续，没有任何空格字符.
尝试替换localhost"环回地址为 127.0.0.1.还可以尝试将端口号添加到您的连接字符串中，例如:
String connectionString = "jdbc:mysql://localhost:3306/my_database?user=root&password=Pass&useUnicode=true&characterEncoding=UTF-8";通常 MySQL 的默认端口是 3306.
不要忘记将用户名和密码更改为您的 MySQL 服务器的用户名和密码.
- 更新您的 JDK 驱动程序库文件
- 测试不同的 JDK 和 JRE(如 JDK 6 和 7)
- 不要更改 max_allowed_packet
"max_allowed_packet"是 MySQL 配置文件中的一个变量，表示最大数据包大小，而不是最大数据包数.所以它无助于解决这个错误.
- 更改 tomcat 安全性
将 TOMCAT6_SECURITY=yes 更改为 TOMCAT6_SECURITY=no
- 使用validationQuery属性
use validationQuery="select now()";确保每个查询都有响应
- 自动重新连接
将此代码添加到您的连接字符串中:
&autoReconnect=true&failOverReadOnly=false&maxReconnects=10尽管这些解决方案都不适用于我，但我建议您尝试一下.因为有些人按照这些步骤解决了他们的问题.
但是什么解决了我的问题?
我的问题是我在数据库上有很多 SELECT.每次我创建一个连接然后关闭它.虽然我每次都关闭连接，但是系统面临许多连接并给我那个错误.我所做的是将我的连接变量定义为整个类的公共(或私有)变量，并在构造函数中对其进行初始化.然后每次我只使用该连接.它解决了我的问题，还大大提高了我的速度.
#结论#没有简单而独特的方法来解决这个问题.建议您考虑自己的情况，选择以上解决方案.如果您在程序开始时出现此错误并且根本无法连接到数据库，则您的连接字符串可能有问题.但是，如果您在多次成功与数据库交互后出现此错误，则问题可能出在连接数上，您可能会考虑更改wait_timeout"和其他 MySQL 设置或重写您的代码如何减少连接数.
I'm trying to connect to the local MySQL server but I keep getting an error.
Here is the code.
public class Connect {
public static void main(String[] args) {
Connection conn = null;
try {
String userName = "myUsername";
String password = "myPassword";
String url = "jdbc:mysql://localhost:3306/myDatabaseName";
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url, userName, password);
System.out.println("Database connection established");
} catch (Exception e) {
System.err.println("Cannot connect to database server");
System.err.println(e.getMessage());
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
System.out.println("Database Connection Terminated");
} catch (Exception e) {}
}
}
}
}
and the errors :
Cannot connect to database server
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.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 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.
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:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2333)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
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:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at Connect.main(Connect.java:16)
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:351)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:213)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:200)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:529)
at java.net.Socket.connect(Socket.java:478)
at java.net.Socket.<init>(Socket.java:375)
at java.net.Socket.<init>(Socket.java:218)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:294)
... 15 more
I've set the classpath, made sure my.cnf had the skip network option commented out.
java version is 1.2.0_26 (64 bit) mysql 5.5.14 mysql connector 5.1.17
I made sure that the user had access to my database.
I have had the same problem in two of my programs. My error was this:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 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.
I spent several days to solve this problem. I have tested many approaches that have been mentioned in different web sites, but non of them worked. Finally I changed my code and found out what was the problem. I'll try to tell you about different approaches and sum them up here.
While I was seeking the internet to find the solution for this error, I figured out that there are many solutions that worked for at least one person, but others say that it doesn't work for them! why there are many approaches to this error? It seems this error can occur generally when there is a problem in connecting to the server. Maybe the problem is because of the wrong query string or too many connections to the database.
So I suggest you to try all the solutions one by one and don't give up!
Here are the solutions that I found on the internet and for each of them, there is at least on person who his problem has been solved with that solution.
Tip: For the solutions that you need to change the MySQL settings, you can refer to the following files:
Linux:
/etc/mysql/my.cnfor/etc/my.cnf(depending on the Linux distribution and MySQL package used)Windows:
C:\**ProgramData**\MySQL\MySQL Server 5.6\my.ini(Notice it's ProgramData, not Program Files)
Here are the solutions:
changing
bind-addressattribute:Uncomment
bind-addressattribute or change it to one of the following IPs:bind-address="127.0.0.1"or
bind-address="0.0.0.0"commenting out "skip-networking"
If there is a
skip-networkingline in your MySQL config file, make it comment by adding#sign at the beginning of that line.change "wait_timeout" and "interactive_timeout"
Add these lines to the MySQL config file:
[wait_timeout][1] = *number* interactive_timeout = *number* connect_timeout = *number*Make sure Java isn't translating 'localhost' to [:::1] instead of [127.0.0.1]
Since MySQL recognizes
127.0.0.1(IPv4) but not:::1(IPv6)This could be avoided by using one of two approaches:
In the connection string use
127.0.0.1instead oflocalhostto avoidlocalhostbeing translated to:::1Run java with the option
-Djava.net.preferIPv4Stack=trueto force java to useIPv4instead ofIPv6. On Linux, this could also be achieved by running (or placing it inside/etc/profile:export _JAVA_OPTIONS="-Djava.net.preferIPv4Stack=true"
check Operating System proxy settings, firewalls and anti-virus programs
Make sure the Firewall, or Anti-virus software isn't blocking MySQL service.
Stop iptables temporarily on linux. If iptables are misconfigured they may allow tcp packets to be sent to mysql port, but block tcp packets from coming back on the same connection.
# Redhat enterprise and CentOS systemctl stop iptables.service # Other linux distros service iptables stopStop anti-virus software on Windows.
change connection string
Check your query string. your connection string should be some thing like this:
dbName = "my_database"; dbUserName = "root"; dbPassword = ""; String connectionString = "jdbc:mysql://localhost/" + dbName + "?user=" + dbUserName + "&password=" + dbPassword + "&useUnicode=true&characterEncoding=UTF-8";
Make sure you don't have spaces in your string. All the connection string should be continues without any space characters.
Try to replace "localhost" with the loopback address 127.0.0.1. Also try to add port number to your connection string, like:
String connectionString = "jdbc:mysql://localhost:3306/my_database?user=root&password=Pass&useUnicode=true&characterEncoding=UTF-8";
Usually default port for MySQL is 3306.
Don't forget to change username and password to the username and password of your MySQL server.
- update your JDK driver library file
- test different JDK and JREs (like JDK 6 and 7)
- don't change max_allowed_packet
"max_allowed_packet" is a variable in MySQL config file that indicates the maximum packet size, not the maximum number of packets. So it will not help to solve this error.
- change tomcat security
change TOMCAT6_SECURITY=yes to TOMCAT6_SECURITY=no
- use validationQuery property
use validationQuery="select now()" to make sure each query has responses
- AutoReconnect
Add this code to your connection string:
&autoReconnect=true&failOverReadOnly=false&maxReconnects=10
Although non of these solutions worked for me, I suggest you to try them. Because there are some people who solved their problem with following these steps.
But what solved my problem?
My problem was that I had many SELECTs on database. Each time I was creating a connection and then closing it. Although I was closing the connection every time, but the system faced with many connections and gave me that error. What I did was that I defined my connection variable as a public (or private) variable for whole class and initialized it in the constructor. Then every time I just used that connection. It solved my problem and also increased my speed dramatically.
#Conclusion# There is no simple and unique way to solve this problem. I suggest you to think about your own situation and choose above solutions. If you take this error at the beginning of the program and you are not able to connect to the database at all, you might have problem in your connection string. But If you take this error after several successful interaction to the database, the problem might be with number of connections and you may think about changing "wait_timeout" and other MySQL settings or rewrite your code how that reduce number of connections.
这篇关于解决“通信链路故障"使用 JDBC 和 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
