Table restarts ID by deleting all records

Question

Table restarts ID by deleting all records

Navigation

#1 by (0 votes)

0

Today I come with a question about something that had never happened to me, and maybe MySql uses it but I want to tell you to see if it has happened to you and if there is a way to solve it. I have a small sales system (POS) where the sales of the day are registered in the "Sale" table, and when the shift ends they go to the SalesTales table and delete all the "Sale" records to be used for the next shift . This in order that when you create a report the system has only the information of completed shifts and I discard the sales of the current shift.

The detail is that, everything works perfectly, sales, and closing shift, sales IDs, which are self-enhancement work well. If I close a shift and at the moment I continue with the sale, zero problems, the IDs continue the series, but, if I turn off the server (personal PC), the light goes out etc, IF the Sales table was empty, the IDs are reset to 1, which causes repeated keys.

If I continue with the sales, the series respects it, and thus closes the server etc, continues without problems. The detail is when the table is empty and the server is closed, when reopening, the IDs are reset to 1.

It's the first time I work this way, I know I could create queries that rule out the sale of the day, and I looked for sales of finished shifts etc. But I am very interested in WHY MySql reboots the IDs instead of continuing the series, even when the table is empty

Thank you very much for your help

mysql autoincrement

asked by Invitado 09.10.2018 в 05:16

source

1 answer

Execute a rest service before loading the components children Raw Entity Framework

score 0 · Answer 1

Understanding that it uses InnoDB as a storage engine, in version 5.7 and earlier of MySQL, the AUTO_INCREMENT counter is stored only in memory and not on disk. When the MySQL server is restarted, InnoDB executes the equilavente to:

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

If the table does not contain tuples or records, basically, the counter will be initialized.

Since version 8.0 of MySQL, the behavior changed and the counter is stored in the redo log for each change, thus allowing the value of the counter to persist in each MySQL reboot.

In the documentation (I regret that it is in English) of MySQL all the details are found, if you want to expand the information, visit: 15.8.1.5 AUTO_INCREMENT Handling in InnoDB .