Search code examples
mysqlalter

Alter huge MySQL table with little free disk space

I'm having some trouble to ALTER a huge (InnoDB) MySQL table. The ID column of the table (which is the primary key) is defined as an UNSIGNED INT but reached it's maximum value (4294967295).
To be able to add more rows to this table, I need to adjust the type of this column to BIGINT. However, a standard MySQL ALTER command (and any other solution I've found so far) will try to generate a new table with the new definitions, and then copy all data to it. For the table in this case, this requires 942.0Gb of free disk space, while I only have 271Gb available (and there is no other partition that does have the required disk space available).

Are there any solutions that do NOT require an entire duplication of the original table (but rather move the data to the new tables, or something similar)?
I don't care about not being able to access the table while it's being altered, the table can be completely locked for several hours with no problem (I can't use it at this moment anyway).

Solution

  • Since you have 271Gb of free disk space and you are ok if table is not being accessed for few hours, follow below steps:

    1. Create a new table lets say tbl_temp with ID as BIGINT keeping remaining table structure as exact same.
    2. Create a simple process ( any server side scripting language you use ) that will select a row from original table and insert it into tbl_temp.
    3. Delete the row inserted from original table.
    4. When all rows are inserted, your original table will be empty. Delete original table
    5. Rename tbl_temp back to original table.

    This way you will be able to migrate entire data with existing disk space.