MySQL exception com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Question

`

import java.sql.*;

public class Match {
    public static void main(String args[]) throws Exception{
        DBConnection1 con = new DBConnection1();
        DBConnection con1 = new DBConnection();
        Connection conn = null,conn1=null;
        conn = con.getConnection();
        conn1 = con1.getConnection();
        Statement st = null;
        PreparedStatement pst = null;
        ResultSet rs = null,rs1 = null;
        st = conn.createStatement();
        String query1 = "SELECT Name FROM Employee WHERE Name=?";
        pst = conn1.prepareStatement(query1);
        st.setFetchSize(Integer.MIN_VALUE);
        String query = "SELECT name FROM emp";
        rs = st.executeQuery(query);
        String name = "";
        int count = 0;
        while(rs.next()){
            title = rs.getString("name");
            pst.setString(1, title);
            rs1 = pst.executeQuery();
            while(rs1.next()){
                    count++;
                    if(count % 100 == 0)
                        System.out.println(count);
            }
        }
        System.out.println(count);
    }
}

`

I am selecting value from the very large database based on some value from other database . I am running my select query in a while loop. After running my java code after getting many result , i am getting

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure exception.

I have no idea why this is happening. If you guys have any idea please help

I already read the old questions based on this exception but none of them helps.

There's probably a limit to the number of consecutive queries you can perform on the server. Can you divide the task to that you don't reach this hypothetical limit? — cangrejo, Apr 29 '14 at 11:18
It might be a good idea to count the number of queries you're performing. Another possibility is that there is a timeout period. If the query takes too long, you might be reaching it. — cangrejo, Apr 29 '14 at 11:20
I am counting the number of times query is running. It fails after 2.8 million results — Rahul Mittal, Apr 29 '14 at 11:22
Does the query run many times or does it run for a long time? — cangrejo, Apr 29 '14 at 11:23
I am using PreparedStatement and it is running in a loop. Also it runs for a long time — Rahul Mittal, Apr 29 '14 at 11:25
Maybe you should post your code. That would definitely help. — cangrejo, Apr 29 '14 at 11:25

score 2 · Accepted Answer · answered Apr 30 '14 at 09:04

import java.sql.*;

public class Match {
    public static void main(String args[]) throws Exception{
        DBConnection1 con = new DBConnection1();
        DBConnection con1 = new DBConnection();
        Connection conn = null,conn1=null;
        conn = con.getConnection();
        conn1 = con1.getConnection();
        Statement st = null;
        PreparedStatement pst = null;
        ResultSet rs = null,rs1 = null;
        st = conn.createStatement();
        st.setFetchSize(Integer.MIN_VALUE);
        String query = "SELECT name FROM emp";
        rs = st.executeQuery(query);
        String title = "",query1="";
        StringBuffer newQuery = new StringBuffer("SELECT Name FROM Employee WHERE ");
        int count = 0;
        long nameCount = 0L;
        while(rs.next()){
            nameCount++;
            title = rs.getString("name");
            query1 = "Name=? or";
            pst = conn1.prepareStatement(query1);
            pst.setString(1, title);

            newQuery.append(pst.toString().substring(pst.toString().indexOf('N'), pst.toString().length())+" ");
        }

        if ( nameCount > 0 ){
            String Query = newQuery.toString().substring(0,newQuery.toString().length() - 3);  
            rs1 = conn1.createStatement().executeQuery(Query);
            while(rs1.next()){
                count++;
                if(count % 50 == 0)
                    System.out.println(count);
            }
        }
    }
}

Using PreparedStatement solves the problem of SQL Injection attack. Now the code is working.

You can also use threading, if synchronization is not required — Mohammad Ashfaq, Apr 30 '14 at 10:53

score 1 · Answer 2 · answered Apr 29 '14 at 11:48

1

I think this code is optimized, but it may have some syntax error:

import java.sql.*;

public class Match {
    public static void main(String args[]) throws Exception{
        DBConnection1 con = new DBConnection1();
        DBConnection con1 = new DBConnection();
        Connection conn = null,conn1=null;
        conn = con.getConnection();
        conn1 = con1.getConnection();
        Statement st = null;

        ResultSet rs = null,rs1 = null;
        st = conn.createStatement();
        //String query1 = "SELECT Name FROM Employee WHERE Name=?";

        st.setFetchSize(Integer.MIN_VALUE);
        String query = "SELECT name FROM emp";
        rs = st.executeQuery(query);
        String name = "";

        StringBuffer newQuery = new StringBuffer("SELECT Name FROM Employee WHERE");
        int count = 0;
        long nameCount = 0L;
        while(rs.next()){
            nameCount++;
            title = rs.getString("name");
            newQuery.append(" Name='" + title + "' or");
        }

        if ( nameCount > 0 ){
            newQuery = newQuery.subString( newQuery.length() - 3);  
            rs1 = conn1.createStatement.executeQuery( newQuery );
            while(rs1.next()){
                count++;
                if(count % 100 == 0)
                    System.out.println(count);
            }
        }
    }
}

Link failure may be because of so many query execution. Hence I have made it to fire only one or two queries, and you will get all your results.

answered Apr 29 '14 at 11:48

Mohammad Ashfaq

1,333
2
14
38

The query will either contain no condition to be met (if there are 0 names) or will always have incorrect syntax (trailing `or`). If these errors were corrected, it could result in max_allowed_packet being reached. – cangrejo Apr 29 '14 at 12:00
That problem could be addressed, however, by fine tuning the maximum query length and the maximum number of queries to be executed to a good balance. – cangrejo Apr 29 '14 at 12:02
Problem of trailing or is already handled in the code, and max_allowed_packet size can also be increased, in order to avoid the error and improve the performance – Mohammad Ashfaq Apr 29 '14 at 12:02
There is a syntax error in query because of that 'or' – Rahul Mittal Apr 30 '14 at 06:13
You can try newQuery = newQuery.subString( 0, newQuery.length() - 3); instead of newQuery = newQuery.subString( newQuery.length() - 3); – Mohammad Ashfaq Apr 30 '14 at 07:54
Yeah i changed that part. Also SQL injection problem is also there. So i correct that part. I will post the code. – Rahul Mittal Apr 30 '14 at 09:00
Thanks for this code but, it is still taking so much time to get the result as my database is very large. – Rahul Mittal Apr 30 '14 at 09:05

MySQL exception com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

2 Answers2