This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
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...
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.
Joined: Dec 02, 2003
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.
Joined: Dec 02, 2003
Session thing may not work.. some app.servers persist sessions