1

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.

Invoice table

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.

7
  • 2
    Someone seems to have reset the auto-increment. Not sure what your question is.
    – mustaccio
    Jan 4 at 12:36
  • 2
    Why does it matter? Why would you have a surrogate key exposed to the users? Jan 4 at 13:51
  • @mustaccio My question, stated halfway down, is 'How, or more particularly why, this should suddenly be happening?. I'm the only user and the only one with admin DB access and I haven't reset the auto inc. I want to understand what is happening. Jan 4 at 14:24
  • 1
    @Charieface I thought someone might say something like that. The invoice ID is used by many processes in my application and is also passed to third parties for tracking direct debit payments. I'm aware that so long as it is unique it doesn't matter what its actual value is but as a programmer I want to know how/why an unexpected value was generated. Doing the same insert with the backup DB, which also has id 3 and 6 missing and is identical except for the last two odd invoices, did not set the PK to 3 but to 2511 as I expected. Jan 4 at 14:31
  • This is an InnoDB table. Even if I try to manually reset the auto_increment counter using ALTER TABLE tablename AUTO_INCREMENT = n I 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. Jan 4 at 14:38
0

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.

5
  • Yes I agree with you and appreciate that can be done that way. But I wrote the code that does the insertion and it is done using the SQL in my post. You can see that in this case I do not assign a value to the autoinc field. I leave that to InnoDB. That's why I cannot understand how this could have happened (or why it should suddenly happen now). Note this is not a website backend accessed by thousands of users. This is a mountaineering association membership database accessed by myself and one other user using software that I wrote myself. All insertions are done using the sql in my OP Jan 4 at 22:44
  • I also log all insertions myself as part of the application that I wrote and at at no time is the invoice_id specified when inserting into the invoice table. Quite the contrary, after the insert I recover the invoice id using SET @the_invoice_id = LAST_INSERT_ID(); so that the invoice_id just generated can be inserted into another table as a simple, non indexed data field. I'm not dismissing your suggestion, as I fully endorse it, I'm just mystified how this could happen - and I guess how I can prevent it happening again. Jan 4 at 22:51
  • I suggest logging queries in the MySQL Server, not in your app, because that will show whether there are some queries run by other clients you have forgotten about, or which you assumed were not running. Jan 5 at 1:19
  • I'm not sure I have the permissions to do that. The database is hosted by a remote ISP and my application logs into it remotely so while I have pretty much full permissions on tables etc, trying to execute things like SET GLOBAL general_log = 1; gives me access denied Jan 5 at 8:31
  • I have never seen the symptoms of auto-increment going wrong. Sorry, I can't offer an explanation of why this would happen, except for a pretty severe code bug in InnoDB. You should search bugs.mysql.com for similar symptoms reported. Jan 5 at 15:03
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.