Undo Log error: No more space left over in system tablespace for allocating UNDO log pages

Question

I am importing a csv file into a table in a mysql database using load data infile command. The csv file is pretty big (around 10 Gig). In the middle of the import, I get the following error:

Undo Log error: No more space left over in system tablespace for allocating UNDO log pages. Please add new data file to the tablespace or check if filesystem is full or enable auto-extension for the tablespace

What is this error for?

Answer 1

The MySQL system comes with a way to rollback changes using this "UNDO log file". It's also used for coherency. With large datasets, that log file may grow too fast and be filled up. Then you get that error. The idea is to be able to undo the last command. This is similar to going in a paint system, for example, making changes to an image and then clicking Ctrl-Z. That's what the UNDO log file is there for.

To avoid having the table running, you can mark it as inactive:

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

You can also delete the table altogether (not recommended) or allow for auto-truncation, which may be slow. The auto-truncate makes sure to remove data as required.

For more info you can see here.