[Bug] CDC streaming job with offset=latest/earliest fails on SSL-required MySQL because SSL properties are applied after JDBC connection

[maks3201]

Version and Environment

• Apache Doris version: 4.1.2 (also reproduced on 4.1.3-rc02, bug is present on master/4.2-SNAPSHOT)
• Deployment mode: Compute-storage-decoupled (cloud mode), but the bug also applies to shared-nothing mode
• Source database: MySQL 8.0 / Amazon Aurora MySQL 8.0 (8.0.mysql_aurora.3.10.3) with require_secure_transport=ON

What happened

A CDC streaming job configured with offset='latest' (or earliest) and ssl_mode='require' immediately fails with a JDBC connection error at job startup. The job never begins replication.

What was expected

The job should connect using SSL to resolve the current binlog position and then begin streaming.

Minimal Reproduction

1. Configure MySQL/Aurora with require_secure_transport=ON:

-- On MySQL:
SET GLOBAL require_secure_transport = ON;

2. Upload a CA certificate to Doris (if using verify-ca, otherwise ssl_mode=require suffices):

CREATE FILE "mysql_ca.pem"
PROPERTIES ("url" = "file:///path/to/rds-combined-ca-bundle.pem", "catalog" = "internal");

3. Create a CDC streaming job:

CREATE JOB my_cdc_job
PROPERTIES (
    'type' = 'insert',
    'format' = 'cdc'
)
FROM MYSQL (
    'host' = '10.0.1.100',
    'port' = '3306',
    'user' = 'cdc_user',
    'password' = '***',
    'database' = 'mydb',
    'table' = 'orders',
    'offset' = 'latest',
    'ssl_mode' = 'require'
)
INTO TABLE mydb.orders;

4. The job fails immediately. The BE cdc_client log shows:

java.sql.SQLException: Could not create connection to database server.
Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol

or (depending on MySQL configuration):

java.sql.SQLException: Connections using insecure transport are prohibited while --require_secure_transport=ON

Root Cause

In MySqlSourceReader.generateMySqlConfig() (fs_brokers/cdc_client/src/main/java/org/apache/doris/cdcclient/source/reader/mysql/MySqlSourceReader.java), the startup mode switch block (starting at line 907 in tag 4.1.2) calls initializeEffectiveOffset() for latest, earliest, and timestamp modes. This method creates a MySqlConnection via DebeziumUtils.createMySqlConnection(config) — i.e., it opens a JDBC connection to the source database to resolve the current binlog file and position.

However, the SSL properties (ssl_mode → database.ssl.mode / sslMode, and ssl_rootcert → truststore path) are applied to jdbcProperties and dbzProps after the startup mode block (line 955+ in tag 4.1.2). So when initializeEffectiveOffset() builds its config and opens a connection, SSL is not yet configured. Against a MySQL instance with require_secure_transport=ON, the plaintext connection attempt is rejected.

Affected Modes

• offset='latest' — always calls initializeEffectiveOffset()
• offset='earliest' — always calls initializeEffectiveOffset()
• Timestamp offset (13-digit epoch) — always calls initializeEffectiveOffset()

The offset='initial' and offset='snapshot' modes are NOT affected because they do not resolve binlog position at configuration time.

Suggested Fix

Move the JDBC properties + SSL configuration block to before the startup mode switch block. When SSL properties are absent, the behaviour is identical (the JDBC connection simply doesn't use SSL). The reordering has no effect on non-SSL users.

[Doris-Breakwater]

Breakwater initial analysis

Decision

Confirmed, actionable code defect. This is not explained by cloud mode or Aurora-specific behavior: the failure is in the shared Java CDC-client configuration path, before replication starts. It is a startup blocker for SSL-required MySQL when Doris must resolve an effective offset; there is no evidence here of data corruption.

The issue currently has no labels. Suggested triage is the repository's bug + streaming-job/CDC labels, plus the appropriate 4.1 backport label after a maintainer confirms the target branch.

Verified facts

• In tag 4.1.2, generateMySqlConfig() evaluates the startup mode at lines 907-953, before constructing and attaching jdbcProperteis / dbzProps at lines 955-986. The same ordering is present in 4.1.3-rc02 and current master.
• latest, earliest, and a 13-digit timestamp all call initializeEffectiveOffset(). That method immediately calls configFactory.createConfig(0) and passes the resulting config to DebeziumUtils.createMySqlConnection().
• Flink CDC's MySqlSourceConfigFactory.createConfig() substitutes empty JDBC properties when none have been attached. In the bundled Debezium version, the default database.ssl.mode is disabled; the forked MySQL connection therefore builds this early connection without the requested sslMode=required or truststore properties.
• The job's ssl_mode mapping, Connector/J sslMode, and truststore properties are only added after that early connection attempt. This confirms the reported root cause and explains rejection by a server with require_secure_transport=ON.
• initial, snapshot, and an explicit JSON file/position or GTID offset do not call initializeEffectiveOffset() during config generation, so they do not hit this particular early-connection defect. They are not necessarily equivalent operational workarounds because their startup semantics differ.
• Existing tests cover SSL with offset=initial and cover latest/earliest without mandatory TLS. They do not cover the failing combination.

The reported Aurora/MySQL runtime errors and manual validation are plausible and consistent with the code, but I did not independently run an Aurora or TLS-required MySQL reproduction. No additional reporter logs are required to establish the code defect.

Existing fix

PR #66559 is already open and targets master. Its fix direction is correct: it moves property construction and, importantly, both configFactory.jdbcProperties(...) and configFactory.debeziumProperties(...) ahead of the startup-mode block, so the temporary config created by initializeEffectiveOffset() contains the SSL settings. Merely moving local Properties population without moving those factory setter calls would not fix the bug; the current PR does move them.

At the time of this analysis, the PR has no human review and no regression test; its checks are still pending. The change is confined to MySqlSourceReader.java.

Recommended next steps

1. Add a focused integration regression using the module's existing MySQL Testcontainers infrastructure (for example, extend the MySqlStartupLatestITCase path): configure MySQL with TLS and require_secure_transport=ON, pass ssl_mode=require, and assert that effective-offset resolution and subsequent streaming succeed. At minimum cover latest; ideally parameterize latest, earliest, and timestamp because all three use the same early connection path.
2. Also retain a non-SSL latest check to verify the reorder does not change existing behavior. If verify-ca is in scope, add a CA/truststore case separately from require, since it exercises additional properties.
3. Complete review and CI for [fix](cdc) move SSL/JDBC config before startup mode to fix offset on SSL-required MySQL #66559, then backport to the maintained 4.1 branch. The PR author requested dev/4.1.x; a committer should confirm/apply the repository's current backport label rather than relying on an assumed internal tag convention.
4. Until a fixed build is available, use initial, snapshot, or a known explicit offset only when the changed startup semantics are acceptable. Do not weaken MySQL's secure-transport requirement as a workaround.

Breakwater-GitHub-Analysis-Slot: slot_042c2292c8a8