Welcome Guest, Not a member yet? Register   Sign In
Using db insert in CI3 auto increment off in table?
#1

(This post was last modified: 05-17-2023, 08:14 AM by xanabobana. Edit Reason: updated error text as code for easier readability )

Hi-  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.  Yesterday, the account approval process started failing and I see in the CI logs: 

Code:
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', '[email protected]', 'Xana Wolf', '$argon2i$v=19$m=4096,t=3,p=1$beO82X4wsRUxieorH5iRjg$C9HPtN9TYYmLLw+/VPJGzgh6ieZidOHwXwbcYtkj/OE', '132.198.100.190', 1684333492)
I reset the auto_increment value on the table, and I was able to insert one record, but then the next insert I get the above error again and the auto_increment value hasn't moved forward.
The code generating the error above is this: 
Code:
$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 getting out of whack?  I can add something in the code to check the max ID and make sure the auto_increment value is set correctly, 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.
Reply
#2

(This post was last modified: 05-18-2023, 11:30 PM by JustJohnQ.)

This is more a mySQL problem than a CI problem. Depending on how you have reset the autoincrement for the id field, it might be that there are some gaps in the id, causing mySQL to think that it can autoincrement starting at a lower value than the actual highest id.
Check the autoincrement value using:
Code:
SHOW TABLE STATUS LIKE 'table_name'
My guess is that the value is lower than 10084, the id that exists in your table.
Set the autoincrement one higher than the absolute highest value in your table:
Code:
ALTER TABLE table_name AUTO_INCREMENT=10085;

Now, mySQL will autoincrement as expected.
Reply
#3

I did reset the auto increment. The first insert is fine, but then the second fails because the auto increment isn't incrementing...
Reply
#4

(This post was last modified: 05-19-2023, 06:48 AM by JustJohnQ.)

What value do you get for autoincrement:
Code:
SHOW TABLE STATUS LIKE 'table_name'
Reply




Theme © iAndrew 2016 - Forum software by © MyBB