Cannot connect to mysql server from java

Question

I get the error "Communications link failure" at this line of code:

mySqlCon = DriverManager.getConnection("jdbc:mysql://**server ip address**:3306/db-name", "mu-user-name", "my-password");

I checked everything in this post:

I increased max-allowed-packet in my.cnf in etc/mysql: max_allowed_packet = 5073741824------ [mysqldump] max_allowed_packet = 1G
The bind-address is: 127.0.0.1
All timeout values are equal to a number
Tomcat is not yet installed on server (new server)
There is no skip-networking in my.cnf
I can ping the server
I am connected to the mysql database via ssh

When I change the query string to this:

mySqlCon = DriverManager.getConnection("jdbc:mysql://**server ip address**:22/127.0.0.1:3306/db-name", "mu-user-name", "my-password");

I get the error Packet for query is too large (4739923 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

While I have changed the packet size on my.cnf and restarted the mysql service after that.

Any suggestions?

NOTE:

I can connect through ssh with this code, but this way doesn't seem rational! I can connect once in main and then I should pass the connection to all the classes.

public my-class-constructor() {

        try {
            go();
        } catch (Exception e) {
            e.printStackTrace();
        }

        mySqlCon = null;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://" + rhost + ":" + lport + "/";
        String db = "my-db-name";
        String dbUser = "dbuser";
        String dbPasswd = "pass";
        try {
            Class.forName(driver);
            mySqlCon = DriverManager.getConnection(url + db, dbUser, dbPasswd);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void go() {
        String user = "ssh-user";
        String password = "ssh-pass";
        String host = "ips-address";
        int port = 22;
        try {
            JSch jsch = new JSch();
            Session session = jsch.getSession(user, host, port);
            lport = 4321;
            rhost = "localhost";
            rport = 3306;
            session.setPassword(password);
            session.setConfig("StrictHostKeyChecking", "no");
            System.out.println("Establishing Connection...");
            session.connect();
            int assinged_port = session.setPortForwardingL(lport, rhost, rport);
            System.out.println("localhost:" + assinged_port + " -> " + rhost
                    + ":" + rport);
        } catch (Exception e) {
            System.err.print(e);
            e.printStackTrace();
        }
    }

it seems your query is too large, try a smaller query e.g. "select * from table limit 10;" — nafas, May 20 '15 at 15:10
If the MySQL database is remote, have you configured the permissions correctly? — John, May 20 '15 at 15:11
did you check "max-allowed-packet" against the size from the error message? — rmalchow, May 20 '15 at 15:11
Also check http://stackoverflow.com/questions/2121829/com-mysql-jdbc-exceptions-jdbc4-communicationsexceptioncommunications-link-fail — Raúl, May 20 '15 at 15:14
I am currently connected to the server through ssh, and I can work on mysql there in command prompt. i get the error exactly at the line I put in the question, I don't run any sql query yet. — Andi Keikha, May 20 '15 at 15:16
max_allowed_packet = 5073741824------ [mysqldump] max_allowed_packet = 1G — Andi Keikha, May 20 '15 at 15:19
Please provide the MySQL version and the jdbc veraion. I will try to reproduce the issue locally today or tomorrow. — Yuri G., May 20 '15 at 16:10
Sorry I was trying to find my jdbc version. I am still workin gon it. MySQL 5.6, Java SE-1.7. — Andi Keikha, May 20 '15 at 17:01

score 3 · Answer 1 · answered May 20 '15 at 15:38

Take a look here . Looks the following describes your case

The largest possible packet that can be transmitted to or from a MySQL 5.7 server or client is 1GB.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

So, it looks like you need to change the max_allowed_packet on the client as well:

mySqlCon = DriverManager.getConnection("jdbc:mysql://**server ip address**:3306/db-name?max_allowed_packet= 5073741824", "mu-user-name", "my-password");

`mySqlCon = DriverManager.getConnection("jdbc:mysql://**server ip address**:22/127.0.0.1:3306/db-name?max_allowed_packet=5073741824", "mu-user-name", "my-password");` — Yuri G., May 20 '15 at 16:03
Oh. I tried both. the same errors as before. I also tried to change the value of max_allowed_packet to another value less than this value, and tried with both. still the same errors. — Andi Keikha, May 20 '15 at 16:04

Andi Keikha · Accepted Answer · 2015-05-20T17:15:29.720

0

I changed the binding address in my.cnf file in /etc/mysql to the ip address of the server, and it solved the problem.

edited May 20 '15 at 17:15

answered May 20 '15 at 17:10

Andi Keikha

1,246
2
16
37

1

good to hear that you've managed with this, thanks for the update – Yuri G. May 20 '15 at 18:59

Cannot connect to mysql server from java

2 Answers2