converting driver.Value type []uint8 to a int64: value out of range

[lionelee]

What did you do?

start dm task

What did you expect to see?

task runs well

What did you see instead?

[lightning.go:465]["failed to runlightning"]
[code=34019:class=load-unit:scope=internal:level=high],
RawCause: fetch table columns failed: sql: Scan error on column index 3,
name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range
github.com/pingcap/tiflow/dm/pkg/terror.(*Error).Delegate
    github.com/pingcap/tiflow/dm/pkg/terror/terror.go:337
github.com/pingcap/tiflow/dm/loader.convertLightningError
    github.com/pingcap/tiflow/dm/loader/lightning.go:306
github.com/pingcap/tiflow/dm/loader.(*LightningLoader).runLightning
    github.com/pingcap/tiflow/dm/loader/lightning.go:287
github.com/pingcap/tiflow/dm/loader.(*LightningLoader).restore
    github.com/pingcap/tiflow/dm/loader/lightning.go:456
github.com/pingcap/tiflow/dm/loader.(*LightningLoader).Process
    github.com/pingcap/tiflow/dm/loader/lightning.go:524
runtime.goexit
    runtime/asm_amd64.s:1598

Versions of the cluster

DM version (run dmctl -V or dm-worker -V or dm-master -V):

v7.1.0

Upstream MySQL/MariaDB server version:

(paste upstream MySQL/MariaDB server version here)

Downstream TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

(paste TiDB cluster version here)

How did you deploy DM: tiup or manually?

(leave TiUP or manually here)

Other interesting information (system version, hardware config, etc):

>
>

current status of DM cluster (execute query-status <task-name> in dmctl)

(paste current status of DM cluster here)

[lionelee]

It seems already been solved in version 1.6 of the MySQL driver

