Auto increment not increasing after mysql insert?

Question

I have a strange behavior happening and I'm wondering if anyone can help! I have a table with Users, that has an auto_increment field for the ID. This worked correctly up until yesterday, when the account approval process started failing seemingly out of nowhere! I see in the Codeigniter php logs:

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)

I understand that a duplicate primary key can not be entered. If you look at the query being inserted, the primary key field is left blank and so should auto-increment.

When I checked the auto_increment value, it was set to 10084, and in the query above you can see it is trying to insert id 10084. So I manually increased the auto_increment value in the table to 10085 using the query

ALTER TABLE tblUser AUTO_INCREMENT=10085;

I ran the code again, and I was able to insert one record (using the code below), but then when I check the auto_increment value, it hasn't moved forward and is still at 10085.

The code generating the error above is this:

$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);

The insert statement looks correct to me, so I'm confused why the auto_increment value keeps not moving forward? I added code to check the max ID and just insert one more than that, and not make use of the auto_increment, but I just would like to figure out why this is happening!

I did find a thread about the mysql NO_AUTO_VALUE_ON_ZERO setting, but I checked and it wasn't set.

SELECT @@sql_mode

returns

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

I also posted on Codeigniter forums thinking it was something with their insert, but the only response said it sounds like a mysql problem...

Any ideas?

Updates: Here is the output for show create table tblUser ;

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

And here is $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}