1

Recently, I received this error from MySQL:

Failed to read auto-increment value from storage engine

Turning towards these SO questions:

I realized I needed to increase the size of the auto_increment field so we could keep adding records to our database. I increased the size of the field from int to bigint unsigned, but the auto_increment size for the table didn't increase when I changed the field.

In other words, when using show table status, my table is showing AUTO_INCREMENT=2147483655 before and after I updated the column (which obviously defeats the purpose of the update).

Is there anyway that I can fix my table so that it correctly shows the max size of the auto_increment field (AUTO_INCREMENT=18446744073709551615) without having to recreate the table?

I've tried dropping auto_increment from the field and re-adding it back, but that didn't work.

Kyle
  • 685
  • 7
  • 14
  • 1
    ...how did you exceed **2 billion** rows? – Dai Nov 24 '22 at 05:39
  • 1
    @Dai It's not that hard. We had to change the ID column from INT to BIGINT a few years ago when it overflowed. We don't actually have 2 billion rows at once, because we delete old rows periodically, but those IDs don't get reused. – Barmar Nov 24 '22 at 06:21
  • 1
    @Dai I've seen this happen in the wild. For example, if many inserts fail, the auto-increment values are lost. Or if someone inadvertently inserted a row specifying a value like 2 billion minus two, overriding the auto-increment. – Bill Karwin Nov 24 '22 at 07:37
  • That's a very good question @Dai. We're tracking price changes in our market over time, and there are a lot of products with a lot of price changes :) Admittedly, the auto-increment grew so quickly due to failed inserts (which we're looking into), but we would've hit this wall eventually – Kyle Nov 24 '22 at 09:59

1 Answers1

1

No, if you need to change the data type of the primary key, you must do a table restructure. It cannot be done as an inplace change.

You can use an online DDL tool such as pt-online-schema-change or gh-ost to do the conversion while still querying the table. Though you obviously can't insert new rows to the table until you upgrade the primary key.

Remember to convert foreign key columns in any other tables that reference this one first. You don't want to allow inserts of new rows into the referenced table until foreign keys can also hold the values.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Got it. Thanks Bill, that was a concise and very helpful answer. I appreciated the tool recommendations and especially appreciated your comment about foreign keys (which is a great reminder). Marking this as closed – Kyle Nov 24 '22 at 10:01