importinto: forbid importing into TTL-enabled tables

Project: pingcap/tidb | Language: Go | Concepts: error-handling, testing-strategy, consistency-models | Difficulty: mid
Link: https://github.com/pingcap/tidb/pull/70427

The problem

The IMPORT INTO command in TiDB allowed importing data into tables that had TTL (Time To Live) enabled. Since TTL jobs can asynchronously delete rows while an import is running, this could lead to data inconsistencies, such as checksum mismatches after import. The system's table mode can block some operations, but it does not reliably prevent all issues related to TTL during imports.

Why it's non-trivial

A naive fix might only block imports based on table mode or rely on TTL-manager checks, but this is insufficient. TTL jobs might still race with import operations, and disabling prechecks (DISABLE_PRECHECK) could let dangerous imports proceed. The solution must ensure that imports into TTL-enabled tables are always blocked, regardless of other options or system state.

The approach

1. Synchronous precheck for TTL

The core logic adds a check in the importer precheck step to reject any target table with TTL enabled, regardless of other precheck options:

+	tableInfo := e.Plan.TableInfo
+	// Import table mode can prevent TTL from deleting data and causing a checksum
+	// mismatch. However, because TTL jobs run asynchronously, a job that races with the
+	// switch to import mode may still report a protected-table error. This precheck is
+	// also needed on versions that do not support table mode. Therefore, IMPORT INTO
+	// forbids importing into a table with TTL enabled. If later testing shows that IMPORT
+	// INTO can always switch table mode without an opt-out, TTL can check table mode before
+	// starting a job and this precheck can be removed.
+	if tableInfo.TTLInfo != nil && tableInfo.TTLInfo.Enable {
+		return exeerrors.ErrLoadDataPreCheckFailed.FastGenByArgs("target table has TTL enabled, please disable TTL before IMPORT INTO")
+	}

This snippet checks if TTL is enabled on the target table and, if so, returns a specific error message. This check is performed before any other prechecks and cannot be bypassed by disabling prechecks.

2. Unit test coverage for the TTL precheck

A new unit test ensures that the precheck rejects TTL-enabled tables and accepts tables when TTL is disabled, even if DISABLE_PRECHECK is set:

+func TestCheckRequirementsTTL(t *testing.T) {
+	// ... setup omitted ...
+	tk.MustExec("create table test.t(id int primary key, created_at datetime) TTL = `created_at` + INTERVAL 1 DAY")
+	// ... get table object ...
+	require.True(t, tableObj.Meta().TTLInfo.Enable)
+	// ... construct controller ...
+	err = c.CheckRequirements(ctx, tk.Session())
+	require.ErrorIs(t, err, exeerrors.ErrLoadDataPreCheckFailed)
+	require.ErrorContains(t, err, "target table has TTL enabled, please disable TTL before IMPORT INTO")
+	// ... disable TTL ...
+	tk.MustExec("alter table test.t ttl_enable = 'OFF'")
+	// ... check passes ...
+	require.NoError(t, c.CheckRequirements(ctx, tk.Session()))
+}

This test verifies that the precheck fails when TTL is enabled and passes when TTL is disabled, regardless of the precheck option.

3. Integration test for SQL-level behavior

Integration tests confirm that the error is raised as expected during actual SQL operations, even with DISABLE_PRECHECK:

+create table import_into_ttl_precheck_target (id int primary key, created_at datetime) TTL = `created_at` + INTERVAL 1 DAY;
+-- error 8173
+IMPORT INTO import_into_ttl_precheck_target FROM SELECT * FROM import_into_ttl_precheck_source WITH DISABLE_PRECHECK;

This ensures the check cannot be bypassed by users at the SQL level.

From the review thread

No notable review discussion on this PR.

Takeaways

• Always perform critical safety checks synchronously and unconditionally, especially when asynchronous background jobs (like TTL) can interfere with data consistency.
• Do not allow error-prone operations to be bypassed by user options (such as DISABLE_PRECHECK) if they can lead to corruption or inconsistency.
• Comprehensive testing (unit and integration) is essential to ensure that safety checks work as intended and cannot be circumvented.