How to solve CloudSQL 8 hours timeout problem?

[Camilo Sacanamboy]

Hi

I'm working in an app with Google Cloud SQL. I'm using Hibernate 4.2.0.Final and I've noticed that after 8 hours of inactivity my connection is closed. I've been searching on the Internet and I couldn't find anything effective to solve this issue. All the information I've found is summarized in these items:

• Use a connection pool where I should specify a higher timeout. I've tried with c3p0 and DBCP 2.1.1 libraries but none of them solved the issue.
  
• Open a connection before some query and then close it after that. The problem is that I would like to use the EntityManager in order to use queries with entities.

Here it's what I've done with DBCP 2.1.1 (http://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2/2.1.1). The problem is that Goggle App Engine log shows an ExceptionInInitializerError when I try to use findAll in PersistenceService. It also shows that my own DataSource (see below) cannot be cast to String. I don't understand why.

This is the PersistenceService I'm using right now

package co.peewah.ems.utils;

import com.google.appengine.api.utils.SystemProperty;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.AbstractMap.SimpleEntry;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.hibernate.cfg.AvailableSettings;


/**
 *
 * @author Muacito
 */
public class PersistenceService
{
    private static final EntityManagerFactory EMF = createEntityManagerFactory();

    private static EntityManager entityManager = EMF.createEntityManager();

    private static EntityManagerFactory createEntityManagerFactory()
    {
        //String mode = "";
        /*
        if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Development)
        {
            mode = "dev.";
        }
        */

        Map<String, Object> properties = new HashMap<>();
       
        //Properties properties = new Properties();

        //properties.put("javax.persistence.jdbc.driver", System.getProperty("persistence." + mode + "db.driver"));
        //properties.put("javax.persistence.jdbc.url", System.getProperty("persistence." + mode + "db.url"));
        //properties.put("javax.persistence.jdbc.user", System.getProperty("persistence." + mode + "db.user"));
        //properties.put("javax.persistence.jdbc.password", System.getProperty("persistence." + mode + "db.password"));
        try
        {
            properties.put(AvailableSettings.DATASOURCE, DataSource.getInstance());
        } catch (IOException | SQLException | PropertyVetoException e)
        {
             e.printStackTrace();
        }
       
       
        System.out.println("----------------------------");
        System.out.println("----------------------------");
        System.out.println(properties);
        System.out.println("----------------------------");
        System.out.println("----------------------------");

        return Persistence.createEntityManagerFactory("Demo", properties);
    }

    private static EntityManager getEntityManager()
    {
        if (!PersistenceService.entityManager.isOpen())
        {
            PersistenceService.entityManager = PersistenceService.EMF.createEntityManager();
        }

        return PersistenceService.entityManager;
    }

    public static <T> void create(T entity)
    {
        try
        {
            if (entity.getClass().getMethod("getId").invoke(entity) == null)
            {
                entity.getClass().getMethod("setId", String.class).invoke(entity,
                        UUID.randomUUID().toString().replace("-", ""));
            }

            if (entity.getClass().getMethod("getCreated").invoke(entity) == null)
            {
                entity.getClass().getMethod("setCreated", Date.class).invoke(entity,
                        GregorianCalendar.getInstance().getTime());
            }

            getEntityManager().getTransaction().begin();

            getEntityManager().persist(entity);

            getEntityManager().flush();

            getEntityManager().getTransaction().commit();

        } catch (Exception ex)
        {
            Logger.getLogger(PersistenceService.class.getName()).log(Level.SEVERE, null, ex);
            getEntityManager().getTransaction().rollback();
        }
    }

    public static <T> void edit(T entity)
    {
        try
        {
            if (entity.getClass().getMethod("getUpdated").invoke(entity) == null)
            {
                entity.getClass().getMethod("setUpdated", Date.class).invoke(entity,
                        GregorianCalendar.getInstance().getTime());
            }

            getEntityManager().getTransaction().begin();

            getEntityManager().merge(entity);

            getEntityManager().flush();

            getEntityManager().getTransaction().commit();
        } catch (Exception ex)
        {
            Logger.getLogger(PersistenceService.class.getName()).log(Level.SEVERE, null, ex);
            getEntityManager().getTransaction().rollback();
        }
    }

    public static <T> void remove(T entity)
    {
        try
        {
            getEntityManager().getTransaction().begin();

            getEntityManager().remove(entity);

            getEntityManager().flush();

            getEntityManager().getTransaction().commit();

        } catch (Exception ex)
        {
            Logger.getLogger(PersistenceService.class.getName()).log(Level.SEVERE, null, ex);
            getEntityManager().getTransaction().rollback();
        }
    }

    public static <T> List<T> filter(Class<T> entityClass, String query, SimpleEntry<String, Object>... parameters)
    {
        TypedQuery<T> typedQuery = getEntityManager().createQuery(query, entityClass);

        for (SimpleEntry<String, Object> param : parameters)
        {
            typedQuery.setParameter(param.getKey(), param.getValue());
        }

        return typedQuery.getResultList();
    }

    public static <T> T find(Class<T> entityClass, Object id)
    {
        T entity = getEntityManager().find(entityClass, id);

        return entity;
    }

    public static <T> List<T> findBy(Class<T> entityClass, String criteria, Object value)
    {
        String c = criteria.replaceFirst(criteria.charAt(0) + "", (criteria.charAt(0) + "").toLowerCase());

        TypedQuery<T> query = getEntityManager().createNamedQuery(entityClass.getSimpleName() + ".findBy" + criteria,
                entityClass);
        query.setParameter(c, value);

        return query.getResultList();
    }

    public static <T> List<T> findAll(Class<T> entityClass)
    {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        cq.select(cq.from(entityClass));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public static <T> List<T> findRange(Class<T> entityClass, int[] range)
    {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        cq.select(cq.from(entityClass));
        Query q = getEntityManager().createQuery(cq);
        q.setMaxResults(range[1] - range[0] + 1);
        q.setFirstResult(range[0]);

        return q.getResultList();
    }

    public static <T> int count(Class<T> entityClass)
    {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        Root<T> rt = cq.from(entityClass);
        cq.select(getEntityManager().getCriteriaBuilder().count(rt));
        Query q = getEntityManager().createQuery(cq);

        return ((Long) q.getSingleResult()).intValue();
    }
}

This is the DataSource I'm using

package co.peewah.ems.utils;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

import com.google.appengine.api.utils.SystemProperty;

/**
 *
 * @author csacanam
 *
 */
public class DataSource
{
   
    private static DataSource datasource;
    private BasicDataSource ds;
   
    private DataSource() throws IOException, SQLException, PropertyVetoException
    {
        ds = new BasicDataSource();
       
       
        String mode = "";

        if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Development)
        {
            mode = "dev.";
        }
       
        String user = System.getProperty("persistence." + mode + "db.user");
        String password = System.getProperty("persistence." + mode + "db.password");
        String address = System.getProperty("persistence." + mode + "db.url");
        String driver = System.getProperty("persistence." + mode + "db.driver");
       
        // Create and configure DBCP DataSource
        ds.setDriverClassName(driver);
        ds.setUrl(address);
        ds.setUsername(user);
        ds.setPassword(password);
       
        ds.setMinIdle(5);
        ds.setMaxIdle(20);
 
        ds.setMaxOpenPreparedStatements(180);
    }
   
    public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException
    {
        if(datasource == null)
        {
            datasource = new DataSource();    
        }
       
        return datasource;
    }
   
    public Connection getConnection() throws SQLException
    {
        return this.ds.getConnection();
    }
   
   
   

}

[Kevin Malachowski]

I don't know much about your specific scenario, but we generally suggest setting a TCP keepalive on the machine you're running from (Step 7: https://cloud.google.com/sql/docs/compute-engine-access#gce-connect-ip)

You might be able to set the keepalive setting on a per-connection level too, depending on what libraries you're using.

...

[Tony Tseng]

The 8 hour timeout seems related to http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout.

Have you tried changing that? Please see https://cloud.google.com/sql/docs/mysql-flags.