Ask us if you have any question about PHP, MySQL, Apache,
Linux or optimizing your website for quick answers!!!

OPTIMIZE TABLE on InnoDB table resets AUTO_INCREMENT counter

MySQL recently fixed a known bug (I was not aware of this before) of OPTIMIZE TABLE (in MySQL Server 5.1.55, 5.5.9) in which OPTIMIZE TABLE statement was not preserving the AUTO_INCREMENT counter on OPTIMIZE TABLE operation.

Want to see yourself? Lets do it (code taken from the MySQL bug report)

  1. CREATE TABLE a (a INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT) ENGINE=INNODB;
  2.  
  3. INSERT INTO a VALUES ();
  4. INSERT INTO a VALUES ();
  5. INSERT INTO a VALUES ();
  6.  
  7. DELETE FROM a ;
  8.  
  9. SHOW TABLE STATUS LIKE 'a';

In the below table status, you can see the AUTO_INCREMENT value is 4 (even after deleting all the rows which is normal behavior).

  1. mysql> SHOW TABLE STATUS LIKE 'a'\G
  2. *************************** 1. row ***************************
  3. Name: a
  4. Engine: InnoDB
  5. Version: 10
  6. Row_format: Compact
  7. Rows: 3
  8. Avg_row_length: 5461
  9. Data_length: 16384
  10. Max_data_length: 0
  11. Index_length: 0
  12. Data_free: 4194304
  13. Auto_increment: 4
  14. Create_time: 2011-02-20 07:52:18
  15. Update_time: NULL
  16. Check_time: NULL
  17. Collation: latin1_swedish_ci
  18. Checksum: NULL
  19. Create_options:
  20. Comment:
  21. 1 row in set (0.00 sec)

Now execute the OPTIMIZE TABLE command and check the table status

  1. OPTIMIZE TABLE a;
  2.  
  3. SHOW TABLE STATUS LIKE 'a';
  1. mysql> SHOW TABLE STATUS LIKE 'a'\G
  2. *************************** 1. row ***************************
  3. Name: a
  4. Engine: InnoDB
  5. Version: 10
  6. Row_format: Compact
  7. Rows: 0
  8. Avg_row_length: 0
  9. Data_length: 16384
  10. Max_data_length: 0
  11. Index_length: 0
  12. Data_free: 4194304
  13. Auto_increment: 1
  14. Create_time: 2011-02-20 07:52:18
  15. Update_time: NULL
  16. Check_time: NULL
  17. Collation: latin1_swedish_ci
  18. Checksum: NULL
  19. Create_options:
  20. Comment:
  21. 1 row in set (0.00 sec)

MySQL fixed this bug 5 years after the bug report.

So be careful with OPTIMIZE TABLE if you have older MySQL versions.

TRUNCATE TABLE reset the AUTO_INCREMENT to 1 so don't confuse this example with TRUNCATE TABLE.

Still need help? Ask us

2 Comments »

  1. gino pilotino said,

    May 2, 2011 @ 5:42 pm

    i’ve lost my morning on this bug, is there any workaround for older versions ?

  2. Wasif said,

    June 25, 2011 @ 1:57 am

    Use ALTER TABLE … AUTO_INCREMENT=… to set the correct value after the
    OPTIMIZE in conjunction with LOCK TABLES so that no other use can insert more data to the table.

RSS feed for comments on this post

Leave a Comment