TiDB Auto-Increment ID Conflict

Translated
1

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

Original topic: tidb 自增id 冲突

| username: 是我的海

[TiDB Usage Environment] Production Environment
[TiDB Version] tidb-vv.5.4.3
[Reproduction Path] Normal business insert
[Encountered Problem]
Business insert data reports primary key conflict, restarting tidb-server alleviates the issue, but after a while, the primary key conflict reappears.

Table Structure Information:
Due to the need to synchronize data to downstream Hive via CDC, using auto_random can easily cause field overflow issues in Hive, so auto-increment ID is used.

MySQL [dcflow]> show create table tblSeaSearch\G
*************************** 1. row ***************************
       Table: tblSeaSearch
Create Table: CREATE TABLE `tblSeaSearch` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `eid` varchar(32) DEFAULT NULL,
  `flowid` bigint(20) DEFAULT NULL,
  `dataid` bigint(20) DEFAULT NULL,
  `content` mediumtext DEFAULT NULL,
  `nshead` varchar(128) DEFAULT NULL,
  `alvl` int(11) DEFAULT '0',
  `alen` int(11) DEFAULT '0',
  `deleted` int(10) NOT NULL DEFAULT '0',
  `ctime` int(10) NOT NULL DEFAULT '0',
  `utime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `ctime` (`ctime`),
  KEY `eid` (`eid`),
  KEY `utime` (`utime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=41110159

TiDB Log Information

[2022/11/15 14:48:07.749 +08:00] [INFO] [conn.go:1121] ["command dispatched failed"] [conn=1245901] [connInfo="id:1245901, addr:10.32.11.109:35788 status:10, collation:utf8mb4_general_ci, user:dcflow_app"] [command=Execute] [status="inTxn:0, autocommit:1"] [sql="INSERT INTO `tblSeaSearch` (`flowid`,`dataid`) VALUES (?,?) [arguments: (0, 30001)]"] [txn_mode=PESSIMISTIC] [err="INSERT INTO `tblSeaSearch` (`flowid`,`dataid`) VALUES (?,?) [arguments: (0, 30001)]: [kv:1062]Duplicate entry '40376465' for key 'PRIMARY'"]

Monitoring Information

Image
Image1380×794 134 KB

2
| username: 爱白话的晓辉 | Original post link

There may be issues with TiDB auto-increment primary keys under multiple servers. Since you are already using auto-increment primary keys, it is recommended to change the table to AUTO_RANDOM, which can also alleviate the problem of hotspot writing.

3
| username: 是我的海 | Original post link

Many of our services use auto-increment IDs because auto_random would cause issues with Hive storage. :grinning: We haven’t encountered this problem with other services. Are you suggesting that there might be a bug with the auto-increment ID cache or something else?

4
| username: 是我的海 | Original post link

Problem located: Business code bug
By enabling TiDB general_log and checking the operation logs of the related table, it was found that during the update, the ID was updated to another ID, causing a primary key conflict in subsequent insert SQL statements.
Log in to the specified node’s TiDB server and execute set tidb_general_log = on;
Search for the term GENERAL_LOG in tidb.log and found the following information:

image
image1380×146 141 KB

5
| username: alfred | Original post link

The general_log can be :+1:

6
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.