Mysql – Alter on huge table consumes all disk space

alter-tableerrorsMySQLmysql-8.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?

Best Answer

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?

Related Question