Support returning uint64 from Valuer in ConvertValue (go-sql-driver/mysql#1143)

[lance6716]

Can you provide the full log? Seems it's a bug of FetchTableAutoIDInfos in lightning library.

[lance6716]

And how to reproduce this problem? Seems 10942694589135710587 is illegal for NEXT_GLOBAL_ROW_ID because it should be int64 indeed

[lionelee]

Can you provide the full log? Seems it's a bug of FetchTableAutoIDInfos in lightning library.

[INFO] [lightning.go:491] ["lightning load start"] [task=task01] [unit=lightning-load]
[INFO] [util.go:190] ["put load worker in etcd"] [task=task01] [source=mysql01] [worker=serverless-svc-dm-worker-0]
[INFO] [checkpoint.go:123] ["initial checkpoint record"] [task=task01] [unit=lightning-load] [component="lightning checkpoint database list"] [task=task01] [source=mysql01]
[INFO] [checkpoint.go:143] ["update lightning loader status"] [task=task01] [unit=lightning-load] [component="lightning checkpoint database list"] [task=task01] [source=mysql01] [status=running]
[INFO] [info.go:49] ["Welcome to TiDB-Lightning"] [release-version=v7.1.1] [git-hash=None] [git-branch=None] [go-version=go1.20.5] [utc-build-time=None] [race-enabled=false]
[INFO] [lightning.go:425] [cfg] [task=task01] [unit=lightning-load] [cfg="{\"id\":1689663743031778450,\"lightning\":{\"table-concurrency\":16,\"index-concurrency\":16,\"region-concurrency\":16,\"io-concurrency\":5,\"check-requirements\":true,\"meta-schema-name\":\"\",\"max-error\":{\"type\":0,\"conflict\":9223372036854775807},\"task-info-schema-name\":\"lightning_task_info\"},\"tidb\":{\"status-port\":10080,\"pd-addr\":\"\",\"sql-mode\":\"NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES,IGNORE_SPACE\",\"tls\":\"false\",\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"max-allowed-packet\":67108864,\"distsql-scan-concurrency\":15,\"build-stats-concurrency\":20,\"index-serial-scan-concurrency\":20,\"checksum-table-concurrency\":2,\"vars\":{\"foreign_key_checks\":\"0\",\"tidb_txn_mode\":\"optimistic\",\"time_zone\":\"+00:00\"}},\"checkpoint\":{\"schema\":\"tidb_lightning_checkpoint\",\"driver\":\"file\",\"enable\":true,\"keep-after-success\":\"remove\"},\"mydumper\":{\"read-block-size\":65536,\"batch-size\":0,\"batch-import-ratio\":0,\"source-id\":\"mysql01\",\"data-source-dir\":\"file:///dumped_data.task01\",\"character-set\":\"auto\",\"csv\":{\"separator\":\",\",\"delimiter\":\"\\\"\",\"terminator\":\"\",\"null\":[\"\\\\N\"],\"header\":true,\"header-schema-match\":true,\"trim-last-separator\":false,\"not-null\":false,\"backslash-escape\":true,\"escaped-by\":\"\\\\\"},\"max-region-size\":268435456,\"filter\":[\"*.*\",\"!mysql.*\",\"!sys.*\",\"!INFORMATION_SCHEMA.*\",\"!PERFORMANCE_SCHEMA.*\",\"!METRICS_SCHEMA.*\",\"!INSPECTION_SCHEMA.*\"],\"files\":null,\"no-schema\":false,\"case-sensitive\":false,\"strict-format\":false,\"default-file-rules\":true,\"ignore-data-columns\":null,\"data-character-set\":\"binary\",\"data-invalid-char-replace\":\"�\"},\"tikv-importer\":{\"addr\":\"\",\"backend\":\"tidb\",\"on-duplicate\":\"replace\",\"max-kv-pairs\":4096,\"send-kv-pairs\":32768,\"compress-kv-pairs\":\"\",\"region-split-size\":0,\"region-split-keys\":0,\"sorted-kv-dir\":\"\",\"disk-quota\":9223372036854775807,\"range-concurrency\":0,\"duplicate-resolution\":\"none\",\"incremental-import\":true,\"keyspace-name\":\"\",\"add-index-by-sql\":false,\"engine-mem-cache-size\":536870912,\"local-writer-mem-cache-size\":134217728,\"store-write-bwlimit\":0},\"post-restore\":{\"checksum\":\"off\",\"analyze\":\"off\",\"level-1-compact\":false,\"post-process-at-last\":true,\"compact\":false},\"cron\":{\"switch-mode\":\"5m0s\",\"log-progress\":\"5m0s\",\"check-disk-quota\":\"1m0s\"},\"routes\":[],\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"black-white-list\":{\"do-tables\":null,\"do-dbs\":null,\"ignore-tables\":null,\"ignore-dbs\":null}}"]
[INFO] [lightning.go:425] [cfg] [task=task01] [unit=lightning-load] [cfg="{\"id\":1689663743031778450,\"lightning\":{\"table-concurrency\":16,\"index-concurrency\":16,\"region-concurrency\":16,\"io-concurrency\":5,\"check-requirements\":true,\"meta-schema-name\":\"\",\"max-error\":{\"type\":0,\"conflict\":9223372036854775807},\"task-info-schema-name\":\"lightning_task_info\"},\"tidb\":{\"status-port\":10080,\"pd-addr\":\"\",\"sql-mode\":\"NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES,IGNORE_SPACE\",\"tls\":\"false\",\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"max-allowed-packet\":67108864,\"distsql-scan-concurrency\":15,\"build-stats-concurrency\":20,\"index-serial-scan-concurrency\":20,\"checksum-table-concurrency\":2,\"vars\":{\"foreign_key_checks\":\"0\",\"tidb_txn_mode\":\"optimistic\",\"time_zone\":\"+00:00\"}},\"checkpoint\":{\"schema\":\"tidb_lightning_checkpoint\",\"driver\":\"file\",\"enable\":true,\"keep-after-success\":\"remove\"},\"mydumper\":{\"read-block-size\":65536,\"batch-size\":0,\"batch-import-ratio\":0,\"source-id\":\"mysql01\",\"data-source-dir\":\"file:///dumped_data.task01\",\"character-set\":\"auto\",\"csv\":{\"separator\":\",\",\"delimiter\":\"\\\"\",\"terminator\":\"\",\"null\":[\"\\\\N\"],\"header\":true,\"header-schema-match\":true,\"trim-last-separator\":false,\"not-null\":false,\"backslash-escape\":true,\"escaped-by\":\"\\\\\"},\"max-region-size\":268435456,\"filter\":[\"*.*\",\"!mysql.*\",\"!sys.*\",\"!INFORMATION_SCHEMA.*\",\"!PERFORMANCE_SCHEMA.*\",\"!METRICS_SCHEMA.*\",\"!INSPECTION_SCHEMA.*\"],\"files\":null,\"no-schema\":false,\"case-sensitive\":false,\"strict-format\":false,\"default-file-rules\":true,\"ignore-data-columns\":null,\"data-character-set\":\"binary\",\"data-invalid-char-replace\":\"�\"},\"tikv-importer\":{\"addr\":\"\",\"backend\":\"tidb\",\"on-duplicate\":\"replace\",\"max-kv-pairs\":4096,\"send-kv-pairs\":32768,\"compress-kv-pairs\":\"\",\"region-split-size\":0,\"region-split-keys\":0,\"sorted-kv-dir\":\"\",\"disk-quota\":9223372036854775807,\"range-concurrency\":0,\"duplicate-resolution\":\"none\",\"incremental-import\":true,\"keyspace-name\":\"\",\"add-index-by-sql\":false,\"engine-mem-cache-size\":536870912,\"local-writer-mem-cache-size\":134217728,\"store-write-bwlimit\":0},\"post-restore\":{\"checksum\":\"off\",\"analyze\":\"off\",\"level-1-compact\":false,\"post-process-at-last\":true,\"compact\":false},\"cron\":{\"switch-mode\":\"5m0s\",\"log-progress\":\"5m0s\",\"check-disk-quota\":\"1m0s\"},\"routes\":[],\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"black-white-list\":{\"do-tables\":null,\"do-dbs\":null,\"ignore-tables\":null,\"ignore-dbs\":null}}"]
[INFO] [lightning.go:519] ["load data source start"] [task=task01] [unit=lightning-load]
[INFO] [loader.go:465] ["[loader] file is filtered by file router"] [task=task01] [unit=lightning-load] [path=metadata]
[INFO] [lightning.go:522] ["load data source completed"] [task=task01] [unit=lightning-load] [takeTime=809.589µs] []
[WARN] [tidb.go:92] ["set session variable failed, will skip this query"] [task=task01] [unit=lightning-load] [query="SET SESSION tidb_opt_write_row_id = '1';"] [error="Error 1227 (42000): Access denied; you need (at least one of) the RESTRICTED_VARIABLES_ADMIN privilege(s) for this operation"]
[INFO] [checkpoints.go:1113] ["open checkpoint file failed, going to create a new one"] [task=task01] [unit=lightning-load] [path=./dumped_data.task01/tidb_lightning_checkpoint.pb] []
[INFO] [import.go:486] ["the whole procedure start"] [task=task01] [unit=lightning-load]
[INFO] [import.go:792] ["restore all schema start"] [task=task01] [unit=lightning-load]
[WARN] [util.go:196] ["run create schema job failed with no retry"] [task=task01] [unit=lightning-load] [query="CREATE DATABASE IF NOT EXISTS `nightly` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ENCRYPTION = 'N';"] [args=null] [retryCnt=0] [error="Error 1273 (HY000): Unsupported collation when new collation is enabled: 'utf8mb4_0900_ai_ci'"]
[INFO] [version.go:429] ["detect server version"] [type=TiDB] [version=6.6.0-serverless]
[WARN] [util.go:196] ["fetch table columns failed with no retry"] [task=task01] [unit=lightning-load] [retryCnt=0] [error="sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range"]
[INFO] [import.go:811] ["restore all schema completed"] [task=task01] [unit=lightning-load] [takeTime=1.170356567s] []
[INFO] [version.go:429] ["detect server version"] [type=TiDB] [version=6.6.0-serverless]
[WARN] [util.go:196] ["fetch table columns failed with no retry"] [task=task01] [unit=lightning-load] [retryCnt=0] [error="sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range"]
[ERROR] [import.go:504] ["run failed"] [task=task01] [unit=lightning-load] [step=1] [error="fetch table columns failed: sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range"]
[ERROR] [import.go:514] ["the whole procedure failed"] [task=task01] [unit=lightning-load] [takeTime=1.706797063s] [error="fetch table columns failed: sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range"]
[ERROR] [lightning.go:465] ["failed to runlightning"] [task=task01] [unit=lightning-load] [error="[code=34019:class=load-unit:scope=internal:level=high], RawCause: fetch table columns failed: sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range"] [errorVerbose="[code=34019:class=load-unit:scope=internal:level=high], RawCause: fetch table columns failed: sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range\ngithub.com/pingcap/tiflow/dm/pkg/terror.(*Error).Delegate\n\tgithub.com/pingcap/tiflow/dm/pkg/terror/terror.go:337\ngithub.com/pingcap/tiflow/dm/loader.convertLightningError\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:306\ngithub.com/pingcap/tiflow/dm/loader.(*LightningLoader).runLightning\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:287\ngithub.com/pingcap/tiflow/dm/loader.(*LightningLoader).restore\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:456\ngithub.com/pingcap/tiflow/dm/loader.(*LightningLoader).Process\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:524\nruntime.goexit\n\truntime/asm_amd64.s:1598"]
[ERROR] [lightning.go:525] ["process error"] [task=task01] [unit=lightning-load] [error="[code=34019:class=load-unit:scope=internal:level=high], RawCause: fetch table columns failed: sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range"] [errorVerbose="[code=34019:class=load-unit:scope=internal:level=high], RawCause: fetch table columns failed: sql: Scan error on column index 3, name \"NEXT_GLOBAL_ROW_ID\": converting driver.Value type []uint8 (\"10942694589135710587\") to a int64: value out of range\ngithub.com/pingcap/tiflow/dm/pkg/terror.(*Error).Delegate\n\tgithub.com/pingcap/tiflow/dm/pkg/terror/terror.go:337\ngithub.com/pingcap/tiflow/dm/loader.convertLightningError\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:306\ngithub.com/pingcap/tiflow/dm/loader.(*LightningLoader).runLightning\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:287\ngithub.com/pingcap/tiflow/dm/loader.(*LightningLoader).restore\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:456\ngithub.com/pingcap/tiflow/dm/loader.(*LightningLoader).Process\n\tgithub.com/pingcap/tiflow/dm/loader/lightning.go:524\nruntime.goexit\n\truntime/asm_amd64.s:1598"]
[INFO] [lightning.go:612] ["progress status of lightning"] [task=task01] [unit=lightning-load] [finished_bytes=0] [total_bytes=0] [progress="0.00 %"] ["current speed (bytes / seconds)"=0]
[INFO] [lightning.go:537] ["lightning load end"] [task=task01] [unit=lightning-load] [IsCanceled=false] [finished_bytes=0] [total_bytes=0] [progress="0.00 %"]
[INFO] [lightning.go:612] ["progress status of lightning"] [task=task01] [unit=lightning-load] [finished_bytes=0] [total_bytes=0] [progress="0.00 %"] ["current speed (bytes / seconds)"=0]
[INFO] [subtask.go:333] ["unit process returned"] [subtask=task01] [unit=Load] [stage=Paused] [status="{\"progress\":\"0.00 %\",\"metaBinlog\":\"(mysql-bin-changelog.012917, 1785)\"}"]
[ERROR] [subtask.go:354] ["unit process error"] [subtask=task01] [unit=Load] ["error information"="ErrCode:34019 ErrClass:\"load-unit\" ErrScope:\"internal\" ErrLevel:\"high\" RawCause:\"fetch table columns failed: sql: Scan error on column index 3, name \\\"NEXT_GLOBAL_ROW_ID\\\": converting driver.Value type []uint8 (\\\"10942694589135710587\\\") to a int64: value out of range\" "]

[lionelee]

And how to reproduce this problem? Seems 10942694589135710587 is illegal for NEXT_GLOBAL_ROW_ID because it should be int64 indeed

steps to reproduce:

1. create table in mysql:

CREATE TABLE IF NOT EXISTS `test`.`dummy` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

2. insert some value:

INSERT INTO `test`.`dummy` values (10942694589135710585, "test0");
INSERT INTO `test`.`dummy` values (10942694589135710586, "test1");

3. create source in dm

source-id: "mysql01"
from:
  host: "localhost"
  port: 3306
  user: "root"
  password: "xxxxxx"

4. start task in dm

name: task01
task-mode: all
ignore-checking-items: ["table_schema"]
mysql-instances:
  - source-id: "mysql01"
    block-allow-list: "rule-1"
target-database:
  host: "xxxxxx"
  port: 4000
  user: "xxxxxx"
  password: "xxxxxx"
  security:
    ssl-ca: "/etc/ssl/cert.pem"
block-allow-list:
  rule-1:
    do-dbs: ["test"]

5. then stop the task and drop db dm_meta
6. restart the task and get the error

[lance6716]

Thanks, the problem is INSERT INTO `test`.`dummy` values (10942694589135710585, "test0"); I'll open an issue for lightning.