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?
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.
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
2 <- 3 is missing
5 <- 6 is missing
9 and then every 1 thereafter until into the thousands
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_id7 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.
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
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?
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.
ALTER TABLE tablename AUTO_INCREMENT = nI shouldn't be able to set it to a value less than the current maximum. So I want to learn how it's possible for some other mechanism to get it set to a lower value when I don't manually reset it.