0

I'm running MySQL 8 on Ubuntu 19. I have a table that is 543G and I have to add a new column and index. I've started with the new column:

alter table hugeTable add column newCol tinyint(1) after existingColumn;

After a few hours of running, I received an error, roughly:

... table 'hugeTable' is full

I was looking around and I didn't see anything that was bad - I still had roughly 294G of disk space left. Looking at the error log, it became apparent what happened - it's generating a #sql file that keeps growing. I kicked it off again to get a feel for how quickly it grows and in just an hour it's over 35G:

-rw-r----- 1 mysql mysql  35G Nov 19 21:53 '#sql-ib1124-819861495.ibd'

and growing quickly.

So the real question is, is there a way around this? I have a backup of this table, so it's OK to risk turning this off and running the update with no undo, #sql, etc files. OR, will I have to use sed to modify the backup file and use that?

0

Make use of the new, better, ways of doing ALTER:

SET SESSION alter_algorithm='INSTANT' ;
ALTER TABLE foo ADD COLUMN ... ;  -- without the AFTER

SET SESSION alter_algorithm='NOCOPY' ;
ALTER TABLE foo ADD INDEX name(col1, col2);

If these cannot be done with the requested algorithm, they will give you an error without doing the ALTER. From there, you can decide what to do.

A single-column index on a "flag" is rarely useful. Perhaps you don't need the new index?

  • I marked this as the correct answer - I had to change my alter script. Adding the new column to the table in the middle isn't allowed with INSTANT, but adding it to the end, this took milliseconds on the 543G table - thanks! – Jerry Skidmore Nov 22 '19 at 18:12
  • "A column can only be added as the last column of the table. Adding a column to any other position among other columns is not supported." – Rick James Nov 22 '19 at 19:43

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.