Cloud SQL sec generation abortion issue

Question

Im having an app engine service connected to an Cloud SQL sec generation DB and everything is just fine, I just wanted to replace the DB structure so I've created a new DB with the new structure and moved the needed Data from the old one to the new one.

once I've deployed the service with the new DB name whenever the app engine service tries to connect to the DB the connection aborted with this error:

Aborted connection 11 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)

given that I'm trying to connect to the instance using JPA and the URL is like this

jdbc:mysql://google/My_DB?cloudSqlInstance=${endpoints.project.id}:${database.region}:${database.instance}&autoReconnect=true&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL

and the user name/ password is sent over properties in the entity manager creation

find below how Im opening and closing the connection:

Im having EMF class

public final class EMF {
    private static final EntityManagerFactory emfInstance =
        Persistence.createEntityManagerFactory("cloud",getProp());

private EMF() {}
public static EntityManagerFactory get() {
    return emfInstance;
} 

private static Properties getProp() {
    Properties p = new Properties();
    p.put("javax.persistence.jdbc.user", Credentials.getDatabaseUsername());
    p.put("javax.persistence.jdbc.password", Credentials.getDatabasePassword());
    return p;


}

}

Then Im having like tens of DAO functions in all Im fowling the below steps:

public void insertSMSVerification(SMSVerification smsVerification) {

EntityManager em = null;
EntityTransaction et = null;

try {

    em = EMF.get().createEntityManager();
    et = em.getTransaction();

    et.begin();
    em.merge(smsVerification);
    et.commit();

}catch (Exception e) {              
    if(et.isActive())
        et.rollback();
    DBException.handleDBException(e, LOG);
} finally {
    if (em != null)
        em.close();
}

}

So, any help plz?

As stated in the Diagnosing issues with Cloud SQL instances documentation, this error usually means the app is not closing connections properly, and not that it is failing to connect. Either it would be nice if you could provide a code snippet of how you are connecting and disconnecting to the DB. — pessolato
Thanks for your feedback, but I have restarted the dB instance several times so if it’s a closing connection problem it should be fixed and I’m using same code in several environments and it works fine — Tamer Saleh
May be I opened a connection using a VM then it wasn’t closed properly. But I think since I did restarted the instance it shouldn’t be affected by that any more right ? — Tamer Saleh
@pessolato would you like to chat with me to help on that issue? I really appreciate your help I spent like 10 hours in that issue without any hope — Tamer Saleh

llompalles llompalles · Accepted Answer · 2019-12-09T14:35:10

According to the documentation the JDBC connection URL should look like this:

jdbc:mysql:///<DATABASE_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false&user=<MYSQL_USER_NAME>&password=<MYSQL_USER_PASSWORD>

In what you have shown you need to substitute jdbc:mysql://google/My_DB? for jdbc:mysql:///<DATABASE_NAME>?

Cloud SQL sec generation abortion issue

1 Answers