JDBC 및 MySQL로 "통신 링크 실패"해결
로컬 MySQL 서버에 연결하려고하는데 오류가 계속 발생합니다.코드는 다음과 같습니다.
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) {}
}
}
}
}
그리고 오류 :
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
클래스 경로를 설정하고 my.cnf에 네트워크 건너 뛰기 옵션이 주석 처리되어 있는지 확인했습니다. Java 버전은 1.2.0_26 (64 비트)입니다. mysql 5.5.14 mysql 커넥터 5.1.17사용자가 내 데이터베이스에 액세스 할 수 있는지 확인했습니다.
두 프로그램에서 같은 문제가 발생했습니다. 내 오류는 이것이었다 :
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.
이 문제를 해결하기 위해 며칠을 보냈습니다. 다른 웹 사이트에서 언급 된 많은 접근 방식을 테스트했지만 그중 하나는 작동하지 않았습니다. 마지막으로 코드를 변경하고 문제가 무엇인지 알았습니다. 다양한 접근 방식에 대해 이야기하고
요약 해
드리겠습니다 .이 오류에 대한 해결책을 찾기 위해 인터넷을 찾고
있었지만 적어도 한 사람에게는 효과가있는 솔루션이 많이 있지만 다른 사람들에게는 효과가 없다고 말합니다. 이 오류에 대한 접근 방식이 많은 이유는 무엇입니까?
보이는
이 오류가 발생할 수 있습니다 일반적으로 서버에 연결에 문제가있을 때
. 쿼리 문자열이 잘못되었거나 데이터베이스에 너무 많은 연결이 문제 일 수 있습니다.따라서 모든 솔루션을 하나씩 시도하고 포기하지 않는 것이 좋습니다.여기 인터넷에서 찾은 솔루션이 있으며 각 솔루션에 대해 적어도 그 문제를 해결 한 사람이 있습니다.팁 : MySQL 설정을 변경해야하는 솔루션은 다음 파일을 참조하십시오.
- Linux : /etc/mysql/my.cnf 또는 /etc/my.cnf (사용되는 Linux 배포 및 MySQL 패키지에 따라 다름)
- Windows : C : ** ProgramData ** \ MySQL \ MySQL Server 5.6 \ my.ini (프로그램 파일이 아니라 ProgramData 임)
해결책은 다음과 같습니다.
- "바인드 주소"속성 변경
"바인드 주소"속성의 주석을 해제하거나 다음 IP 중 하나로 변경하십시오.bind-address = "127.0.0.1"또는bind-address = "0.0.0.0"
- "건너 뛰기 네트워킹"
MySQL 설정 파일에 "skip-networking"줄이 있다면, 그 줄의 시작 부분에 "#"기호를 추가하여 주석을 만드십시오.
- "wait_timeout"및 "interactive_timeout"변경
MySQL 구성 파일에 다음 행을 추가하십시오.
=
숫자
interactive_timeout =
숫자
connect_timeout =
숫자
- Java가 'localhost'를 [127.0.0.1] 대신 [::: 1]로 변환하지 않는지 확인하십시오.
MySQL은 127.0.0.1 (IPv4)을 인식하지만 ::: 1 (IPv6)은 인식하지 못하기 때문에다음 두 가지 방법 중 하나를 사용하면이를 피할 수 있습니다.옵션 1 : 연결 문자열 사용에
127.0.0.1
대신
로컬 호스트
localhost를 방지하기에 ::: 1 번역되고옵션 # 2 : -Djava.net.preferIPv4Stack = true 옵션으로 java를 실행하여 java가 IPv6 대신 IPv4를 사용하도록하십시오. Linux에서는이를 실행하거나 / etc / profile에 배치하여이를 달성 할 수도 있습니다.
export _JAVA_OPTIONS="-Djava.net.preferIPv4Stack=true"
- 운영 체제 프록시 설정, 방화벽 및 바이러스 백신 프로그램 확인
방화벽 또는 안티 바이러스 소프트웨어가 MySQL 서비스를 차단하지 않는지 확인하십시오.리눅스에서 임시로 iptables를 중지하십시오. iptables가 잘못 구성되면 tcp 패킷을 mysql 포트로 보낼 수는 있지만 tcp 패킷이 동일한 연결에서 다시 들어오는 것을 차단할 수 있습니다.
# Redhat enterprise and CentOS
systemctl stop iptables.service
# Other linux distros
service iptables stop
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을 변경하지 마십시오
"
"은 최대 패킷 수가 아니라 최대 패킷 크기를 나타내는 MySQL 구성 파일의 변수입니다. 따라서이 오류를 해결하는 데 도움이되지 않습니다.
- 바람둥이 보안 변경
TOMCAT6_SECURITY = yes를 TOMCAT6_SECURITY = no로 변경
- validationQuery 속성 사용
validationQuery = "select now ()"를 사용하여 각 쿼리에 응답이 있는지 확인하십시오.
- 자동 재 연결
이 코드를 연결 문자열에 추가하십시오.
&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.
If you are using MAMP PRO, the easy fix, which I really wish I had realized before I started searching the internet for days trying to figure this out. Its really this simple...
You just have to click "Allow Network Access to MySQL" from the MAMP MySQL tab.
Really, thats it.
Oh, and you MIGHT have to still change your bind address to either 0.0.0.0 or 127.0.0.1 like outlined in the posts above, but clicking that box alone will probably solve your problems if you are a MAMP user.
Setting the bind-address to the server's network IP instead of the localhost default, and setting privileges on my user worked for me.
my.cnf:
bind-address = 192.168.123.456
MySql Console:
GRANT ALL PRIVILEGES ON dbname.* to username@'%' IDENTIFIED BY 'password';
In my case,
-
Change the remote machine mysql configuration at
/etc/mysql/my.cnf: changebind-address = 127.0.0.1to#bind-address = 127.0.0.1 -
On the remote machine, change mysql user permissions with
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password'; -
IMPORTANT: restart mysql on the remote machine:
sudo /etc/init.d/mysql restart
In my case it was an idle timeout, that caused the connection to be dropped on the server. The connection was kept open, but not used for a long period of time. Then a client restart works, while I believe a reconnect will work as well.
A not bad solution is to have a daemon/service to ping the connection from time to time.
As the detailed answer above says, this error can be caused by many things.
I had this problem too. My setup was Mac OSX 10.8, using a Vagrant managed VirtualBox VM of Ubuntu 12.04, with MySQL 5.5.34.
I had correctly setup port forwarding in the Vagrant config file. I could telnet to the MySQL instance both from my Mac and from within the VM. So I knew the MySQL daemon was running and reachable. But when I tried to connect over JDBC, I got the "Communications link failure" error.
In my case, the problem was solved by editing the /etc/mysql/my.cnf file. Specifically, I commented out the "#bind-address=127.0.0.1" line.
I've just faced the same problem. It happened because the MySQL Daemon was binded to the IP of the machine, which is required to make connection with an user that has permission to connect @your_machine. In this case, the user should have permission to connect USER_NAME@MACHINE_NAME_OR_IP
I wanted remote access to my machine so I changed in my.cnf from
bind-address = MY_IP_ADDRESS
To
bind-address = 0.0.0.0
Which will allow an user from localhost AND even outside (in my case) to connect to the instance. Both below permissions will work if you bind the MySQL to 0.0.0.0:
USER_NAME@MACHINE_NAME_OR_IP
USER_NAME@localhost
In my case (I am a noob), I was testing Servlet that make database connection with MySQL and one of the Exception is the one mentioned above.
It made my head swing for some seconds but I came to realize that it was because I have not started my MySQL server in localhost.
After starting the server, the problem was fixed.
So, check whether MySQL server is running properly.
In case you are having problem with a set of Docker containers, then make sure that you do not only EXPOSE the port 3306, but as well map the port from outside the container -p 3306:3306. For docker-compose.yml:
version: '2'
services:
mdb:
image: mariadb:10.1
ports:
- "3306:3306"
…
For me the solution was to change in the conf file of mysql server the parameter bind-address="127.0.0.1" or bind-address="x.x.x.x" to bind-address="0.0.0.0". Thanks.
It happens (in my case) when there is not enough memory for MySQL. A restart fixes it, but if that's the case consider a nachine with more memory, or limit the memory taken by jvms
Go to Windows services in the control panel and start the MySQL service. For me it worked. When I was doing a Java EE project I got this error" Communication link failure". I restarted my system and then it worked.
After that I again got the same error even after restarting my system. Then I tried to open the MySQL command line console and login with root, even then it gave me an error.
Finally when I started the MySQL service from Windows services, it worked.
Had the same. Removing port helped in my case, so I left it as jdbc:mysql://localhost/
If you are using hibernate, this error can be caused for keeping open a Session object more time than wait_timeout
I've documented a case in here for those who are interested.
I found the solution
since MySQL need the Localhost in-order to work.
go to /etc/network/interfaces file and make sure you have the localhost configuration set there:
auto lo
iface lo inet loopback
NOW RESTART the Networking subsystem and the MySQL Services:
sudo /etc/init.d/networking restart
sudo /etc/init.d/mysql restart
Try it now
It is majorly because of weak connection between mysql client and remote mysql server.
In my case it is because of flaky VPN connection.
In phpstorm + vagrant autoReconnect driver option helped.
The resolution provided by Soheil was successful in my case.
To clarify, the only change I needed to make was with MySQL's server configuration;
bind-address = **INSERT-IP-HERE**
I am using an external MySQL server for my application. It is a basic Debian 7.5 installation with MySQL Server 5.5 - default configuration.
IMPORTANT:
Always backup the original of any configuration files you may modify. Always take care when elevated as super user.
File
/etc/mysql/my.cnf
Line
bind-address = 192.168.0.103 #127.0.0.1
Restart your MySQL Server service:
/usr/sbin/service mysql restart
As you can see, I simply provided the network IP of the server and commented out the default entry. Please note that simply copy and paste my solution will not work for you, unless by some miracle our hosts share the same IP.
Thanks @ Soheil
I was experiencing similar problem and the solution for my case was
- changing bind-address = 0.0.0.0 from 127.0.0.1
- changing url's localhost to localhost:3306
the thing i felt is we should never give up, i tried every options from this post and from other forums as well...happy it works @saurab
I faced this problem also.
As Soheil suggested, I went to php.ini file at the path C:\windows\php.ini , then I revised port number in this file.
it is on the line mysqli.default_port =..........
So I changed it in my java app as it's in the php.ini file,now it works fine with me.
I know this is an old thread but I have tried numerous things and fixed my issue using the following means..
I'm developing a cross platform app on Windows but to be used on Linux and Windows servers.
A MySQL database called "jtm" installed on both systems. For some reason, in my code I had the database name as "JTM". On Windows it worked fine, in fact on several Windows systems it flew along.
On Ubuntu I got the error above time and time again. I tested it out with the correct case in the code "jtm" and it works a treat.
Linux is obviously a lot less forgiving about case sensitivity (rightly so), whereas Windows makes allowances.
I feel a bit daft now but check everything. The error message is not the best but it does seem fixable if you persevere and get things right.
I just restarted MySQL (following a tip from here: https://stackoverflow.com/a/14238800) and it solved the issue.
I had the same issue on MacOS (10.10.2) and MySql (5.6.21) installed via homebrew.
The confusing thing was that one of my apps connected to the database fine and the other did not.
After trying many things on the app that threw the exception com.mysql.jdbc.CommunicationsException as suggested by the the accepted answer of this question to no avail, I was surprised that restarting MySQL worked.
The cause of my issue might have been the following as suggested in the answer in the aforementioned link:
Are you using connection pool ? If yes, then try to restart the server. Probably few of the connections in your connection pool are in closed state.
For Windows :- Goto start menu write , "MySqlserver Instance Configuration Wizard" and reconfigure your mysql server instance. Hope it will solve your problem.
After years having the same issue and no permanent solution this is whats solved it for the past 3 weeks (which is a record in terms of error free operation)
set global wait_timeout=3600;
set global interactive_timeout=230400;
Don't forget to make this permanent if it works for you.
If you are using local emulator, you have to use IP address 10.0.2.2 instead of localhost to access to your local MySQL server.
'Programing' 카테고리의 다른 글
| 유사성을 위해 이미지를 비교하는 간단하고 빠른 방법 (0) | 2020.05.17 |
|---|---|
| 도메인 기반 디자인이란 무엇입니까? (0) | 2020.05.17 |
| 유닉스 / 리눅스 시스템에서 실행중인 프로세스로 커맨드 라인 인수를 얻는 방법? (0) | 2020.05.16 |
| Java : System.exit ()를 호출하는 메소드를 테스트하는 방법? (0) | 2020.05.16 |
| 두 세트의 교집합을 계산하는 방법은 무엇입니까? (0) | 2020.05.16 |