Flink程序自定义MySQLSink出现客户端连接超时/失效异常

一、错误日志

2022-08-30 03:14:45
org.apache.flink.streaming.runtime.tasks.AsynchronousException: Caught exception while processing timer.
    at org.apache.flink.streaming.runtime.tasks.StreamTask$StreamTaskAsyncExceptionHandler.handleAsyncException(StreamTask.java:1214)
    at org.apache.flink.streaming.runtime.tasks.StreamTask.handleAsyncException(StreamTask.java:1190)
    at org.apache.flink.streaming.runtime.tasks.StreamTask.invokeProcessingTimeCallback(StreamTask.java:1329)
    at org.apache.flink.streaming.runtime.tasks.StreamTask.lambda$null$17(StreamTask.java:1318)
    at org.apache.flink.streaming.runtime.tasks.StreamTaskActionExecutor$1.runThrowing(StreamTaskActionExecutor.java:50)
    at org.apache.flink.streaming.runtime.tasks.mailbox.Mail.run(Mail.java:90)
    at org.apache.flink.streaming.runtime.tasks.mailbox.MailboxProcessor.processMail(MailboxProcessor.java:317)
    at org.apache.flink.streaming.runtime.tasks.mailbox.MailboxProcessor.runMailboxLoop(MailboxProcessor.java:189)
    at org.apache.flink.streaming.runtime.tasks.StreamTask.runMailboxLoop(StreamTask.java:619)
    at org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:583)
    at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:758)
    at org.apache.flink.runtime.taskmanager.Task.run(Task.java:573)
    at java.lang.Thread.run(Thread.java:748)
Caused by: TimerException{org.apache.flink.streaming.runtime.tasks.ExceptionInChainedOperatorException: Could not forward element to next operator}
    ... 11 more
Caused by: org.apache.flink.streaming.runtime.tasks.ExceptionInChainedOperatorException: Could not forward element to next operator
    at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.pushToOperator(CopyingChainingOutput.java:88)
    at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.collect(CopyingChainingOutput.java:46)
    at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.collect(CopyingChainingOutput.java:26)
    at org.apache.flink.streaming.api.operators.CountingOutput.collect(CountingOutput.java:50)
    at org.apache.flink.streaming.api.operators.CountingOutput.collect(CountingOutput.java:28)
    at org.apache.flink.streaming.api.operators.TimestampedCollector.collect(TimestampedCollector.java:50)
    at org.apache.flink.streaming.api.functions.windowing.PassThroughAllWindowFunction.apply(PassThroughAllWindowFunction.java:35)
    at org.apache.flink.streaming.runtime.operators.windowing.functions.InternalSingleValueAllWindowFunction.process(InternalSingleValueAllWindowFunction.java:48)
    at org.apache.flink.streaming.runtime.operators.windowing.functions.InternalSingleValueAllWindowFunction.process(InternalSingleValueAllWindowFunction.java:34)
    at org.apache.flink.streaming.runtime.operators.windowing.WindowOperator.emitWindowContents(WindowOperator.java:577)
    at org.apache.flink.streaming.runtime.operators.windowing.WindowOperator.onProcessingTime(WindowOperator.java:533)
    at org.apache.flink.streaming.api.operators.InternalTimerServiceImpl.onProcessingTime(InternalTimerServiceImpl.java:284)
    at org.apache.flink.streaming.runtime.tasks.StreamTask.invokeProcessingTimeCallback(StreamTask.java:1327)
    ... 10 more
