Undo Log Error: No More Space Left Over in System Tablespace for Allocating Undo Log Pages

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

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 cansee here.

What is a tablespace and why is it used?

A data file that can hold data for one or more InnoDB tables and associated indexes.

There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These are,

a. System tablespace
b. File per tablespace
c. General tablespace

System tablespace contains,

1. InnoDB data dictionary.
2. DoubleWrite Buffer.
3. Change buffer
4. Undo Logs.

Apart from this it also contains,

1. Tables &
2. Index data

Associated file is .idbdata1

The innodb_file_per_table option, which is enabled by default in MySQL 5.6 and higher, allows tables to be created in file-per-table tablespaces, with a separate data file for each table. Enabling the innodb_file_per_table option makes available other MySQL features such as table compression and transportable tablespaces.

Associated file is .idbd

InnoDB introduced general tablespaces in MySQL 5.7.6. General tablespaces are shared tablespaces created using CREATE TABLESPACE syntax. They can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.

Related Topics