Flink cdc扫描过程报错Query execution was interrupted, maximum statement execution time exceeded

[丁浩浩]

我有一张大表有上亿条数据，但是在扫描过程中发生错误。提示超过最大执行时间，这个应该如何解决。

2020-11-18 17:07:43,658 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Step 7: - 104030000 of 131508640 rows scanned from table 'test.right_info' after 01:00:00.952
2020-11-18 17:07:44,131 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Step 7: - 104040000 of 131508640 rows scanned from table 'test.right_info' after 01:00:01.425
2020-11-18 17:07:44,601 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Step 7: committing transaction
2020-11-18 17:07:44,601 ERROR io.debezium.connector.mysql.SnapshotReader                   [] - Failed due to error: Aborting snapshot due to error when last running 'SELECT * FROM `test`.`right_info`': Query execution was interrupted, maximum statement execution time exceeded
org.apache.kafka.connect.errors.ConnectException: Query execution was interrupted, maximum statement execution time exceeded Error code: 3024; SQLSTATE: HY000.
    at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_91]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_91]
    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_91]
Caused by: java.sql.SQLException: Query execution was interrupted, maximum statement execution time exceeded
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1739) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.SnapshotReader.lambda$execute$14(SnapshotReader.java:648) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:473) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:641) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    ... 3 more
2020-11-18 17:07:44,606 INFO  io.debezium.connector.common.BaseSourceTask                  [] - Stopping down connector
2020-11-18 17:07:44,606 INFO  io.debezium.connector.mysql.MySqlConnectorTask               [] - Stopping MySQL connector task
2020-11-18 17:07:44,606 INFO  io.debezium.connector.mysql.ChainedReader                    [] - ChainedReader: Stopping the snapshot reader
2020-11-18 17:07:44,607 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Discarding 4363 unsent record(s) due to the connector shutting down
2020-11-18 17:07:44,607 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Discarding 0 unsent record(s) due to the connector shutting down
2020-11-18 17:07:44,608 INFO  io.debezium.connector.mysql.MySqlConnectorTask               [] - Connector task finished all work and is now shutdown
2020-11-18 17:07:44,609 ERROR com.alibaba.ververica.cdc.debezium.DebeziumSourceFunction    [] - Reporting error:
org.apache.kafka.connect.errors.ConnectException: Query execution was interrupted, maximum statement execution time exceeded Error code: 3024; SQLSTATE: HY000.
    at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_91]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_91]
    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_91]
Caused by: java.sql.SQLException: Query execution was interrupted, maximum statement execution time exceeded
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1739) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.SnapshotReader.lambda$execute$14(SnapshotReader.java:648) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:473) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:641) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
    ... 3 more
2020-11-18 17:07:46,386 INFO  io.debezium.embedded.EmbeddedEngine                          [] - Stopping the embedded engine
2020-11-18 17:08:34,081 INFO  org.apache.flink.metrics.slf4j.Slf4jReporter                 [] -

[hailongwang]

Hi,
   可以看下以下的文章是否对你有帮助[1][2]。
   如果不需要启动时候获得一次已有的快照，可以设置下 snapshot.mode 参数[3]


[1] https://dba.stackexchange.com/questions/134923/query-execution-was-interrupted-max-statement-time-exceeded
[2] https://mysqlserverteam.com/server-side-select-statement-timeouts/
[3] https://debezium.io/documentation/reference/1.3/connectors/mysql.html#mysql-property-snapshot-mode


Best,
Hailong Wang

在 2020-11-18 17:26:40，"丁浩浩" <[hidden email]> 写道：

>我有一张大表有上亿条数据，但是在扫描过程中发生错误。提示超过最大执行时间，这个应该如何解决。
>
>2020-11-18 17:07:43,658 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Step 7: - 104030000 of 131508640 rows scanned from table 'test.right_info' after 01:00:00.952
>2020-11-18 17:07:44,131 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Step 7: - 104040000 of 131508640 rows scanned from table 'test.right_info' after 01:00:01.425
>2020-11-18 17:07:44,601 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Step 7: committing transaction
>2020-11-18 17:07:44,601 ERROR io.debezium.connector.mysql.SnapshotReader                   [] - Failed due to error: Aborting snapshot due to error when last running 'SELECT * FROM `test`.`right_info`': Query execution was interrupted, maximum statement execution time exceeded
>org.apache.kafka.connect.errors.ConnectException: Query execution was interrupted, maximum statement execution time exceeded Error code: 3024; SQLSTATE: HY000.
>    at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_91]
>    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_91]
>    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_91]
>Caused by: java.sql.SQLException: Query execution was interrupted, maximum statement execution time exceeded
>    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1739) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.SnapshotReader.lambda$execute$14(SnapshotReader.java:648) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:473) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:641) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    ... 3 more
>2020-11-18 17:07:44,606 INFO  io.debezium.connector.common.BaseSourceTask                  [] - Stopping down connector
>2020-11-18 17:07:44,606 INFO  io.debezium.connector.mysql.MySqlConnectorTask               [] - Stopping MySQL connector task
>2020-11-18 17:07:44,606 INFO  io.debezium.connector.mysql.ChainedReader                    [] - ChainedReader: Stopping the snapshot reader
>2020-11-18 17:07:44,607 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Discarding 4363 unsent record(s) due to the connector shutting down
>2020-11-18 17:07:44,607 INFO  io.debezium.connector.mysql.SnapshotReader                   [] - Discarding 0 unsent record(s) due to the connector shutting down
>2020-11-18 17:07:44,608 INFO  io.debezium.connector.mysql.MySqlConnectorTask               [] - Connector task finished all work and is now shutdown
>2020-11-18 17:07:44,609 ERROR com.alibaba.ververica.cdc.debezium.DebeziumSourceFunction    [] - Reporting error:
>org.apache.kafka.connect.errors.ConnectException: Query execution was interrupted, maximum statement execution time exceeded Error code: 3024; SQLSTATE: HY000.
>    at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_91]
>    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_91]
>    at java.lang.Thread.run(Thread.java:745) [?:1.8.0_91]
>Caused by: java.sql.SQLException: Query execution was interrupted, maximum statement execution time exceeded
>    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1739) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.SnapshotReader.lambda$execute$14(SnapshotReader.java:648) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:473) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:641) ~[flink-connector-mysql-cdc-1.1.0.jar:1.1.0]
>    ... 3 more
>2020-11-18 17:07:46,386 INFO  io.debezium.embedded.EmbeddedEngine                          [] - Stopping the embedded engine
>2020-11-18 17:08:34,081 INFO  org.apache.flink.metrics.slf4j.Slf4jReporter                 [] -