MySQL suddenly producing out of sequence autoinc numbers

Question

In MySQL (8.0.27-cll-lve) have an invoice table using an autoincrement field, invoice_id, as the PK. A transaction inserts data for a new invoice, uses LAST_INSERT_ID() to get the new invoice_id and then inserts that into a transaction table along with other stuff.

For a couple of years the autoinc invoice_id has been steadily increasing by 1 each time a new invoice was inserted and yesterday stood at 2509.

However the penultimate transaction inserted an invoice_id of 3 (instead of the expected 2510) and the next one inserted an invoice_id of 6

Any idea how, or more particularly why, this should suddenly be happening?

Additional info

1. About a fortnight ago a few invoices were intentionally deleted via the application but they had invoice_ids of 1803, 1779 ie not the most recent ids, and there have been other deletions over the years with no change in the autoinc sequence.

2. A snapshot backup of the database taken just before the inserts on the live DB, shows the lowest sequence of invoice_id, which were generated in 2019, to be

   1

   2 <- 3 is missing

   4

   5 <- 6 is missing

   7

   8

   9 and then every 1 thereafter until into the thousands

   .

   .

   2507

   2508

   2509

   2510 <- last invoice_id generated (snapshot taken before the two inserts in question)

So it appears that MySQL is re-using the missing 3 and 6 - but why suddenly after several years?

Code in case it helps

Last two transactions

START TRANSACTION; 
INSERT INTO invoices (member_id,  fee_type,  fee_year,  invoice_amount,  invoice_date,  invoice_ref,  user_comment) 
VALUES ( 4525, 3, 2022, 31, '2022-01-03', '2022 Pro', 'null_comment' );
SET @the_invoice_id =  LAST_INSERT_ID(); 
INSERT INTO payment_invoice_allocation (invoice_id,allocated_amount,transaction_date,allocation_type, user_comment) 
VALUES (@the_invoice_id, 31, '2022-01-03', 'receivable','null_comment' ); 
COMMIT;


START TRANSACTION; 
INSERT INTO invoices (member_id,  fee_type,  fee_year,  invoice_amount,  invoice_date,  invoice_ref,  user_comment) 
VALUES ( 3921, 4, 2022, 56, '2022-01-03', '2022 Don', 'null_comment' );
SET @the_invoice_id =  LAST_INSERT_ID(); 
INSERT INTO payment_invoice_allocation (invoice_id,allocated_amount,transaction_date,allocation_type, user_comment) 
VALUES (@the_invoice_id, 56, '2022-01-03', 'receivable','null_comment' ); 
COMMIT;

Invoice table DDL

CREATE TABLE invoices (
  invoice_id int NOT NULL AUTO_INCREMENT,
  member_id int DEFAULT NULL,
  fee_type int NOT NULL,
  fee_year int DEFAULT NULL,
  invoice_amount decimal(10,2) DEFAULT NULL,
  invoice_date date DEFAULT NULL,
  invoice_ref varchar(300) DEFAULT NULL,
  user_comment varchar(100) DEFAULT NULL,
  date_recorded datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

• although, table info in my DBMS now shows the last line of the live DB to say

  ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 (which seems to suggest it is going to duplicate invoice_id 7 next) and that of the backup DB to be

  ENGINE=InnoDB AUTO_INCREMENT=2511 DEFAULT CHARSET=latin1 which is what I would expect.

Screenshot of the invoice table in the DBMS ordered in descending order of date/time of invoice creation. ie last two inserts at the top.

Edit

Further testing in response to comment by user mustaccio.

I've just done a test in another copy of the DB that I use just for trying things out.

I inserted a new invoice using the SQL above. The PK in the invoice table autoincremented to 2076 (the next highest available number in that table) and inserted in the new record as the invoice_id.

I then deleted the three rows with invoice_id 3, 4 and 5 to artificially make a 'gap' like there was in the live database.

I then ran the SQL ALTER TABLE invoices AUTO_INCREMENT = 3 to try to re-create what mustaccio said had happened. Then I ran the insert code again to insert another new invoice.

As I expected, it ignored me trying to reset the autoinc lower than the current max of 2076 and the new record went in with id = 2077.

So even trying to reset it manually I can't make the autoinc 'fill in' missing values. - So how did it happen with no manual intervention?

Edit 2

I notice that recently the ISP I use to host the remote database changed their cpanel interface from 'Paper_lantern' to 'Jupiter'. I believe this is just a cosmetic change to the cpanel GIU but could that have any implication as to how the MySQL works? - I don't think so but if I was sure, perhaps I wouldn't be asking those here that know more about these things than I do.

Edit 3 It gets worse, and other have had the same issue

I did a couple of test inserts one after the other. The first one gave an autoinc value of 10 (a previously deleted value between 9 and 11) and the second gave a Duplicate key error when the autoinc tried to assign the new row an ID of 11 when that already existed.

A post here from 2009 describes exactly the same problem, although gives no reason and ambiguous solutions.

I think I may have resolved my issue by dropping the table invoices completely and rebuilding it line by line from an SQL dump taken a few days ago. That seemed to reset the autoinc index back to where it should be (earlier I could see in PhPMyAdmin that it had the wrong value). However I would still like to know what causes this in case it happens again.

Answer 1

Thanks to the suggestion by Bill Karwin I found that these symptoms are, or at least were, caused by a InnoDB table bug (id number 199 in bugs.mysql.com) here that was first reported in 2003 and still going strong in 2017.

It is due to a server restart following the deletion of some rows in a table with an autoinc field.

I've deleted rows in the past and my ISP almost certainly restarted their server recently as I know they changed their cpanel software just before I experienced the symptoms.

However, bugs.mysql.com say it was fixed in version 8.0.0 and my ISP is running version 8.0.27 (or at least it is at the time of this post). So either the bug is still there or the plot thickens.

Answer 2

InnoDB does not generate auto-inc values less than the highest value in the table. Even if you use ALTER TABLE to set the auto-increment to a lower value, it immediately resets to the max+1 value.

But a client can run an INSERT statement and specify a value manually. This can be one of the unused values you identified. It doesn't have to be greater than the current highest value in the table. In this way, clients can "fill in the gaps."

This is the most likely explanation: one or more of the clients inserting data to your table is specifying id values, not relying on the table to generate auto-incrementing values. You could enable the MySQL query log to confirm this.