[google-cloud-sql-discuss] Re: Communications Link Failure connecting to MySQL Cloud Instace

Hey Dave,

Thanks for coming back with that information. It appears that you're doing nothing unusual in that particular block of code. I do have some questions, though, which should take us closer to the root cause.

As you can read in this Stack Overflow Q&A, there are several possible reasons for this behaviour:

1. IP address or hostname in JDBC URL is wrong.
   
2. Hostname in JDBC URL is not recognized by local DNS server.
   
3. Port number is missing or wrong in JDBC URL.
   
4. DB server is down.
   
5. DB server doesn't accept TCP/IP connections.
   
6. DB server has run out of connections.
   
7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.
   

I don't believe 1, 2, 3 or 7 are at play since your connections work at other times and all those causes would be likely to affect every single connection, so this leaves the following options:

• DB server is down
  
• DB server is not accepting TCP/IP connections
  
• DB server has run out of connections
  

Specifically, the issue that seems most plausible to me is the last one, since this is a common source of issues.

Could you share a more full code example showing that the code definitely closes connections it opens?

It even be could be possible even if you normally close connections that an unexpected exception or other exit path from the code is causing the connection-closing code to be missed. This would result in a buildup over time of sockets in a TIME_WAIT state, and eventually the DB server would be unable to service requests until some of these timed out. 

You could also check this by logging onto the DB server machine and checking sudo netstat -nat. 

If this is the case, you can solve the issue by making sure that all possible code paths close the connection. You could, in addition to this (although this should take care of the vast majority of hanging TIME_WAIT connections) potentially tweak the mysql wait_timeout parameter, although you'll want to gather statistics on TCP traffic on the machine to make sure that you don't set it too low so that it cuts off actual connections that wouldn't ordinarily fail, although they happen to experience some significant delays between packet arrivals for whatever reason.

Let me know how this all looks to you and what you investigation of your code turns up. We can of course look into other avenues if this doesn't correspond to the actual root cause.

Cheers,

Nick
Cloud Platform Community Support

On Thursday, February 9, 2017 at 3:22:54 PM UTC-5, Dave Greenly wrote:

Sure - here is how we connect in Java with as much of the log as was output.

Let me know if I can provide any more info, this is becoming a very painful problem for us. 

Thanks!!

Dave

public String ConnectToDb(String host, String user, String password, String dbname) {

try {

Class.forName("com.mysql.jdbc.Driver");

db = DriverManager.getConnection(

"jdbc:mysql://" + host + ":3306/" + dbname + "?user=" + user + "&password=" + password);

} catch (Exception e) {

db = null;

e.printStackTrace();

System.out.println("SQLException: " + e.getMessage());

return e.getMessage();

}

return "success";

}

Exception Caught in Query()SQLException: Communications link failure The last packet successfully received from the server was 3,561 milliseconds ago. The last packet sent successfully to the server was 3,561 milliseconds ago. SQLState: 08S01 VendorError: 0 Feb 09, 2017 1:14:37 PM spaceNavSendMail.Sql queryAggregatePcQuery SEVERE: Error in queryAggregatePcQuery() com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 3,561 milliseconds ago. The last packet sent successfully to the server was 3,561 milliseconds ago. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3427) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3327) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446) at spaceNavSendMail.Sql.queryAggregatePcQuery(Sql.java:3435) at spaceNavSendMail.SpaceNavSendMail.generateEmailMessage(SpaceNavSendMail.java:980) at spaceNavSendMail.SpaceNavSendMail.getConjunctionReport(SpaceNavSendMail.java:897) at spaceNavSendMail.SpaceNavSendMail.sendSummaryEmail(SpaceNavSendMail.java:710) at spaceNavSendMail.SpaceNavSendMail.main(SpaceNavSendMail.java:347) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader.main(JarRsrcLoader.java:58)Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3337)

On Thursday, January 12, 2017 at 9:22:35 AM UTC-7, Dave Greenly wrote:

Hi there, 

We have a Cloud Instance of a MySQL Database which we use daily.  We have multiple compute engines that pull and push data to it, and on those compute engines are Java programs that connect to the database and read/write.

Pretty standard stuff.

On a semi-consistent basis, our Java programs have difficulty connecting to the database, and we receive a "Communications Link Failure" and our code cannot connect to the DB.  After some indeterminate amount of time, things "just start working again".

We actually put a check in our code for this error, and if we get it, we wait a few seconds, and try again, but even after 5 or so retry attempts, sometimes it is still not working.

We have chalked this up in the past to some type of network anomaly going on in the cloud.

We are using mysql-connector-java-5.1.38, and the MySQL instance on the cloud is version MySQL 5.5.

Has anyone else experienced this?

Thanks

Dave

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/923caf13-cbc7-420a-9e17-9e5580958d32%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.