AUTO_INCREMENT seeding not working intermittently

[Prashant Tekriwal]

Hi,

I am facing a weird situation where the AUTO_INCREMENT seeding to another number is not working intermittently.


The Background:

I have 3 tables (among others) which uses AUTO_INCREMENT for its primary key.

For table1 the key starts from 1, for table2 key starts from 500000 and for table3 key starts from 1000000.

I use below statements to seed the AUTO_INCREMENT for tabl2 and table3.

ALTER TABLE table2 AUTO_INCREMENT=500000

ALTER TABLE table3 AUTO_INCREMENT=1000000

The usage is like this. We have a tool that is used for running users tests.

Each time the tool is run a new database is created with same structure on MySQL Server.



The Problem:

After the deployment, for initial 3-4 hours all the execution of the tool ran fine but afterwards half of the executions started failing.

The reason for the failure was that even though table2 primary key is supposed to start from 500000, it was starting from 1 i.e. the default AUTO_INCREMENT seed value.

Now since the above mentioned ALTER statement is part of the sql file in which
database creation sqls are present, it is sure that the ALTER statement must have executed.

I am totally stuck here and do not know how to proceed further to resolve the issue.

The MySQL server version is 5.6.20.

PS: I know that many of you would suggest that the design of the database is not good and it must be changed which we know very well and can't do much about it at this point in time due to legacy code, so my request is to keep your responses to the solution of this problem only.

Let me know if any more information is needed.

Thanks,
Prashant