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();
        }
    }

Answer 1

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");