java - JPA-通信异常 : Communications link failure at second connection

Question

我有一个使用 JPA EclipseLInk 2.5.2 的应用程序，它每分钟都会联系 mysql 来执行操作。必须做的第一件事是表明它对数据库来说是 Activity 的。为此，我使用 ScheuledExecutorService 每分钟运行一个业务对象。该对象每次打开和关闭 DAO。

在我的开发环境(我的电脑)上，默认配置 Wamp64 位和 mysql，一切正常。当我安装 preprod 服务器(CentOS 7、mysql Ver 14.14 Distrib 5.7.16)时，第一个循环正常，但在尝试使用 DAO 执行 sql 查询时，下一个循环失败并显示此消息:

[EL Warning]: 2016-12-13 09:35:01.936--UnitOfWork(1778154389)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 57 316 milliseconds ago.  The last packet sent successfully to the server was 4 milliseconds ago.
Error Code: 0
Call: SELECT id, lastDateAliveChargement, lastDateAliveDechargement FROM LastAlive
Query: ReportQuery(referenceClass=LastAlive sql="SELECT id, lastDateAliveChargement, lastDateAliveDechargement FROM LastAlive")

在 preprod 中，我首先使用了与 mariaDB jdbc 驱动程序一起使用的 mariaDB。然后我用mysql jdbc driver换成mysql，问题依旧。当我列出 mysql 上的挂起连接时，它们仍然会显示命令 Sleep，直到 wait_timeout 过期。我尝试使用这些设置(my.cnf)配置 mysql

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=100
port=3306
wait_timeout=3600   

在我的电脑上(wamp，Windows): [mysql] no-auto-rehash//无法在服务器上使用此参数启动 [mysqld] 端口=3306

以下是 JPA 初始化的参数:

persistenceMapInitiales.put("javax.persistence.jdbc.driver","com.mysql.cj.jdbc.Driver");            
persistenceMapInitiales.put("javax.persistence.jdbc.url","jdbc:mysql://MY_REMOTE_IP/myDB?characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC&autoReconnect=true&useSSL=false");
persistenceMapInitiales.put("javax.persistence.jdbc.user","toto");
persistenceMapInitiales.put("javax.persistence.jdbc.password","toto");
persistenceMapInitiales.put("eclipselink.ddl-generation","create-or-extend-tables");
persistenceMapInitiales.put("eclipselink.create-ddl-jdbc-file-name","createDDL_ddlGeneration.jdbc"); 
persistenceMapInitiales.put("eclipselink.drop-ddl-jdbc-file-name","dropDDL_ddlGeneration.jdbc"); 
persistenceMapInitiales.put("eclipselink.ddl.default-table-suffix","engine=InnoDB");
persistenceMapInitiales.put("eclipselink.ddl-generation.output-mode","both");

persistenceMapInitiales.put("eclipselink.connection-pool.default.initial","1" );
persistenceMapInitiales.put("eclipselink.connection-pool.default.min","64" );
persistenceMapInitiales.put("eclipselink.connection-pool.default.max","64" );  

scheduledExectorService 的声明:

public static void main(String[] args)
{
    final Integer CRON_PERIOD = 1; // every minute
    ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
    scheduler.scheduleAtFixedRate(new MyWork(), 0, CRON_PERIOD, TimeUnit.MINUTES);
}

商务舱

public class MyWork() implements Runnable{

    private LastAliveDao lastAliveDao;
    //...

    public void run()
    {
        sayIsAlive();
        //...
    }

    private void sayIsAlive()
    {
        logger.info("upadet isAlive");
        try{
            lastAliveDao = new JpaLastAliveDao();
            lastAliveDao.sayChargementIsAlive();
        }catch(Exception e){
            logger.error("", e);
        }finally{
            try{
                lastAliveDao.close();
            }catch(Exception e)
            {
                logger.warn("Exception closing dao : ", e);
            }
            lastAliveDao = null;
        }
    }
}

DAO 代码:

public class JpaLastAliveDao extends JpaDao<LastAlive, Integer> implements LastAliveDao {

@Override
public void sayChargementIsAlive() {
    List<LastAlive> alives = this.list();
    if(alives.size() == 0){
        LastAlive alive = new LastAlive();
        alive.setLastDateAliveChargement(Calendar.getInstance().getTime());
        this.persist(alive);
    }else{
        LastAlive alive = alives.get(0);
        alive.setLastDateAliveChargement(Calendar.getInstance().getTime());
        this.update(alive);
    }
}

以及 JpaDAO 代码:

public abstract class JpaDao<T, PK extends Serializable>
    implements Dao<T, PK>{

    protected Class<T> entityClass;

    protected EntityManagerFactory emf;
    protected EntityManager em;

    @SuppressWarnings("unchecked")
    public JpaDao()
    {
        emf = JPAUtils.getInstance("facturator").getEmf();
        em = emf.createEntityManager();
        ParameterizedType genericSuperclass = (ParameterizedType) 
                getClass().getGenericSuperclass();
        this.entityClass = (Class<T>) genericSuperclass.getActualTypeArguments()[0];
    }

    public T persist(T t) {
        this.em.getTransaction().begin();
        this.em.persist(t);
        this.em.getTransaction().commit();
        return t;
    }

    public T findById(PK id) {
        return this.em.find(entityClass, id);
    }

    public T update(T t) {
        this.em.getTransaction().begin();
        T entityPersist = this.em.merge(t);
        this.em.getTransaction().commit();
        return entityPersist;
    }

    @SuppressWarnings("unchecked")
    public List<T> list()
    {
        return this.em.createQuery("SELECT t FROM "+entityClass.getName()+" t ").getResultList();
    }

    public void delete(T t) {
        this.em.getTransaction().begin();
        this.em.remove(t);
        this.em.getTransaction().commit();
    }

    public void close(){
        em.close();
    }

}

Answer 1

好的，我找到原因了。数据库服务器和程序服务器配置在 2 个不同的子掩码网络上:一个为 255.255.255.0，另一个为 255.255.255.255。

我发现它带有 SocketException，该异常未在初始日志设置的堆栈中显示。