sync-diff-inspector: Failure on invisible column

[dveeden]

Bug Report

1. What did you do?

Upstream (MySQL 8.4.0):

CREATE TABLE t1 (id int primary key, c1 int invisible);
CREATE TABLE t2 (id int primary key, c1 int invisible);

Downstream (TiDB v8.0.0):

CREATE TABLE t1 (id int primary key, c1 int);
CREATE TABLE t2 (id int primary key);

2. What did you expect to see?

The structure check for these tables to report differences.

3. What did you see instead?

$ ./bin/sync_diff_inspector --config ./sync-diff.conf 
A total of 2 tables need to be compared


There is something error when compare structure of table, please check log info in output/sync_diff.log

[2024/05/24 12:05:52.770 +02:00] [INFO] [printer.go:46] ["Welcome to sync_diff_inspector"] ["Release Version"=v8.1.0-1-g17af326] ["Git Commit Hash"=17af3262fc169d6a4ae3f6ece6ade4e49b24a804] ["Git Branch"=master] ["UTC Build Time"="2024-05-24 09:30:01"] ["Go Version"=go1.22.3]
[2024/05/24 12:05:52.771 +02:00] [INFO] [main.go:101] [config="{\"check-thread-count\":4,\"split-thread-count\":5,\"export-fix-sql\":true,\"check-struct-only\":false,\"dm-addr\":\"\",\"dm-task\":\"\",\"data-sources\":{\"mysql1\":{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null},\"tidb0\":{\"host\":\"127.0.0.1\",\"port\":4000,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null}},\"routes\":null,\"table-configs\":{\"config1\":{\"target-tables\":[\"test.*\"],\"Schema\":\"\",\"Table\":\"\",\"ConfigIndex\":0,\"HasMatched\":false,\"IgnoreColumns\":null,\"Fields\":null,\"Range\":\"\",\"TargetTableInfo\":null,\"Collation\":\"\",\"chunk-size\":0}},\"task\":{\"source-instances\":[\"mysql1\"],\"source-routes\":null,\"target-instance\":\"tidb0\",\"target-check-tables\":[\"test.*\"],\"target-configs\":[\"config1\"],\"output-dir\":\"./output\",\"SourceInstances\":[{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null}],\"TargetInstance\":{\"host\":\"127.0.0.1\",\"port\":4000,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null},\"TargetTableConfigs\":[{\"target-tables\":[\"test.*\"],\"Schema\":\"\",\"Table\":\"\",\"ConfigIndex\":0,\"HasMatched\":false,\"IgnoreColumns\":null,\"Fields\":null,\"Range\":\"\",\"TargetTableInfo\":null,\"Collation\":\"\",\"chunk-size\":0}],\"TargetCheckTables\":[{}],\"FixDir\":\"output/fix-on-tidb0\",\"CheckpointDir\":\"output/checkpoint\",\"HashFile\":\"\"},\"ConfigFile\":\"./sync-diff.conf\",\"PrintVersion\":false}"]
[2024/05/24 12:05:52.777 +02:00] [INFO] [mysql_shard.go:372] ["will increase connection configurations for DB of instance"] ["connection limit"=10]
[2024/05/24 12:05:52.777 +02:00] [INFO] [source.go:412] ["table match check finished"]
[2024/05/24 12:05:52.778 +02:00] [INFO] [tidb.go:209] ["find router for tidb source"]
[2024/05/24 12:05:52.778 +02:00] [INFO] [source.go:412] ["table match check finished"]
[2024/05/24 12:05:52.778 +02:00] [INFO] [diff.go:363] ["The downstream is TiDB. pick it as work source first"]
[2024/05/24 12:05:52.784 +02:00] [INFO] [pd_service_discovery.go:1016] ["[pd] switch leader"] [new-leader=http://127.0.0.1:2379] [old-leader=]
[2024/05/24 12:05:52.784 +02:00] [INFO] [pd_service_discovery.go:498] ["[pd] init cluster id"] [cluster-id=7372478626427809710]
[2024/05/24 12:05:52.784 +02:00] [INFO] [client.go:606] ["[pd] changing service mode"] [old-mode=UNKNOWN_SVC_MODE] [new-mode=PD_SVC_MODE]
[2024/05/24 12:05:52.784 +02:00] [INFO] [tso_client.go:236] ["[tso] switch dc tso global allocator serving url"] [dc-location=global] [new-url=http://127.0.0.1:2379]
[2024/05/24 12:05:52.784 +02:00] [INFO] [tso_dispatcher.go:359] ["[tso] tso dispatcher created"] [dc-location=global]
[2024/05/24 12:05:52.784 +02:00] [INFO] [client.go:612] ["[pd] service mode changed"] [old-mode=UNKNOWN_SVC_MODE] [new-mode=PD_SVC_MODE]
[2024/05/24 12:05:52.785 +02:00] [INFO] [pd.go:212] ["tidb support auto gc safepoint"] [version=8.0.0]
[2024/05/24 12:05:52.785 +02:00] [INFO] [diff.go:349] ["start update service to keep GC stopped automatically"]
[2024/05/24 12:05:52.785 +02:00] [INFO] [diff.go:191] ["not found checkpoint file, start from beginning"]
[2024/05/24 12:05:52.785 +02:00] [INFO] [pd.go:227] ["generate dumpling gc safePoint id"] [id=Sync_diff_1716545152785467755]
[2024/05/24 12:05:52.788 +02:00] [FATAL] [main.go:129] ["failed to check structure difference"] [error="line 3 column 43 near \"INVISIBLE */,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\" "] [errorVerbose="line 3 column 43 near \"INVISIBLE */,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\" \ngithub.com/pingcap/errors.AddStack\n\t/home/dvaneeden/go/pkg/mod/github.com/pingcap/[email protected]/errors.go:178\ngithub.com/pingcap/errors.Trace\n\t/home/dvaneeden/go/pkg/mod/github.com/pingcap/[email protected]/juju_adaptor.go:15\ngithub.com/pingcap/tidb/pkg/parser.(*Parser).ParseSQL\n\t/home/dvaneeden/go/pkg/mod/github.com/pingcap/tidb/pkg/[email protected]/yy_parser.go:170\ngithub.com/pingcap/tidb/pkg/parser.(*Parser).ParseOneStmt\n\t/home/dvaneeden/go/pkg/mod/github.com/pingcap/tidb/pkg/[email protected]/yy_parser.go:191\ngithub.com/pingcap/tidb-tools/pkg/dbutil.getTableInfoBySQL\n\t/home/dvaneeden/dev/pingcap/tidb-tools/pkg/dbutil/table.go:150\ngithub.com/pingcap/tidb-tools/pkg/dbutil.GetTableInfoBySQL\n\t/home/dvaneeden/dev/pingcap/tidb-tools/pkg/dbutil/table.go:146\ngithub.com/pingcap/tidb-tools/pkg/dbutil.GetTableInfo\n\t/home/dvaneeden/dev/pingcap/tidb-tools/pkg/dbutil/table.go:136\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.(*MySQLSources).GetSourceStructInfo\n\t/home/dvaneeden/dev/pingcap/tidb-tools/sync_diff_inspector/source/mysql_shard.go:252\nmain.(*Diff).compareStruct\n\t/home/dvaneeden/dev/pingcap/tidb-tools/sync_diff_inspector/diff.go:315\nmain.(*Diff).StructEqual\n\t/home/dvaneeden/dev/pingcap/tidb-tools/sync_diff_inspector/diff.go:303\nmain.checkSyncState\n\t/home/dvaneeden/dev/pingcap/tidb-tools/sync_diff_inspector/main.go:126\nmain.main\n\t/home/dvaneeden/dev/pingcap/tidb-tools/sync_diff_inspector/main.go:104\nruntime.main\n\t/usr/lib/golang/src/runtime/proc.go:271\nruntime.goexit\n\t/usr/lib/golang/src/runtime/asm_amd64.s:1695"] [stack="main.checkSyncState\n\t/home/dvaneeden/dev/pingcap/tidb-tools/sync_diff_inspector/main.go:129\nmain.main\n\t/home/dvaneeden/dev/pingcap/tidb-tools/sync_diff_inspector/main.go:104\nruntime.main\n\t/usr/lib/golang/src/runtime/proc.go:271"]

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

v8.0.0

5. which tool are you using?

sync-diff-inspector

6. what versionof tool are you using (pump -V or tidb-lightning -V or syncer -V)?

$ ./bin/sync_diff_inspector -V
App Name: sync_diff_inspector
Release Version: v8.1.0-1-g17af326
Git Commit Hash: 17af3262fc169d6a4ae3f6ece6ade4e49b24a804
Git Branch: master
UTC Build Time: 2024-05-24 09:30:01
Go Version: go1.22.3

[dveeden]

Note that this was added to MySQL in 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

As documented on pingcap/tidb#7968 this isn't supported on TiDB yet.