mysql sink: affected rows metric may be inaccurate with multi-statements

What did you do?

Run a MySQL sink with the default multi-statement setting and inspect ticdc_sink_dml_event_affected_row_count.

The MySQL writer combines a batch into one request:

BEGIN;
statement1;
statement2;
COMMIT;

After the request succeeds, it calls sql.Result.RowsAffected() and records the result in the metric.

What did you expect to see?

The metric should report values that correspond to the committed DML statements.

What did you see instead?

go-sql-driver/mysql keeps an affected-rows result for every statement in a multi-statement request, but RowsAffected() returns only the last result. The last statement in this request is COMMIT, whose affected rows are normally 0.

For example, the driver may hold these results:

BEGIN       -> 0
statement1  -> 6
statement2  -> 4
COMMIT      -> 0

RowsAffected() returns 0 rather than the DML results. Consequently, the actual/total series may stay close to 0 even when rows are successfully written downstream.

The expected series has a separate accuracy problem with batch DML. It increments once per generated SQL statement, and the multi-statement path uses the number of generated statements. One batch INSERT containing 10 rows is one statement, so it can record expected=1 while MySQL reports actual=10.

Affected rows also do not always equal the number of input rows:

• An INSERT of 10 new rows normally reports 10.
• A DELETE attempted for 10 rows reports 6 if only 6 rows match.
• An UPDATE matching 10 rows reports 7 if only 7 values change under the default connection semantics.
• A REPLACE of 10 existing rows can report 20 because each replacement counts as a delete plus an insert.

This means a difference between actual and expected is not, by itself, proof of data inconsistency.

Impact

This affects the accuracy and interpretation of the affected-rows metric and its dashboard panel. Transaction execution, rollback, retry, checkpoint advancement, and downstream data correctness do not depend on RowsAffected().

Versions of the cluster

Observed on the current master implementation by code inspection. The repository currently uses go-sql-driver/mysql v1.9.3.

Start at the MySQL sink's multi-statement execution path and the call to sql.Result.RowsAffected(), then trace how actual and expected values feed ticdc_sink_dml_event_affected_row_count. Done means the metric reflects committed DML results for multi-statements, its batch-DML semantics are tested or documented, and the dashboard interpretation no longer treats ordinary affected-row differences as inconsistency.