Search code examples
mysqlinnodb

Not able to set Auto_increment value lower then already inserted maximum value on InnoDB table in mysql

I have created 1 table. That table has Id field primary key and auto increment. Last inserted id is 15000.

Now I have manually inserted 99999 in this Id field.So I am getting next increment value as 100000. But I want my next auto_increment value 15001.

So for that i used alter table tbl_name auto_increment = 15001;

But getting next auto_increment value as 100000.

I want next auto_increment value as 15001.

How to do this on INNODB engine in mysql?

Solution

  • The MySQL documentation is clear on that:

    You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

    What you are attempting can't be done with alter table.