线上又又又崩了(自增主键超过了int最大值)

yesewubian000

已于 2024-09-19 12:10:38 修改

阅读量380

点赞数 4

文章标签：数据库 mysql sql

于 2024-09-17 13:18:38 首次发布

本文链接：https://blog.csdn.net/yesewubian000/article/details/142313404

版权

问题出现：

中秋节，带娃在公园玩的正嗨呢？一个电话给呼叫过来了，线上又又又崩了。娃闹了老半天不走，都不是一颗糖能解决的了，好吧！给了一袋（6颗）。吃糖有害牙齿健康。减少吃糖。

问题定位：

哪个服务出问题看那个服务的日志。ctrl+G，跳转到最后？从后往前查询关键词：Exception。后台一坨的报错(习惯了，各种补丁，其它平台也各种崩。），其中比较低调新颖的就属这个。

 java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2147483647' for key 'PRIMARY'

主键重复，而且还是和这次业务相关的表。查询一下报错数量（| wc -l）呢？3千多条/小时，我这个服务20个节点，一个小时6万条的报错了。问题肯定就出现在这个地方了。

问题分析：

主键重复，看一下线上数据库倒叙。最大的正好是这个值。是程序到这个地方卡住了吗？数据库不能自动递增了？我看一下数据库,长度=20，这个长度也没有到20啊！而且看这个数有点熟悉（2147483647=21亿）。网上搜了一下，int最大值，看一下数据库类型果然是int.为啥当时不用bigint,为啥不用bigint

问题解决：

更改数据库类型。（此步骤可能耗时过长，亲测数据量：540万条，表大小：200GB 耗时：2小时）

备用方案：表备份迁移（快速让线上可用，详见附录1）

# 看文章也有删除主键,更改类型,重新设置主键. 
# 我现在线上并发比较大,服务也不能停, 不敢去掉主键约束,
# 担心插入很多重复数据(因为类型还是int,自增的结果都是2147483647).
ALTER TABLE `xxx`.`xxx`
MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id' FIRST;

数据库完活了，还有代码呢？代码用的int数据库更改为long，程序肯定也是崩溃的啊！更改代码里面的int为long，发版线上。OK了。

事后分析：

我们的数据也没有21亿啊！一天60万，一年2亿，现在跑了6年。12亿的数据。距离21亿还差着不少呢？
最后看了一下数据库自增的规则，数据库只存储了奇数的值。线上接近12亿的数据，自增的时候扩展一倍占用空间，约等于21亿。好吧！确实是数据量太大导致的。

附录1：

已有的数据更改完了，其它的表和数据库是否存在类似问题呢？
进行数据检测

SELECT k.TABLE_SCHEMA as 库,k.TABLE_NAME as 表,k.COLUMN_NAME as 字段,columns.DATA_TYPE as 类型,tables.AUTO_INCREMENT as 自增值,concat(tables.AUTO_INCREMENT/21474836.47,"%") as 使用量占比 
FROM information_schema.KEY_COLUMN_USAGE k
join information_schema.tables on tables.TABLE_NAME = k.TABLE_NAME and tables.TABLE_SCHEMA =k.TABLE_SCHEMA
join information_schema.columns on columns.TABLE_NAME = k.TABLE_NAME and columns.TABLE_SCHEMA =k.TABLE_SCHEMA and columns.COLUMN_NAME = k.COLUMN_NAME
# 类型为主键
where CONSTRAINT_NAME = 'PRIMARY'
# 自增数据不为空
and tables.AUTO_INCREMENT is not NULL 
# 表结构字段类型为int
and columns.DATA_TYPE = "int"
# 根据数据量由多到少排序
order by tables.AUTO_INCREMENT  desc

附录2：

直接更改线上字段类型耗时过长，着急提供服务怎么办？

# 1. 创建新表
create table new_table like	current_table;

# 2. 更改新表数据类型
ALTER TABLE new_table MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT;

# 3. 拉取当前表最近三天内数据到新表中(防止主表数据丢失导致业务异常)
REPLACE INTO new_table SELECT * FROM current_table 
	WHERE create_time >='3天内数据';
# 4. 将当前表更改名称为历史表,将新表更改表名为当前表
RENAME TABLE current_table TO old_table;
RENAME TABLE new_table TO current_table;

# ---------截止当前后，线上正常运行----------

# 5. 更改历史表数据类型（耗时较长，但不影响线上服务提供）
ALTER TABLE old_table MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT;

# 6. 更改期间的数据同步。
REPLACE INTO old_table SELECT * FROM current_table;

# 7. 表名称还原
RENAME TABLE current_table TO new_table ;
RENAME TABLE old_table TO current_table ;

# 8. 删除临时表
drop TABLE new_table;