Recently, I received this error from MySQL:

Failed to read auto-increment value from storage engine

Turning towards these SO questions:

• Error: Failed to read auto-increment value from storage engine
• mysql Failed to read auto-increment value from storage engine

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.

CodePudding user response：

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.