Fallback to txn mode when adding unique index

[tangenta]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Add unique index for a distinct column on a huge table.

2. What did you expect to see? (Required)

No error.

3. What did you see instead (Required)

[INFO] [index.go:764] ["[ddl] fallback to txn-merge backfill process"] [error="[Lightning:Restore:ErrFoundDuplicateKey]found duplicate key

4. What is your TiDB version? (Required)

7389b2b

[sleepymole]

Let me add more details.

Test data

dbgen template

CREATE TABLE `test`.`multi_indexes` (
 `f_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  /*{{ rownum }}*/
  `f_user_id` bigint(20) unsigned NOT NULL,
  /*{{ rand.range(1, 9223372036854775808) }}*/
  `f_account_id` bigint(20) unsigned NOT NULL  ,
  /*{{ rand.range(1, 9223372036854775808) }}*/
  `f_subaccount_id` bigint(20) unsigned NOT NULL  ,
  /*{{ rand.range(1, 9223372036854775808) }}*/
  `f_transaction_id` bigint(20) unsigned NOT NULL  ,
  /*{{ rownum }}*/
  `f_transaction_type` tinyint(3) unsigned NOT NULL  ,
  /*{{ rand.range(1, 10) }}*/
  `f_currency` varchar(10) NOT NULL  ,
  /*{{ rand.regex('[a-z0-9]+', '', 5) }}*/
  `f_balance` decimal(36,18) NOT NULL DEFAULT '0.000000000000000000'  ,
  /*{{ rand.range(1, 100000) }}*/
  `f_amount` decimal(36,18) NOT NULL DEFAULT '0.000000000000000000'  ,
  /*{{ rand.range(1, 10000000) }}*/
  `f_precision` tinyint(4) NOT NULL DEFAULT '0'  ,
  /*{{ rand.range(0, 20) }}*/
  `f_state` tinyint(4) NOT NULL  ,
  /*{{ rand.range(0, 5) }}*/
  `f_created_at` bigint(20) unsigned NOT NULL DEFAULT '0'  ,
  /*{{ rand.range(1610535068, 1676882755) }}*/
  `f_updated_at` bigint(20) unsigned NOT NULL DEFAULT '0'  ,
  /*{{ rand.range(1610535068, 1676882755) }}*/
  `f_version` bigint(20) unsigned NOT NULL DEFAULT '0'  ,
  /*{{ rand.range(1, 10000) }}*/
  PRIMARY KEY (`f_id`),
  UNIQUE KEY `uniq_subaccount_transaction` (`f_subaccount_id`,`f_transaction_type`,`f_transaction_id`),
  KEY `idx_user_id` (`f_user_id`),
  KEY `idx_account_state_subaccount` (`f_account_id`,`f_state`,`f_subaccount_id`),
  KEY `idx_state_precision` (`f_state`,`f_precision`),
  KEY `idx_updated_at` (`f_updated_at`)
);

dbgen command

./dbgen -i multi_indexes.sql -o test-data/multi_indexes -N 4000000000 -R 2000000 -r 1000

The generated data size is approximately 562GiB. It's guaranteed that there is no unique conflict in the test data.

Test steps

Firstly, I use tidb-lightning to import the table without any secondary index. Then I execute a SQL to add all indexes.

ALTER TABLE `test`.`multi_indexes`
  ADD UNIQUE INDEX `uniq_subaccount_transaction` (`f_subaccount_id`,`f_transaction_type`,`f_transaction_id`),
  ADD INDEX `idx_user_id` (`f_user_id`),
  ADD INDEX `idx_account_state_subaccount` (`f_account_id`,`f_state`,`f_subaccount_id`),
  ADD INDEX `idx_state_precision` (`f_state`,`f_precision`),
  ADD INDEX `idx_updated_at` (`f_updated_at`);

I did two tests and got this error both times.

[2023/02/21 11:14:52.386 +00:00] [ERROR] [backend.go:104] ["[ddl-ingest] ingest data into storage error"] ["index ID"=2] [error="[Lightning:Restore:ErrFoundDuplicateKey]found duplicate key 't\ufffd\u0000\u0000\u0000\u0000\u0000\u0000T_i\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0002\u0004\u0000^\ufffd\ufffd\u0014\ufffdza\u0004\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0007\u0004\u0000\u0000\u0000\u0000M\ufffd\ufffd\u0011', value '\u0000\u0000\u0000\u0000M\ufffd\ufffd\u0011'"] ["current disk usage"=100111059775] ["max disk quota"=107374182400]
...
[2023/02/21 11:14:57.259 +00:00] [INFO] [index.go:764] ["[ddl] fallback to txn-merge backfill process"] [error="[Lightning:Restore:ErrFoundDuplicateKey]found duplicate key 't\ufffd\u0000\u0000\u0000\u0000\u0000\u0000T_i\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0002\u0004\u0000^\ufffd\ufffd\u0014\ufffdza\u0004\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0007\u0004\u0000\u0000\u0000\u0000M\ufffd\ufffd\u0011', value '\u0000\u0000\u0000\u0000M\ufffd\ufffd\u0011'"]

The full tidb log is in tidb.log.gz