MySQL插入后自增字段未递增问题排查求助

问题现象 #

账户审批流程突然失败，Codeigniter日志报错：

ERROR - 2023-05-17 10:24:52 --> Query error: Duplicate entry '10084' for key 'PRIMARY' - Invalid query: INSERT INTO tblUser (active, first_name, last_name, company, phone, email, username, password, ip_address, created_on) VALUES (0, 'Xana', 'Wolf', 'uvm', '000-000-0000', 'xanabobana@yahoo.com', 'Xana Wolf', '$argon2i$v=19$m=4096,t=3,p=1$beO82X4wsRUxieorH5iRjg$C9HPtN9TYYmLLw+/VPJGzgh6ieZidOHwXwbcYtkj/OE', '132.198.100.190', 1684333492)

手动执行ALTER TABLE tblUser AUTO_INCREMENT=10085;后插入成功，但AUTO_INCREMENT值停留在10085不再递增。

已做排查 #

• 检查MySQL的@@sql_mode，确认NO_AUTO_VALUE_ON_ZERO未开启，返回结果：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• 临时通过max(id)+1的方式插入成功，但需找到根本原因。

相关代码与数据 #

插入逻辑代码 #

$data = [
    $this->identity_column => $identity,
    'username' => $identity,
    'password' => $password,
    'email' => $email,
    'ip_address' => $ip_address,
    'created_on' => time(),
    'active' => ($manual_activation === FALSE ? 1 : 0)
];

// filter out any data passed that doesn't have a matching column in the users table
// and merge the set user data and the additional data
$user_data = array_merge($this->_filter_data($this->tables['users'], $additional_data), $data);
$this->trigger_events('extra_set');              
           
$this->db->insert($this->tables['users'], $user_data);

表结构 #

CREATE TABLE `tblUser` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varbinary(16) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` text NOT NULL,
  `salt` varchar(40) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `activation_selector` varchar(255) DEFAULT NULL,
  `activation_code` varchar(255) DEFAULT NULL,
  `forgotten_password_selector` varchar(255) DEFAULT NULL,
  `forgotten_password_code` varchar(255) DEFAULT NULL,
  `forgotten_password_time` int(11) unsigned DEFAULT NULL,
  `remember_selector` varchar(255) DEFAULT NULL,
  `remember_code` varchar(255) DEFAULT NULL,
  `created_on` int(11) unsigned NOT NULL,
  `last_login` int(11) unsigned DEFAULT NULL,
  `active` tinyint(1) unsigned DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_email` (`email`),
  UNIQUE KEY `uc_activation_selector` (`activation_selector`),
  UNIQUE KEY `uc_remember_selector` (`remember_selector`),
  UNIQUE KEY `uc_forgotten_password_selector` (`forgotten_password_selector`)
) ENGINE=InnoDB AUTO_INCREMENT=10097 DEFAULT CHARSET=utf8

实际插入的$user_data数据 #

{"active":0,"first_name":"Alexana","last_name":"Wolf","company":"uvm","phone":"000-000-0000","id":10097,"email":"xanabobana@yahoo.com","username":"Alexana Wolf","password":"$argon2i$v=19$m=4096,t=3,p=1$aK8SP0YLxQHewjskOiPBYQ$ZE7HZTRHderNSwlqs60eDXuQ16eM+22fugID5r4xzP8","ip_address":"132.198.100.89","created_on":1684511791}

问题根源 #

从$user_data的JSON可以看到，插入数据中包含了id字段（值为10097），这就是核心问题：

• 虽然你以为插入语句没指定id，但实际传递给insert方法的数组里已经带了id值
• MySQL在插入时如果显式指定了自增主键的值，会直接使用该值插入，不会触发自增序列的递增，所以AUTO_INCREMENT值保持不变
• 当这个指定的id值和现有数据重复时，就会抛出主键重复错误

出现这种情况的原因是array_merge后的$user_data包含了id，大概率是additional_data中传入了id，而_filter_data函数因为id是表中存在的列，所以没有过滤掉它。

解决方案 #

1. 排查additional_data的来源，确认为什么会传入id字段，去掉不必要的id值传递
2. 在插入前手动移除$user_data中的id字段：

unset($user_data['id']);
$this->db->insert($this->tables['users'], $user_data);

3. 修改_filter_data函数，排除自增主键字段（比如id），确保插入时不会包含该字段

内容的提问来源于stack exchange，提问作者xanabobana