This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with AUTO_INCREMENT index reset when DB restarts

 
Vladas Razas
Ranch Hand
Posts: 385
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 385
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 385
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Session thing may not work.. some app.servers persist sessions
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic