I have a program that gets the device list from and then query the details for every single device. There are about 10 thousand devices to query.
Here's the code:
public static void main(String[] args) {
final static String query1 = "SELECT b.DEVICEID AS DeviceID, a.ICName AS ICName, a.GroupID AS GroupID "
+ "FROM ic_tab AS a, devic_tab AS b WHERE a.ICID = b.ICID";
final static String query2 = "SELECT c.weekday AS Weekday, d.TIME AS Time, d.PLANID AS PlanID "
+ "FROM ic_tab AS a, devic_tab AS b, grptodweekday_tab AS c, grptod_tab AS d "
+ "WHERE b.DEVICEID = '%s' AND b.ICID = a.ICID AND a.GROUPID = c.groupid "
+ "AND a.GROUPID = d.GROUPID AND c.segmenttype = d.SEGMENTTYPE ORDER BY Weekday, LENGTH(Time), Time";
final static String query3 = "SELECT a.planid AS PlanID, a.green AS Green, "
+ "a.yellow AS Yellow, b.CYCLETIME AS Cycle, b.offset AS Offset "
+ "FROM basic_tab AS a, cfg_tab AS b WHERE a.DEVICEID = '%s' "
+ "AND a.DEVICEID = b.DEVICEID AND a.planid = b.planid ORDER BY PlanID";
Connection conn = MySQLConnection.getMySQLConnection();
ResultSet rs = MySQLConnection.query(conn, queryDevice);
try {
int count = 1;
while(rs.next()) {
String deviceID = rs.getString("DeviceID");
ResultSet rs2 = MySQLConnection.query(conn, String.format(query2, deviceID));
while(rs2.next()) {
//do something
}
rs2.close();
ResultSet rs3 = MySQLConnection.query(conn, String.format(query3, deviceID));
while(rs3.next()) {
//do something
}
rsWeekday.close();
}
rsDevice.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
Please ignore that stupid column naming, I'm also confused about what the hell designer was doing.
The issue is that I'm able to connect to the database and also can query from it. But I'll get the "Communications link failure" after queried 300 to 900 devices (which "query3" String in the code does). It's just random, I'd try so many times.
Above is the error message:
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet successfully received from the server was 5,848 milliseconds ago. The last packet sent successfully to the server was 5,856 milliseconds ago.
at mysql.connector.java@8.0.19/com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at mysql.connector.java@8.0.19/com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at mysql.connector.java@8.0.19/com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at mysql.connector.java@8.0.19/com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at mySQLtoJson/com.xxx.utility.MySQLConnection.query(MySQLConnection.java:37)
at mySQLtoJson/com.xxx.MySQLtoJson.main(MySQLtoFirebase.java:82)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
The last packet successfully received from the server was 5,848 milliseconds ago. The last packet sent successfully to the server was 5,856 milliseconds ago.
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
at mysql.connector.java@8.0.19/com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at mysql.connector.java@8.0.19/com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at mysql.connector.java@8.0.19/com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at mysql.connector.java@8.0.19/com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
at mysql.connector.java@8.0.19/com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:539)
at mysql.connector.java@8.0.19/com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:703)
at mysql.connector.java@8.0.19/com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:642)
at mysql.connector.java@8.0.19/com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:941)
at mysql.connector.java@8.0.19/com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:887)
at mysql.connector.java@8.0.19/com.mysql.cj.NativeSession.execSQL(NativeSession.java:1073)
at mysql.connector.java@8.0.19/com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:724)
... 3 more
I had searched for solutions but they've all failed to connect to the server at the beginning, which seems different from my problem. And I had tried to fix it by reconnecting to MySQL server every 100 queries but still doesn't work. What would be the problem in this situation?
