0

I have a bit of a situation with a mySQL DB...

We have a registration queue, which ties a registration ID (Order) to a user in the form of an auto-incrementing key. When the registration process is completed, these users get migrated to another table and removed from the registration queue.

enter image description here

The second table houses the registered users. The registration ID (Order) is then passed into the column labelled 'Invoice'.

Today, the order/invoice numbers have seemed to 'reset' themselves... The previous day, the last Order/Invoice recorded was: 22904

enter image description here

Does anyone know what would cause this? I've been using Primary Keys for years with auto-increment and never have I seen anything like this. The Key type is set at bigint(20) for anyone that is wondering.

Registered users do get removed after the expiry date, but I thought auto-incrementing keys never use previous numbers.

Any help or insight on the matter is greatly appreciated.

Cheers, Matt

Matt U
  • 46
  • 5
  • Please [edit] your question to include the table structure you have, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query for SQL related questions. Also add the AUTO_INCREMENT value currently set for the tables, see https://stackoverflow.com/questions/15821532/get-current-auto-increment-value-for-any-table. Also include the code/query which transfers the data from one table to the other. – Progman Jun 25 '20 at 14:38
  • 1
    Have you optimized or restarted the database? Look at this: https://serverfault.com/questions/228690/mysql-auto-increment-fields-resets-by-itself – MrApnea Jun 25 '20 at 14:40
  • There's no way MySQL's auto-increment column will generate values smaller than the greatest value in the table, in either InnoDB or MyISAM engines. I guess your app is specifying those values, overriding the auto-increment. – Bill Karwin Jun 25 '20 at 14:44
  • @MrApnea Thanks for that article. We have reset the AUTO_INCREMENT back to an appropriate value and filled the blanks in with those incorrect Invoice numbers. Looks like its working for now, but I'm still curious as to why our database reset without our knowledge. – Matt U Jun 25 '20 at 15:07

0 Answers0