Caused by: java.sql.SQLException: connection disabled
    at com.alibaba.druid.pool.DruidPooledConnection.checkStateInternal(DruidPooledConnection.java:1185)
    at com.alibaba.druid.pool.DruidPooledConnection.checkState(DruidPooledConnection.java:1170)
    at com.alibaba.druid.pool.DruidPooledConnection.setAutoCommit(DruidPooledConnection.java:718)
    at com.meiyijia.pd.flink.meta.sink.dwd.MySQLSinkToOptions.invoke(MySQLSinkToOptions.java:73)
    at com.meiyijia.pd.flink.meta.sink.dwd.MySQLSinkToOptions.invoke(MySQLSinkToOptions.java:21)
    at org.apache.flink.streaming.api.operators.StreamSink.processElement(StreamSink.java:54)
    at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.pushToOperator(CopyingChainingOutput.java:71)
    ... 22 more
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 41,094,955 milliseconds ago. The last packet sent successfully to the server was 41,094,955 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:2056)
    at com.alibaba.druid.pool.DruidPooledConnection.setAutoCommit(DruidPooledConnection.java:729)
    at com.meiyijia.pd.flink.meta.sink.dwd.MySQLSinkToOptions.invoke(MySQLSinkToOptions.java:38)
    ... 25 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 41,094,955 milliseconds ago. The last packet sent successfully to the server was 41,094,955 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
    at com.mysql.cj.protocol.a.NativeProtocol.send(NativeProtocol.java:569)
    at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:624)
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:940)
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:886)
    at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1073)
    at com.mysql.cj.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:2046)
    ... 27 more
Caused by: java.net.SocketException: Broken pipe (Write failed)
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.cj.protocol.a.SimplePacketSender.send(SimplePacketSender.java:55)
    at com.mysql.cj.protocol.a.TimeTrackingPacketSender.send(TimeTrackingPacketSender.java:50)
    at com.mysql.cj.protocol.a.NativeProtocol.send(NativeProtocol.java:560)
    ... 32 more

二、原因分析

　　1、mysql5以上的，设置autoReconnect=true是有效的，只有4.x版本不起作用

　　2、即使在创建Mysql时url中加入了autoReconnect=true参数,一但这个连接两次访问数据库的时间超出了服务器端wait_timeout的时间限制,还是会CommunicationsException: The last packet successfully received from the server was xxx milliseconds ago.

　　3、较好的策略是对处于idle状态的connection定时发送一个sql,来刷新服务器上的时间戳.这可以使用德鲁伊连接池的连接， 在默认配置不改变的情况下，如果连续8小时内都没有访问数据库的操作，再次访问mysql数据库的时候，mysql数据库会拒绝访问

三、Flink自定义MySQLSink出现连接失效解决方案

　　1、配置如下相关参数

#初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
initialSize=1
#最大连接池数量
maxActive=20
#最小连接池数量
minIdle=10
#获取连接时最大等待时间，单位毫秒
maxWait=30000
#申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testOnBorrow=true
#归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testOnReturn=true
#建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效
testWhileIdle=true
#如果连接泄露，是否需要回收泄露的连接，默认false
removeAbandoned=false
#如果回收了泄露的连接，是否要打印一条log，默认false；
logAbandoned=false
#连接回收的超时时间，默认5分钟，单位为毫秒
removeAbandonedTimeoutMillis=300000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis=10000
#Druid用来测试连接是否可用的SQL语句
validationQuery=select 1
#Druid用来测试连接是否可用的SQL语句超时时间
validationQueryTimeout=10000
#连接池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作
keepAlive=true
#连接的最大物理连接时长，超过则会被强制回收
phyTimeoutMillis=600000
#是否将PreparedStatements放入连接池中，默认为true
poolPreparedStatements=true

　　2、在open方法中获取连接，在close方法中关闭连接，在invoke方法中进行一次判断，判断连接是否关闭或为null，若是则重新获取连接（加上此判断非常重要！！！）

