TiDB Lightning Error Resolution

Starting from v5.4.0, you can configure TiDB Lightning to skip errors like invalid type conversion and unique key conflicts, and to continue the data processing as if those wrong row data does not exist. A report will be generated for you to read and manually fix errors afterward. This is ideal for importing from a slightly dirty data source, where locating the errors manually is difficult and restarting TiDB Lightning on every encounter is costly.

This document introduces how to use the type error feature (lightning.max-error) and the duplicate resolution feature (tikv-importer.duplicate-resolution). It also introduces the database where these errors are stored (lightning.task-info-schema-name). At the end of this document, an example is provided.

Type error

You can use the lightning.max-error configuration to increase the tolerance of errors related to data types. If this configuration is set to N, TiDB Lightning allows and skips up to N errors from the data source before it exists. The default value 0 means that no error is allowed.

These errors are recorded in a database. After the import is completed, you can view the errors in the database and process them manually. For more information, see Error Report.

  1. [lightning]
  2. max-error = 0

The above configuration covers the following errors:

  • Invalid values (example: set 'Text' to an INT column).
  • Numeric overflow (example: set 500 to a TINYINT column)
  • String overflow (example: set 'Very Long Text' to a VARCHAR(5) column).
  • Zero date-time (namely '0000-00-00' and '2021-12-00').
  • Set NULL to a NOT NULL column.
  • Failed to evaluate a generated column expression.
  • Column count mismatch. The number of values in the row does not match the number of columns of the table.
  • Unique/Primary key conflict in TiDB-backend, when on-duplicate = "error".
  • Any other SQL errors.

The following errors are always fatal, and cannot be skipped by changing max-error:

  • Syntax error (such as unclosed quotation marks) in the original CSV, SQL or Parquet file.
  • I/O, network or system permission errors.

Unique/Primary key conflict in the physical import mode is handled separately and explained in the next section.

Error report

If TiDB Lightning encounters errors during the import, it outputs a statistics summary about these errors in both your terminal and the log file when it exits.

  • The error report in the terminal is similar to the following table:

    #ERROR TYPEERROR COUNTERROR DATA TABLE
    1Data Type1000lightning_task_info.type_error_v1

  • The error report in the TiDB Lightning log file is as follows:

    1. [2022/03/13 05:33:57.736 +08:00] [WARN] [errormanager.go:459] ["Detect 1000 data type errors in total, please refer to table `lightning_task_info`.`type_error_v1` for more details"]

All errors are written to tables in the lightning_task_info database in the downstream TiDB cluster. After the import is completed, if the error data is collected, you can view the errors in the database and process them manually.

You can change the database name by configuring lightning.task-info-schema-name.

  1. [lightning]
  2. task-info-schema-name = 'lightning_task_info'

TiDB Lightning creates 3 tables in this database:

  1. CREATE TABLE syntax_error_v1 (
  2.     task_id     bigint NOT NULL,
  3.     create_time datetime(6) NOT NULL DEFAULT now(6),
  4.     table_name  varchar(261) NOT NULL,
  5.     path        varchar(2048) NOT NULL,
  6.     offset      bigint NOT NULL,
  7.     error       text NOT NULL,
  8.     context     text
  9. );
  11. CREATE TABLE type_error_v1 (
  12.     task_id     bigint NOT NULL,
  13.     create_time datetime(6) NOT NULL DEFAULT now(6),
  14.     table_name  varchar(261) NOT NULL,
  15.     path        varchar(2048) NOT NULL,
  16.     offset      bigint NOT NULL,
  17.     error       text NOT NULL,
  18.     row_data    text NOT NULL
  19. );
  21. CREATE TABLE conflict_error_v1 (
  22.     task_id     bigint NOT NULL,
  23.     create_time datetime(6) NOT NULL DEFAULT now(6),
  24.     table_name  varchar(261) NOT NULL,
  25.     index_name  varchar(128) NOT NULL,
  26.     key_data    text NOT NULL,
  27.     row_data    text NOT NULL,
  28.     raw_key     mediumblob NOT NULL,
  29.     raw_value   mediumblob NOT NULL,
  30.     raw_handle  mediumblob NOT NULL,
  31.     raw_row     mediumblob NOT NULL,
  32.     KEY (task_id, table_name)
  33. );

