Flink cdc connector：数据量较大时，snapshot阶段报错

[jindy_liu]

信息如下：

2020-11-27 18:52:01,040 INFO  io.debezium.connector.mysql.SnapshotReader                  
[] - Step 7: - 2910000 of 1311846828 rows scanned from table
'tag.tag_taggedinfo' after 00:16:38.994
2020-11-27 18:52:01,088 INFO  io.debezium.connector.mysql.SnapshotReader                  
[] - Step 7: committing transaction
2020-11-27 18:52:01,097 ERROR io.debezium.connector.mysql.SnapshotReader                  
[] - Failed due to error: Aborting snapshot due to error when last running
'SELECT * FROM `low_db`.`t_low_media`': Streaming result set
com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@1e658cc0 is still
active. No statements may be issued when any streaming result sets are open
and in use on a given connection. Ensure that you have called .close() on
any active streaming result sets before attempting more queries.
org.apache.kafka.connect.errors.ConnectException: Streaming result set
com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@1e658cc0 is still
active. No statements may be issued when any streaming result sets are open
and in use on a given connection. Ensure that you have called .close() on
any active streaming result sets before attempting more queries. Error code:
0; SQLSTATE: S1000.
        at
io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[?:1.8.0_71]
        at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[?:1.8.0_71]
        at java.lang.Thread.run(Thread.java:745) [?:1.8.0_71]
Caused by: java.sql.SQLException: Streaming result set
com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@1e658cc0 is still
active. No statements may be issued when any streaming result sets are open
and in use on a given connection. Ensure that you have called .close() on
any active streaming result sets before attempting more queries.
        at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:813)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:747)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        ... 3 more
2020-11-27 18:52:01,147 WARN  io.debezium.connector.mysql.SnapshotReader                  
[] - Failed to close the connection properly
java.sql.SQLException: Streaming result set
com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@1e658cc0 is still
active. No statements may be issued when any streaming result sets are open
and in use on a given connection. Ensure that you have called .close() on
any active streaming result sets before attempting more queries.
        at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
com.mysql.cj.jdbc.ConnectionImpl.rollbackNoChecks(ConnectionImpl.java:1961)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1855)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
com.mysql.cj.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:1720)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at com.mysql.cj.jdbc.ConnectionImpl.close(ConnectionImpl.java:720)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at io.debezium.jdbc.JdbcConnection.close(JdbcConnection.java:849)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:850)
~[blob_p-cdf63b97f57b02ab06e2520753201c5ebcc5134a-46cf2866fe3474b104bc42c62dfec68f:?]
        at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[?:1.8.0_71]
        at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[?:1.8.0_71]
        at java.lang.Thread.run(Thread.java:745) [?:1.8.0_71]
2020-11-27 18:52:01,292 INFO  io.debezium.connector.common.BaseSourceTask                
[] - Stopping down connector
2020-11-27 18:52:01,294 INFO  io.debezium.connector.mysql.MySqlConnectorTask              
[] - Stopping MySQL connector task
2020-11-27 18:52:01,298 INFO  io.debezium.connector.mysql.ChainedReader                  
[] - ChainedReader: Stopping the snapshot reader
2020-11-27 18:52:01,317 INFO  io.debezium.connector.mysql.SnapshotReader                  
[] - Discarding 0 unsent record(s) due to the connector shutting down        

请问有人有遇到过吗?
查了些资料，感觉命中了这个，https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html。

但不知道在flink cdc里怎么解决，debezium SnapshotReader 代码写死了。
 
    private Statement createStatementWithLargeResultSet(Connection
connection) throws SQLException {
        int fetchSize = context.getConnectorConfig().getSnapshotFetchSize();
        Statement stmt =
connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(fetchSize);
        return stmt;
    }

                                                                                                                                                                                                                                                                             



--
Sent from: http://apache-flink.147419.n8.nabble.com/

[chenjb]

老哥，我碰到了一样的问题，有找到原因吗？



--
Sent from: http://apache-flink.147419.n8.nabble.com/

[jindy_liu]

snapshot阶段如果后端处理的慢，就容易反压，反压会造成debezium执行select * from xxx的时候会花较长时间。

这个报错一般是mysql本身的原因。出现通信错误的原因挺复杂的，需要单独看。我的原因比较坑，定位也花了些时间！！！公司DBA会进行sql语句执行时长监控，并kill掉相应的sql，从而造成上述通信异常问题，

还有一些原因比如空闲时间太长了，mysql server也会断开连接。常见的这些是改mysql的配置，社区的jark
wu有一些分享配置，mysql-cdc-connector的github上也有分享。比如wait_timeout之类的。




--
Sent from: http://apache-flink.147419.n8.nabble.com/