You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Flink CDC Issue Import (Jira)" <ji...@apache.org> on 2024/03/20 09:29:03 UTC
[jira] [Commented] (FLINK-34833) [Bug][sqlserver] SqlServer incremental source cannot support exactly-once
[ https://issues.apache.org/jira/browse/FLINK-34833?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17828786#comment-17828786 ]
Flink CDC Issue Import commented on FLINK-34833:
------------------------------------------------
Date: Wed Dec 13 10:10:52 CST 2023, Author: [loserwang1024|https://github.com/loserwang1024]
> maybe we can set wait time before get lsn?
@gong , low_watermark smaller is no effect. But for high_watermark, what is the optimal waiting time to ensure that updated data is successfully transferred to CDC table?
> [Bug][sqlserver] SqlServer incremental source cannot support exactly-once
> --------------------------------------------------------------------------
>
> Key: FLINK-34833
> URL: https://issues.apache.org/jira/browse/FLINK-34833
> Project: Flink
> Issue Type: Bug
> Components: Flink CDC
> Reporter: Flink CDC Issue Import
> Priority: Major
> Labels: github-import
>
> ## Search before asking
> - [X] I searched in the [issues|https://github.com/ververica/flink-cdc-connectors/issues] and found nothing similar.
> ## Flink version
> 1.18
> ## Flink CDC version
> 3.0
> ## Reason
> ### overview
> At first, we can see what SqlServer incremental source do to guarantee exactly-once sematics in parallel read。
> First, split the table into multiple chunks based on the key, each chunk as a snapshot split. These splits can be read in parallel.
> In snapshot phase, for each snapshot split between [low_key, high_key]:
> 1. use SqlServerDialect#displayCurrentOffset to get LSN as low_watermark
> 2. read snapshot data between [low_key, high_key] as a temporary state `state1` by JDBC connection query.
> 3. use SqlServerDialect#displayCurrentOffset to get LSN as high_watermark
> 4. read log between (low_watermark, high_watermark), will update temporary state `state1` and generate **final state of high_watermark**, then emit to downstream.
> Then in stream phase, we read log between [high_watermark, +∞)for this split between [low_key, high_key].
> ### problem
> However, `SqlServerDialect#displayCurrentOffset → SqlServerUtils#currentLsn → SqlServerConnection#getMaxTransactionLsn` return the max LSN of system table `cdc.lsn_time_mapping` , which is not the real latest LSN of whole database system. In this incremental source framework, only the real latest LSN of whole database system can guarantee exactly-once sematics.
> As shown in https://stackoverflow.com/questions/29477391/cdc-data-only-shows-up-after-5-minutes, this developer find that [CDC Data Only Shows up After 5 Minutes|https://stackoverflow.com/questions/29477391/cdc-data-only-shows-up-after-5-minutes) , and the reason is :
> > Because the capture process extracts change data from the transaction log, there is a built in latency between the time that a change is committed to a source table and the time that the change appears within its associated change table.
> For example, low_mark and high_watermark maybe 5 minutes lower than the Lsn of step two(read snapshot data). Then in streaming phase, the log which should be ignored will be read again.
> ## How to verify
> Modify test: com.ververica.cdc.connectors.oracle.source.OracleSourceITCase#testEnableBackfillWithDMLPostLowWaterMark, we can see three dml operations are read twicely.
> ```java
> @Test
> public void testEnableBackfillWithDMLPostLowWaterMark() throws Exception {
> List<String> records = testBackfillWhenWritingEvents(false, 25, USE_POST_LOWWATERMARK_HOOK];
> List<String> expectedRecords =
> Arrays.asList(
> "+I[101, user_1, Shanghai, 123567891234]",
> "+I[102, user_2, Shanghai, 123567891234]",
> "+I[103, user_3, Shanghai, 123567891234]",
> "+I[109, user_4, Shanghai, 123567891234]",
> "+I[110, user_5, Shanghai, 123567891234]",
> "+I[111, user_6, Shanghai, 123567891234]",
> "+I[118, user_7, Shanghai, 123567891234]",
> "+I[121, user_8, Shanghai, 123567891234]",
> "+I[123, user_9, Shanghai, 123567891234]",
> "+I[1009, user_10, Shanghai, 123567891234]",
> "+I[1010, user_11, Shanghai, 123567891234]",
> "+I[1011, user_12, Shanghai, 123567891234]",
> "+I[1012, user_13, Shanghai, 123567891234]",
> "+I[1013, user_14, Shanghai, 123567891234]",
> "+I[1014, user_15, Shanghai, 123567891234]",
> "+I[1015, user_16, Shanghai, 123567891234]",
> "+I[1016, user_17, Shanghai, 123567891234]",
> "+I[1017, user_18, Shanghai, 123567891234]",
> "+I[1018, user_19, Shanghai, 123567891234]",
> "+I[2000, user_21, Pittsburgh, 123567891234]",
> "+I[15213, user_15213, Shanghai, 123567891234]",
> // the operations already be applied in snapshot phase, but are read again in streaming phase
> "+I[15213, user_15213, Shanghai, 123567891234]",
> "-U[2000, user_21, Shanghai, 123567891234]",
> "+U[2000, user_21, Pittsburgh, 123567891234]",
> "-D[1019, user_20, Shanghai, 123567891234]");
> assertEqualsInAnyOrder(expectedRecords, records);
> }
> private List<String> testBackfillWhenWritingEvents(
> boolean skipSnapshotBackfill, int fetchSize, int hookType) throws Exception {
> createAndInitialize("customer.sql");
> StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
> env.enableCheckpointing(200L);
> env.setParallelism(1);
> ResolvedSchema customersSchema =
> new ResolvedSchema(
> Arrays.asList(
> physical("ID", BIGINT().notNull()),
> physical("NAME", STRING()),
> physical("ADDRESS", STRING()),
> physical("PHONE_NUMBER", STRING())),
> new ArrayList<>(),
> UniqueConstraint.primaryKey("pk", Collections.singletonList("ID")));
> TestTable customerTable =
> new TestTable(ORACLE_DATABASE, ORACLE_SCHEMA, "CUSTOMERS", customersSchema);
> String tableId = customerTable.getTableId();
> OracleSourceBuilder.OracleIncrementalSource source =
> OracleSourceBuilder.OracleIncrementalSource.<RowData>builder()
> .hostname(ORACLE_CONTAINER.getHost())
> .port(ORACLE_CONTAINER.getOraclePort())
> .username(CONNECTOR_USER)
> .password(CONNECTOR_PWD)
> .databaseList(ORACLE_DATABASE)
> .schemaList(ORACLE_SCHEMA)
> .tableList("DEBEZIUM.CUSTOMERS")
> .skipSnapshotBackfill(skipSnapshotBackfill)
> .startupOptions(StartupOptions.initial())
> .deserializer(customerTable.getDeserializer())
> .build();
> // Do some database operations during hook in snapshot period.
> SnapshotPhaseHooks hooks = new SnapshotPhaseHooks();
> String[] statements =
> new String[] {
> String.format(
> "INSERT INTO %s VALUES (15213, 'user_15213', 'Shanghai', '123567891234')",
> tableId),
> String.format("UPDATE %s SET address='Pittsburgh' WHERE id=2000", tableId),
> String.format("DELETE FROM %s WHERE id=1019", tableId)
> };
> SnapshotPhaseHook snapshotPhaseHook =
> (sourceConfig, split) -> {
> // database update operations use TEST_USER rather than CONNECTOR_USER
> JdbcConfiguration configuration =
> JdbcConfiguration.copy(
> ((JdbcSourceConfig) sourceConfig)
> .getDbzConnectorConfig()
> .getJdbcConfig())
> .withUser("debezium")
> .withPassword("dbz")
> .build();
> try (OracleConnection oracleConnection =
> OracleConnectionUtils.createOracleConnection(configuration)) {
> oracleConnection.setAutoCommit(false);
> oracleConnection.execute(statements);
> oracleConnection.commit();
> }
> };
> if (hookType == USE_POST_LOWWATERMARK_HOOK) {
> hooks.setPostLowWatermarkAction(snapshotPhaseHook);
> } else if (hookType == USE_PRE_HIGHWATERMARK_HOOK) {
> hooks.setPreHighWatermarkAction(snapshotPhaseHook);
> }
> source.setSnapshotHooks(hooks);
> List<String> records = new ArrayList<>();
> try (CloseableIterator<RowData> iterator =
> env.fromSource(source, WatermarkStrategy.noWatermarks(), "Backfill Skipped Source")
> .executeAndCollect()) {
> records = fetchRowData(iterator, fetchSize, customerTable::stringify);
> env.close();
> }
> return records;
> }
> ```
> ### Are you willing to submit a PR?
> - [ ] I'm willing to submit a PR!
> ---------------- Imported from GitHub ----------------
> Url: https://github.com/apache/flink-cdc/issues/2853
> Created by: [loserwang1024|https://github.com/loserwang1024]
> Labels: bug,
> Assignee: [loserwang1024|https://github.com/loserwang1024]
> Created at: Tue Dec 12 10:44:24 CST 2023
> State: open
--
This message was sent by Atlassian Jira
(v8.20.10#820010)