type_error_v1 records all type errors managed by the max-error configuration. There is one row per error.

conflict_error_v1 records all unique/primary key conflict in the Local-backend. There are 2 rows per pair of conflicts.

ColumnSyntaxTypeConflictDescription
task_id✓✓✓The TiDB Lightning task ID that generates this error
create_time✓✓✓The time at which the error is recorded
table_name✓✓✓The name of the table that contains the error, in the form of ‘db.tbl‘
path✓✓The path of the file that contains the error
offset✓✓The byte position in the file where the error is found
error✓✓The error message
context✓The text that surrounds the error
index_name✓The name of the unique key in conflict. It is ‘PRIMARY’ for primary key conflicts.
key_data✓The formatted key handle of the row that causes the error. The content is for human reference only, and not intended to be machine-readable.
row_data✓✓The formatted row data that causes the error. The content is for human reference only, and not intended to be machine-readable
raw_key✓The key of the conflicted KV pair
raw_value✓The value of the conflicted KV pair
raw_handle✓The row handle of the conflicted row
raw_row✓The encoded value of the conflicted row

Error Resolution - 图1

Note

The error report records the file offset, not line/column number which is inefficient to obtain. You can quickly jump near a byte position (using 183 as example) using the following commands:

  • shell, printing the first several lines.

    1. head -c 183 file.csv | tail

  • shell, printing the next several lines:

    1. tail -c +183 file.csv | head

  • vim — :goto 183 or 183go

Example