import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MySQLSinkToRequest extends RichSinkFunction<List<Request>> {
    Connection cn = null;
    PreparedStatement ps = null;
    Logger logger = LoggerFactory.getLogger(this.getClass());

    @Override
    public void open(Configuration parameters) throws Exception {
        //获取连接
        cn = MySQLDruidDataPool.getConnBigdataMetaModule();
    }

    @Override
    public void invoke(List<Request> values, Context context) throws Exception {
        Map<String,String> duplicateMap = new HashMap<>();
        if (values.size() != 0){
            try {
                //判断连接是否关闭或为null，若是则重新获取连接（加上此判断非常重要！！！）
                if (cn == null || cn.isClosed()){
                    cn = MySQLDruidDataPool.getConnBigdataMetaModule();
                }
                cn.setAutoCommit(false);
                //插入/更新语句
                ps = cn.prepareStatement(YouSQL);
                long i = 0;
                for (Request ws : values) {
                    String key = ws.getRequestId();
                    String value = duplicateMap.get(key);
                    if (value != null && value.length() != 0){
                        //去重，防止出现死锁
                    }else {
                        duplicateMap.put(key, "true");
                        ps.setString(1, ws.getRequestId());
                        ps.setString(2, ws.getMatchTypeOptionValue());
                        ps.addBatch();
                        i = i + 1;
                        if (i % 2000 == 0) {
                            ps.executeBatch();
                            cn.commit();
                            ps.clearBatch();
                        }
                    }
                }
                ps.executeBatch();
                cn.commit();
                ps.clearBatch();
            }catch (Exception e) {
                logger.error("Request,Data writing failed");
                e.printStackTrace();
            }
        }else {
            logger.info("values数量为0");
        }
    }

    @Override
    public void close() throws Exception {
        cn.setAutoCommit(true);
        //关闭连接
        MySQLDruidDataPool.close(cn,ps);
    }
}

　　连接池相关知识参考地址：https://blog.csdn.net/qq_21137441/article/details/102916199

四、工具说明

1、MySQLDruidDataPool

package com.meiyijia.pd.flink.meta.utils.db.mysql;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.typesafe.config.Config;
import com.typesafe.config.ConfigFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.Properties;

public class MySQLDruidDataPool {

    static Logger log = LoggerFactory.getLogger(MySQLDruidDataPool.class);

    static DruidDataSource druidDataSourceBigdataMetaModule;

    static String mysql_url_db_bigdata_meta_module = null;
    static String mysql_cj_driver = null;
    static String mysql_username = null;

