1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Rep Power

    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
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Stockholm, Sweden
    Rep Power
    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.

IMN logo majestic logo threadwatch logo seochat tools logo