In this example, a data source is prepared with some known errors.

  1. Prepare the database and table schema.

    1. mkdir example && cd example
    3. echo 'CREATE SCHEMA example;' > example-schema-create.sql
    4. echo 'CREATE TABLE t(a TINYINT PRIMARY KEY, b VARCHAR(12) NOT NULL UNIQUE);' > example.t-schema.sql

  2. Prepare the data.

    1. cat <<EOF > example.t.1.sql
    3.     INSERT INTO t (a, b) VALUES
    4.     (0, NULL),              -- column is NOT NULL
    5.     (1, 'one'),
    6.     (2, 'two'),
    7.     (40, 'forty'),          -- conflicts with the other 40 below
    8.     (54, 'fifty-four'),     -- conflicts with the other 'fifty-four' below
    9.     (77, 'seventy-seven'),  -- the string is longer than 12 characters
    10.     (600, 'six hundred'),   -- the number overflows TINYINT
    11.     (40, 'forty'),         -- conflicts with the other 40 above
    12.     (42, 'fifty-four');     -- conflicts with the other 'fifty-four' above
    14. EOF

  3. Configure TiDB Lightning to enable strict SQL mode, use the Local-backend to import data, delete duplicates, and skip up to 10 errors.

    1. cat <<EOF > config.toml
    3.     [lightning]
    4.     max-error = 10
    6.     [tikv-importer]
    7.     backend = 'local'
    8.     sorted-kv-dir = '/tmp/lightning-tmp/'
    9.     duplicate-resolution = 'remove'
    11.     [mydumper]
    12.     data-source-dir = '.'
    13.     [tidb]
    14.     host = '127.0.0.1'
    15.     port = 4000
    16.     user = 'root'
    17.     password = ''
    18.     sql-mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE'
    20. EOF

  4. Run TiDB Lightning. This command will exit successfully because all errors are skipped.

    1. tiup tidb-lightning -c config.toml

  5. Verify that the imported table only contains the two normal rows:

    1. $ mysql -u root -h 127.0.0.1 -P 4000 -e 'select * from example.t'
    2. +---+-----+
    3. | a | b   |
    4. +---+-----+
    5. | 1 | one |
    6. | 2 | two |
    7. +---+-----+

  6. Check whether the type_error_v1 table has caught the three rows involving type conversion:

    1. $ mysql -u root -h 127.0.0.1 -P 4000 -e 'select * from lightning_task_info.type_error_v1;' -E
    3. *************************** 1. row ***************************
    4.     task_id: 1635888701843303564
    5. create_time: 2021-11-02 21:31:42.620090
    6.  table_name: `example`.`t`
    7.        path: example.t.1.sql
    8.      offset: 46
    9.       error: failed to cast value as varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin for column `b` (#2): [table:1048]Column 'b' cannot be null
    10.    row_data: (0,NULL)
    12. *************************** 2. row ***************************
    13.     task_id: 1635888701843303564
    14. create_time: 2021-11-02 21:31:42.627496
    15.  table_name: `example`.`t`
    16.        path: example.t.1.sql
    17.      offset: 183
    18.       error: failed to cast value as varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin for column `b` (#2): [types:1406]Data Too Long, field len 12, data len 13
    19.    row_data: (77,'seventy-seven')
    21. *************************** 3. row ***************************
    22.     task_id: 1635888701843303564
    23. create_time: 2021-11-02 21:31:42.629929
    24.  table_name: `example`.`t`
    25.        path: example.t.1.sql
    26.      offset: 253
    27.       error: failed to cast value as tinyint(4) for column `a` (#1): [types:1690]constant 600 overflows tinyint
    28.    row_data: (600,'six hundred')

  7. Check whether the conflict_error_v1 table has caught the four rows that have unique/primary key conflicts:

    1. $ mysql -u root -h 127.0.0.1 -P 4000 -e 'select * from lightning_task_info.conflict_error_v1;' --binary-as-hex -E
    3. *************************** 1. row ***************************
    4.     task_id: 1635888701843303564
    5. create_time: 2021-11-02 21:31:42.669601
    6.  table_name: `example`.`t`
    7.  index_name: PRIMARY
    8.    key_data: 40
    9.    row_data: (40, "forty")
    10.     raw_key: 0x7480000000000000C15F728000000000000028
    11.   raw_value: 0x800001000000020500666F727479
    12.  raw_handle: 0x7480000000000000C15F728000000000000028
    13.     raw_row: 0x800001000000020500666F727479
    15. *************************** 2. row ***************************
    16.     task_id: 1635888701843303564
    17. create_time: 2021-11-02 21:31:42.674798
    18.  table_name: `example`.`t`
    19.  index_name: PRIMARY
    20.    key_data: 40
    21.    row_data: (40, "forty")
    22.     raw_key: 0x7480000000000000C15F728000000000000028
    23.   raw_value: 0x800001000000020600666F75727479
    24.  raw_handle: 0x7480000000000000C15F728000000000000028
    25.     raw_row: 0x800001000000020600666F75727479
    27. *************************** 3. row ***************************
    28.     task_id: 1635888701843303564
    29. create_time: 2021-11-02 21:31:42.680332
    30.  table_name: `example`.`t`
    31.  index_name: b
    32.    key_data: 54
    33.    row_data: (54, "fifty-four")
    34.     raw_key: 0x7480000000000000C15F6980000000000000010166696674792D666FFF7572000000000000F9
    35.   raw_value: 0x0000000000000036
    36.  raw_handle: 0x7480000000000000C15F728000000000000036
    37.     raw_row: 0x800001000000020A0066696674792D666F7572
    39. *************************** 4. row ***************************
    40.     task_id: 1635888701843303564
    41. create_time: 2021-11-02 21:31:42.681073
    42.  table_name: `example`.`t`
    43.  index_name: b
    44.    key_data: 42
    45.    row_data: (42, "fifty-four")
    46.     raw_key: 0x7480000000000000C15F6980000000000000010166696674792D666FFF7572000000000000F9
    47.   raw_value: 0x000000000000002A
    48.  raw_handle: 0x7480000000000000C15F72800000000000002A
    49.     raw_row: 0x800001000000020A0066696674792D666F7572