    /**
     * 初始化线程池
     */
    static {
        Config load_comment = ConfigFactory.load("comment/comment.properties");
        String mode = load_comment.getString("mode");
        Config load = null;
        if (mode.equals("test")){
            load = ConfigFactory.load(mode + "/flink-" + mode + ".properties");
        }else if (mode.equals("prd")){
            load = ConfigFactory.load(mode + "/flink-" + mode + ".properties");
        }else if (mode.equals("dev")){
            load = ConfigFactory.load(mode + "/flink-" + mode + ".properties");
        }
        String mysql_driver = load.getString("mysql_driver");
        mysql_cj_driver = load.getString("mysql_cj_driver");
        String mysql_url = load.getString("mysql_url");
        mysql_url_db_bigdata_meta_module = load.getString("mysql_url_db_bigdata_meta_module");
        mysql_username = load.getString("mysql_username");
        String mysql_password = load.getString("mysql_password");
        String initialSize = load.getString("initialSize");
        String maxActive = load.getString("maxActive");
        String minIdle = load.getString("minIdle");
        String maxWait = load.getString("maxWait");
        String validationQuery = load.getString("validationQuery");
        String validationQueryTimeout = load.getString("validationQueryTimeout");
        String testOnBorrow = load.getString("testOnBorrow");
        String testOnReturn = load.getString("testOnReturn");
        String testWhileIdle = load.getString("testWhileIdle");
        String removeAbandoned = load.getString("removeAbandoned");
        String logAbandoned = load.getString("logAbandoned");
        String removeAbandonedTimeoutMillis = load.getString("removeAbandonedTimeoutMillis");
        String timeBetweenEvictionRunsMillis = load.getString("timeBetweenEvictionRunsMillis");
        String keepAlive = load.getString("keepAlive");
        String phyTimeoutMillis = load.getString("phyTimeoutMillis");
        String poolPreparedStatements = load.getString("poolPreparedStatements");

        Properties properties = new Properties();//配置文件对象
        properties.setProperty("driverClassName", mysql_driver);
        properties.setProperty("username",mysql_username);
        properties.setProperty("password",mysql_password);
        //初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
        properties.setProperty("initialSize",initialSize);
        //最大连接池数量
        properties.setProperty("maxActive",maxActive);
        //最小连接池数量
        properties.setProperty("minIdle",minIdle);
        //获取连接时最大等待时间，单位毫秒
        properties.setProperty("maxWait",maxWait);
        //用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
        properties.setProperty("validationQuery",validationQuery);
        properties.setProperty("validationQueryTimeout",validationQueryTimeout);
        properties.setProperty("timeBetweenEvictionRunsMillis",timeBetweenEvictionRunsMillis);
        //申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
        properties.setProperty("testOnBorrow",testOnBorrow);
        //归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        properties.setProperty("testOnReturn",testOnReturn);
        //建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        properties.setProperty("testWhileIdle",testWhileIdle);

        properties.setProperty("removeAbandoned",removeAbandoned);
        properties.setProperty("logAbandoned",logAbandoned);

        properties.setProperty("keepAlive",keepAlive);
        properties.setProperty("phyTimeoutMillis",phyTimeoutMillis);
        properties.setProperty("poolPreparedStatements",poolPreparedStatements);
        properties.setProperty("removeAbandonedTimeoutMillis",removeAbandonedTimeoutMillis);
        //url
        properties.setProperty("url",mysql_url);

        try {
            properties.setProperty("url",mysql_url_db_bigdata_meta_module);
            properties.setProperty("driverClassName", mysql_cj_driver);
            //加载配置文件,获取德鲁伊连接池
            druidDataSourceBigdataMetaModule = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取MYSQL连接
     * @return
     */
    public static Connection getConnBigdataMetaModule(){
        Connection con = null;
        try {
            con = druidDataSourceBigdataMetaModule.getConnection();
        }catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 关闭结果集和数据库处理对象(或预处理对象)和数据库连接对象(数据连接池本质上不会被关闭)
     * @param conn
     * @param ps
     */
    public static void close(Connection conn, PreparedStatement ps) {
        try{
            if(null != ps) {
                ps.close();
            }
            if(null != conn) {
                conn.close();
            }
        }catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 关闭结果集和数据库处理对象(或预处理对象)和数据库连接对象(数据连接池本质上不会被关闭)
     * @param conn
     * @param ps
     * @param rs
     */
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try{
            if(null != rs) {
                rs.close();
            }
            if(null != ps) {
                ps.close();
            }
            if(null != conn) {
                conn.close();
            }
        }catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

2、comment.properties

#开发环境、线上测试环境、线上生产环境
#mode=dev
mode=prd
#mode=test

3、flink-prd.properties

################## 生产环境 ###########################
################## MySQL 相关配置 开始###########################
mysql_driver=com.mysql.cj.jdbc.Driver
mysql_cj_driver=com.mysql.cj.jdbc.Driver
mysql_url_db_bigdata_meta_module=jdbc:mysql://xxx.xxx.xxx.xxx:3306/xxx?autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true&sslMode=disabled
mysql_username=xxx
mysql_password=xxx
#####JDBC配置
#批次大小
batchSize=20000
#批次时间，单位：毫秒
batchIntervalMs=30000
#最大重试次数
maxRetries=5
################## MySQL 相关配置 结束###########################
################## 连接池 相关配置 开始###########################
#初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
initialSize=1
#最大连接池数量
maxActive=20
#最小连接池数量
minIdle=10
#获取连接时最大等待时间，单位毫秒
maxWait=30000
#申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testOnBorrow=true
#归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testOnReturn=true
#建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效
testWhileIdle=true
#如果连接泄露，是否需要回收泄露的连接，默认false
removeAbandoned=false
#如果回收了泄露的连接，是否要打印一条log，默认false；
logAbandoned=false
#连接回收的超时时间，默认5分钟，单位为毫秒
removeAbandonedTimeoutMillis=300000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis=10000
#Druid用来测试连接是否可用的SQL语句
validationQuery=select 1
#Druid用来测试连接是否可用的SQL语句超时时间
validationQueryTimeout=10000
#连接池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作
keepAlive=true
#连接的最大物理连接时长，超过则会被强制回收
phyTimeoutMillis=600000
#是否将PreparedStatements放入连接池中，默认为true
poolPreparedStatements=true
################## 连接池 相关配置 结束###########################

4、驱动版本

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

5、配置文件