Does tidb-lightning support importing data from one table into another table multiple times to achieve data duplication?

Translated
1

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

Original topic: tidb-lightning 是否支持将一个表中数据多次导入到另一个表中，实现数据的多次复制

| username: xiaoxiaozuofang

【TiDB Usage Environment】Production Environment / Testing / PoC
【TiDB Version】
【Reproduction Path】What operations were performed that led to the issue
【Encountered Issue: Does tidb-lightning support importing data from one table into another table multiple times to achieve multiple data copies?】
【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】

2
| username: WalterWj | Original post link

I don’t understand this requirement. :thinking:

3
| username: xiaoxiaozuofang | Original post link

Actually, I just want to copy the data from one table multiple times and import it into another table.

4
| username: WalterWj | Original post link

You can import the logic and just change the table name.

5
| username: 我是咖啡哥 | Original post link

He is probably trying to generate data, similar to executing insert select multiple times.

6
| username: WalterWj | Original post link

The same dumpling export can specify --sql, so the desired content can be exported.

7
| username: xiaoxiaozuofang | Original post link

Something like this, right? The original table has more than 3 million records, and I want to copy it three times and import it into a new table. Can this be achieved through dumping and importing with tidb-lightning?

8
| username: 裤衩儿飞上天 | Original post link

Export once and then insert select. 3 million is very fast, it might have been done by the time you opened the thread :stuck_out_tongue_closed_eyes:

9
| username: 我是咖啡哥 | Original post link

The table has no primary key, no unique constraints, just import it several times. The schema uses TiDB.

10
| username: xingzhenxiang | Original post link

Importing logic is much more convenient. When using mysqldump, add --skip-extended-insert and --complete-insert.

11
| username: xiaoxiaozuofang | Original post link

Is this how you set it in tidb-lightning.toml: backend = “tidb” on-duplicate = “replace”?

12
| username: xiaoxiaozuofang | Original post link

tidb-lightning repeatedly imported several times, but it is still the data from the first import.

13
| username: dba-kit | Original post link

Change on-duplicate = "replace" to on-duplicate = "ignore" and try again. This method should actually be faster.

14
| username: xiaoxiaozuofang | Original post link

I think it’s about copying a set of data, whether it’s importing multiple times or the data imported the first time.

15
| username: xiaoxiaozuofang | Original post link

How to solve the “Transaction is too large, size: 104857616” error when using insert into select?

16
| username: 胡杨树旁 | Original post link

Large transactions are limited, try inserting in batches.

17
| username: 胡杨树旁 | Original post link

With this setting, if the same data is imported, it should be overwritten; if it is different data, it should be inserted.

18
| username: xiaoxiaozuofang | Original post link

How is the same data inserted?

19
| username: xiaoxiaozuofang | Original post link

The inserted data is the same. Inserting 100 rows at a time by ID will report “Transaction is too large, size: 104857616.” I don’t know if this is a default TiDB setting limit. If so, can it be modified by changing the default configuration?

20
| username: 胡杨树旁 | Original post link

The official documentation says it can be adjusted.