自增字段写入数据表现不一致的情况

ShawnYan · 2022 年8 月 23 日 02:10

【 TiDB 使用环境】线上、测试、调研
【 TiDB 版本】tidb v6.1
【遇到的问题】
自增字段设为主键和加索引的表现不一致，与mysql也不一致
造成这种结果差异的原因是什么？有参数控制还是bug？

【复现路径】做过哪些操作出现的问题

drop table t6,t7;

create table t6 (
id int(1) not null auto_increment,
name varchar(64),
primary key(id)
) engine=innodb;

create table t7 (
id int(1) not null auto_increment,
name varchar(64),
key idx_id(`id`)
) engine=innodb;

insert into t6 values(1,'chen'),(2147483646,'wu');
insert into t7 values(1,'chen'),(2147483646,'wu');

insert into t6 (`name`) values('a47');
insert into t7 (`name`) values('a47');

【问题现象及影响】

tidb> insert into t6 (`name`) values('a47');
Query OK, 1 row affected (0.01 sec)

tidb> insert into t7 (`name`) values('a47');
ERROR 1690 (22003): constant 2147483649 overflows int

【附件】

请提供各个组件的 version 信息，如 cdc/tikv，可通过执行 cdc version/tikv-server --version 获取。

wakaka · 2022 年8 月 23 日 02:36

这不是超过了int的最大值么？有啥问题么

wakaka · 2022 年8 月 23 日 02:37

自增值是在不同tidb节点分片的，t6表分到1个节点，那个节点的最大值没超过int范围。t7超过了

Z六月星星 · 2022 年8 月 23 日 03:23

TiDB 能保证自增值的单调性，但并不能保证其连续性

Z六月星星 · 2022 年8 月 23 日 03:23

https://docs.pingcap.com/zh/tidb/dev/auto-increment#auto_id_cache

Tank001 · 2022 年8 月 23 日 03:25

试一下 mysql没啥问题
但是tidb确实有问题 , 平时没注意到

OnTheRoad · 2022 年8 月 23 日 03:49

简单的画了下TIDB中AUTO_INCREMENT的实现原理，大概是这个意思吧。

ShawnYan · 2022 年8 月 23 日 04:16

这个case只用到一个 tidb server 哈，sql是在一个cli里顺序执行的。

ShawnYan · 2022 年8 月 23 日 04:17

定义是，如果在另一台服务器上执行插入操作，那么 AUTO_INCREMENT 值的顺序可能会剧烈跳跃，这是由于每台服务器都有各自缓存的 AUTO_INCREMENT 自增值。

但是这个case是在一个 tidb server执行的。

OnTheRoad · 2022 年8 月 23 日 04:59

官方文档并没有明确是在插入时，才申请AUTO_ID_CACHE吧？

ShawnYan · 2022 年8 月 23 日 05:17

这是为了多 tidb server 写入不冲突用的，本例应该不涉及，而且自增步长也是1.

tidb> show variables like 'auto_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| autocommit               | ON    |
| automatic_sp_privileges  | 1     |
+--------------------------+-------+
4 rows in set (0.00 sec)

db_user · 2022 年8 月 23 日 06:03

猜测可能是因为没有主键造成的关系，感觉像是有主键的话是一组key,value键值对，步长为一，没有主键的话是两组key,value键值对组成，可能计算了两遍，造成步长为2的情况

create table t8 (a int(1) primary key, id int(1) not null auto_increment, name varchar(64), key idx_id(id) ) engine=innodb;
insert into t8 (a,name) values(1,‘a47’);
insert into t8 (a,name) values(2,‘a47’);
insert into t8 (a,name) values(5,‘a47’);

这样试下看看呢

wuxiangdong · 2022 年8 月 23 日 06:34

猜测
非聚簇表，每次取自增的时候，取了双份，第一份给id,第二份给隐藏列

wuxiangdong · 2022 年8 月 23 日 07:06

create table t6 (
id int(1) not null auto_increment,
name varchar(64),
primary key(id) nonclustered
) engine=innodb;
这样t6和t7的表现就一样了

ShawnYan · 2022 年8 月 23 日 07:11

是也不是，这样t6.t7的表现，就都跟mysql不一样了。

ShawnYan · 2022 年8 月 23 日 07:22

神奇的表现来了，tidb 里自增竟然没变

test case:

drop table t8;

create table t8 (
a int(1) primary key, -- diff
id int(1) not null auto_increment,
name varchar(64),
key idx_id(`id`)
) engine=innodb;

insert into t8 values(1,1,'chen'),(2147483646,2147483646,'wu');

show create table t8\G

insert into t8 (`a`,`name`) values(2147483647,'a47');

show create table t8\G

mariadb

AUTO_INCREMENT 增 1

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> insert into t8 (`a`,`name`) values(2147483647,'a47');
Query OK, 1 row affected (0.01 sec)

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> select * from t8;
+------------+------------+------+
| a          | id         | name |
+------------+------------+------+
|          1 |          1 | chen |
| 2147483646 | 2147483646 | wu   |
| 2147483647 | 2147483647 | a47  |
+------------+------------+------+
3 rows in set (0.00 sec)

tidb

AUTO_INCREMENT 不变？

tidb> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2147513647
1 row in set (0.00 sec)

tidb> insert into t8 (`a`,`name`) values(2147483647,'a47');
Query OK, 1 row affected (0.01 sec)

tidb> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2147513647
1 row in set (0.00 sec)

tidb> select * from t8;
+------------+------------+------+
| a          | id         | name |
+------------+------------+------+
|          1 |          1 | chen |
| 2147483646 | 2147483646 | wu   |
| 2147483647 | 2147483647 | a47  |
+------------+------------+------+
3 rows in set (0.00 sec)

wuxiangdong · 2022 年8 月 23 日 07:25

tidb,非局促表，有_tidb_rowid隐藏列，mysql局促表有_rowid隐藏列

db_user · 2022 年8 月 23 日 07:31

tidb中不是和mariadb中结果一致么，id为自增列,我感觉和key-value的键值对数量有关，影响自增计算次数

ShawnYan · 2022 年8 月 23 日 07:34

不一致了啊，看表定义，上面是 AUTO_INCREMENT= 47,48，下面是47,47

db_user · 2022 年8 月 23 日 07:38

这块应该是tidb和mysql的不同，tidb是记录的当前最大值，mysql是记录的下一个值，你可以看下你上面的t6,主键做自增的也一样