DM is continuously syncing, why is there no data in TiDB?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: DM一直在同步,为什么tidb里没有数据?

| username: iceman

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v6.1.0
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Problem Phenomenon and Impact]
DM synchronization status shows it is syncing, but no data tables are created in TiDB
[Resource Configuration]
name: “test”

Task mode, can be set to

full: only perform full data migration

incremental: real-time binlog synchronization

all: full + binlog migration

task-mode: “all”

Downstream TiDB configuration information.

target-database:
host: “192.168.106.92” # Example: 172.16.10.83
port: 4000
user: “root”
password: “*********” # Plaintext passwords are supported but not recommended. It is recommended to use dmctl encrypt to encrypt plaintext passwords before use.

Configuration of all upstream MySQL instances required for the current data migration task.

mysql-instances:

Upstream instance or replication group ID.

source-id: “mysql-01”

Configuration item name of the whitelist or blacklist of databases or tables to be migrated, used to reference the global whitelist and blacklist configuration. The global configuration is seen below in the block-allow-list configuration.

block-allow-list: “listA”

Global configuration of whitelist and blacklist, referenced by instances through configuration item names.

block-allow-list:
listA: # Name
do-tables: # Whitelist of upstream tables to be migrated.
- db-name: “nadia." # Name of the database containing the tables to be migrated.
tbl-name: "
” # Name of the tables to be migrated.

[Attachment: Screenshot/Log/Monitoring]

| username: xfworld | Original post link

Check the DM logs.

| username: h5n1 | Original post link

tbl-name: " ” # The name of the table that needs to be migrated. Change this to *

| username: iceman | Original post link

However, there was an error in copying. The actual configuration is:
block-allow-list:
listA: # Name
do-tables: # Whitelist of upstream tables to be migrated.
- db-name: “nadia” # Name of the database containing the tables to be migrated.
tbl-name: “*”

| username: Billmay表妹 | Original post link

Has the issue been resolved?

| username: iceman | Original post link

root@db93:/home/tidb/dm/deploy/dm-worker-8262/log# tail dm-worker_stderr.log -n 20
[mysql] 2022/12/23 07:25:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 07:35:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 07:45:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 07:55:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:05:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:15:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:25:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:35:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:45:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 08:55:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:05:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:15:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:25:12 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:37:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:47:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 09:57:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:07:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:37:18 packets.go:123: closing bad idle connection: EOF

| username: iceman | Original post link

I looked at the directory in dumped_data.nadia on the worker and saw that not all the tables are there. Does this mean the dump is not yet complete?

| username: iceman | Original post link

Not yet.

| username: iceman | Original post link

It keeps disconnecting, what could be the reason? The source database has already set a timeout of 86400.

“subTaskStatus”: [
{
“name”: “nadia”,
“stage”: “Paused”,
“unit”: “Dump”,
“result”: {
“isCanceled”: false,
“errors”: [
{
“ErrCode”: 32001,
“ErrClass”: “dump-unit”,
“ErrScope”: “internal”,
“ErrLevel”: “high”,
“Message”: "mydumper/dumpling runs with error, with output (may be empty): ",
“RawCause”: “sql: SHOW COLUMNS FROM nadia.goods_store: driver: bad connection”,
“Workaround”: “”
}
],
“detail”: null
},
“unresolvedDDLLockID”: “”,
“dump”: {
“totalTables”: “103”,
“completedTables”: 20,
“finishedBytes”: 19923930333,
“finishedRows”: 106142560,
“estimateTotalRows”: 227656254,
“bps”: “0”,
“progress”: “”
},
“validation”: null
}
]

| username: buchuitoudegou | Original post link

Are there other connections to the downstream TiDB? Check show processlist and max_connections.

| username: xfworld | Original post link

The connection is down. If you want to solve the problem, you need to clearly describe all the environments.

| username: iceman | Original post link

The image you provided is not visible. Please provide the text you need translated.

| username: iceman | Original post link

The upstream is MySQL 5.7 on Alibaba Cloud public network. The downstream is TiDB 6.1.3 in a private network environment. All network ports have been opened and can be connected.

| username: iceman | Original post link

The worker logs are all like this:
[mysql] 2022/12/23 10:07:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:37:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:47:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 10:57:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:07:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:37:18 packets.go:123: closing bad idle connection: EOF

| username: xfworld | Original post link

It is estimated that packet capture is needed to find out the reason.

Refer to:

Reference 1: Go MySQL 报错 closing bad idle connection: EOF

Reference 2: MySQL连接池与超时设定_mysql多久连接一次-CSDN博客


It is still estimated to be caused by environmental issues…

| username: 会飞的土拨鼠 | Original post link

Does the DM synchronization status show as “synchronizing”? Are there any tables or databases that have been successfully synchronized?

| username: iceman | Original post link

No, still dumping. The absence of tables is likely because it’s still in the dumping process. The current issue is that during the dump process, it keeps reporting:

“Message”: "mydumper/dumpling runs with error, with output (may be empty): ",
“RawCause”: “sql: SHOW COLUMNS FROM nadia.goods_store: driver: bad connection”,

Checked the worker logs:
[mysql] 2022/12/23 11:17:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:27:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:37:18 packets.go:123: closing bad idle connection: EOF
[mysql] 2022/12/23 11:47:18 packets.go:123: closing bad idle connection: EOF

| username: 会飞的土拨鼠 | Original post link

This is a database connection issue, the connection failed. You need to check if the “synchronized configuration file” is correct. Verify if the migration database and the target database can connect normally, and then execute the command. If TiDB is a test database without important data, it is recommended to restart the TiDB cluster during idle time.

| username: iceman | Original post link

Looking at this log, it disconnects every 10 minutes.

| username: okenJiang | Original post link

It seems to be this issue Dumpling may get blocked when taskChan is full and cause connection timeout · Issue #36549 · pingcap/tidb · GitHub. Why don’t you try the new version of DM? Also, you should post the normal logs of the worker, not just dm-worker_stderr.log.