Search code examples
databaseinnodb

MySql make like a rollback some times automatically

We have many computers with POS system, each one of them have an local database where we register all sales, and at finish of the day, we take the data and insert in a master database, at the moment we have observed that in some computers, MySQL make like a rollback, for example, when we install the system in our table config have the auto increment id, and set the first sale with and id 1000, and this id change in the other sale at 1001, 1002, 1003, etc. But one day, when the id was 5000, the database reset all data in tables and start again in 1000, have you some idea what could be happening.

Thanks!

Solution

  • Need more details to understand what happened. Here's some info that may explain it anyway:

    After MySQL is shutdown, or crashes (power failure, etc), and is restarted, the first thing to be done for each InnoDB table with an AUTO_INCREMENT it so compute the next id as SELECT MAX(id)+1 FROM tbl.

    Even if this does not explain your situation, here is a gotcha that may bite you:

    1. create id 1234 (and COMMIT it)
    2. delete 1234 (and COMMIT)
    3. restart mysql (gracefully or due to crash)
    4. The next id to be created will be 1234. This can be bad if you assume an id cannot be reused.

    If the restart had not happened, step 4 would have created 1235, and 1234 would be forever "burned".

    (Note: Version 8.0 changes this behavior.)