Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: mysql移除自增属性dm 同步到tidb时报错
[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
v4.0.8
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
MySQL data is synchronized to TiDB in real-time through DM.
When executing DDL changes on the upstream MySQL, after removing the auto-increment attribute of the primary key ID column, the synchronization task reports an error.
When DM executes synchronization, can the tidb_allow_remove_auto_inc variable be enabled and then executed?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]
When executing synchronization with DM, can you enable the tidb_allow_remove_auto_inc variable and then proceed? It shouldn’t be possible, right? After enabling it downstream and removing the auto_increment from the corresponding table, DM should skip this DDL operation and continue synchronization…
The current assessment of the skipping method indicates that it is risky. It is still necessary to load the latest table structure into DM. If this process fails, full synchronization will be the only option.
Set the tidb_allow_remove_auto_inc variable to on to allow TiDB to remove the auto-increment primary key, and then adjust it back later.
This is a session-level variable. Manually enabling it is fine, but the MySQL data is transferred to TiDB through DM, and DM does not enable this variable during execution.
I don’t quite understand. Are you currently migrating the entire database, or are you synchronizing two sets of databases? Isn’t this error just because you performed an alter command to modify the auto_increment attribute on the upstream table? My suggestion is to enable the tidb_allow_remove_auto_inc variable downstream, manually execute the alter operation to remove the auto_increment from the corresponding table, and then skip this operation in DM. The subsequent data will continue to synchronize. Where is the risk?
I think it’s fine, you can test it.
You can manually set the session-level variable tidb_allow_remove_auto_inc to on, then manually execute the SQL statement to alter and remove the primary key in that session. After that, let the DM synchronization task skip the error DDL statement. This method works fine. I handle many DDL synchronization issues in production this way.
Do the experts all recommend enabling the tidb_allow_remove_auto_inc variable parameter configuration at the session level?
Starting from TiDB versions 2.1.18 and 3.0.4, TiDB uses the session variable @@tidb_allow_remove_auto_inc to control whether it is allowed to remove the AUTO_INCREMENT attribute from a column using ALTER TABLE MODIFY or ALTER TABLE CHANGE. By default, it is not allowed to remove it.
If you have business requirements, you can try setting it.
You can add session variables in the task. For details, refer to the official documentation: DM 任务完整配置文件介绍 | PingCAP 文档中心
This is good. I didn’t know before that DM can set session-level variables. @qiuxb, you can try this method.
TiDB has a variable to control this feature, and by default, it is not allowed: tidb_allow_remove_auto_inc.
If the downstream table data is synchronized from upstream, skipping it won’t pose any risk.
|
username: qiuxb
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: