aspose file tools*
The moose likes JDBC and the fly likes Problem with AUTO_INCREMENT index reset when DB restarts Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with AUTO_INCREMENT index reset when DB restarts" Watch "Problem with AUTO_INCREMENT index reset when DB restarts" New topic
Author

Problem with AUTO_INCREMENT index reset when DB restarts

Vladas Razas
Ranch Hand

Joined: Dec 02, 2003
Posts: 385
I am using MySql 4.something. I am observing this behaviour - when the data table is emptied and then DB restarted auto_inc index is reset.

I have a problem with this. Currently I am working on web app where every user can add and delete records from table. User A comes in and enters data which seen by User B. User B selects record for deletion (using index) and goes for lunch. User A decides he was terribly wrong with the data he entered before. He clears it all. Not sure why he restarts DB. User B comes back from lunch and presses Delete for the record he marked before. Boom. The wrong one is deleted because record with ID=2 is now completely another record...

How could I solve this problem?
Thanks!
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
You should be asking this on a mysql forum of some sort.

This may be a bug but I more suspect the following...

A TRUNCATE will reset the counter in mysql. Also in MySQL if you do the following...

DELETE FROM table;

With no where then MySQL turns that into TRUNCATE TABLE table because that will be faster to execute and produce mostly the same result... but as noted a TRUNCATE will reset the auto increment fields.

I have never seen any behaviour where a restart has some effect. I doubt this is the source of your issue.
Vladas Razas
Ranch Hand

Joined: Dec 02, 2003
Posts: 385
I did delete all records by hand. I executed batch (prepared statement) that would delete all records like "DELETE FROM mytable WHERE ID=?". And then restarted MYSQL. If table is not empty auto_inc index is not reset, but if it is - index reset.

Since it's webapp I am thinking to enforce "refresh" on user everytime his session is invalid (i don't need session otherwise). And then explicitly write in documentation "do not restart MYSQL when servlet container/application server is running". So if the session is invalid, and it should be when container restarted, no more data changing until new data is retrieved.
Vladas Razas
Ranch Hand

Joined: Dec 02, 2003
Posts: 385
Session thing may not work.. some app.servers persist sessions
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem with AUTO_INCREMENT index reset when DB restarts