November 9th, 2013, 02:43 AM
Mysql Auto generate duplicate id after restart the server(deleted primary key)
Mysql - Innodb engine
My problem is after delete primary key record(Auto Increment) the mysql behaviour is it will not generate same id again. Because, the auto increment from mysql server session. But, after restart mysql server it is generating duplicate id for particular table. The reason is mysql session is destroyed once restart the server.
Soln :Mysql server itself keep some where the max increment id in my sql server.
Or the duplicate primary key should not generate(after restart the mysql server). Is it possible...?
Thanks. looking forward your reply
November 13th, 2013, 05:06 AM
The first time after a restart of MySQL you have an insert that auto increments an id on a table the value is generated by performing a SELECT MAX(id) + 1.
After that the current auto_increment value is stored in memory for performance.
This implementation by InnoDB usually works because you seldom delete the newest records from a table where you have auto_incremented the primary key.
So to avoid duplicate keys you either:
1. Don't delete records before reboot
2. Create a separate table that stores the auto_increment value.