Error ERROR 3019 (HY000) when cleaning MySQL database (No more space left)

Question

I have a MySQL database and a big table on it called "tc_positions". Recently I ran out of space on my pc and I'm trying to clean up the database using the command:

delete from tc_positions where servertime < DATE(NOW() - INTERVAL 180 day)

However, I'm encountering the following error:

ERROR 3019 (HY000): 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.

I try with:

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

but I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNDO TABLESPACE tablespace_name SET INACTIVE' at line 1

I don't know hot to get the undo tablespace name (I'm a noob student), I'm on mysql Ver 14.14 Distrib 5.7.31.

now Is there any way to clean the database out of space in this situation?

Solution: Finally I delete day by day records, then I duplicate a part of the table with 3 days data, drop the biggest table, rename the copy as the original and done. Thanks for all!

• Lajos Arpad: Have you considered removing something else and then cleaning your database?
• ticktalk: as @LajosArpad suggested , that could include other files outside of the mysql instance so you can add to the appropriate tablespace to do your clean up then restore when done. longer term you'll need a 'strategy' to deal with exhausting tablespaces - regular purge of older data etc ...
• Claudio Carrasco: Yes, but at this point the database takes up almost all the storage space
• Claudio Carrasco: yes @ticktalk, I agree with you, but at this point, I have no choice but to delete old data from the database in order to reduce its size.
• ticktalk: restart your db instance, that will (should) clear any undo logs. If your diskspace is nigh on exhausted and you cannot (temporarily) free up space to add to the system undo logs then you have other issues.

Answer 1

How many records are in that table roughly? If the delete is a large amount of records, you might need to start with a smaller interval. Something like...

delete from tc_positions where servertime < DATE(NOW() - INTERVAL 180 day) limit 10000;

The command you're running doesn't work on the 5.7 distro sadly, which is why you're getting the syntax error.

MySQL also doesn't always reduce the size of a file when data is removed, especially if innodb_file_per_table is turned off. You'll want to enable it, and then alter table tc_positions engine=innodb There are other ways, but they require a lot more diskspace, which you don't seem to have.

• easleyfixed: So they could upgrade to a newer distro and fix the problem? Also I wonder if they could pop in a USB on the MySQL server and allow it as additional new space as some sort of temp buffer to get over the clear out hump then remove the stick and unmount the spare space?
• RHaberle: A USB isn't a terrible idea depending on how big the DB is. If it's on a personal DB I would be a bit surprised if there wasn't some normal hygiene of the machine that could free up enough space for the undo log. For updating, I'd be surprised if they could download the 8.0 distro at this point, doesn't leave a lot of room for backups either. But yes, migrating and running that command would help.
• easleyfixed: I'm not saying put data on it, I'm saying expand the space just enough for the logs that refuse to be generated so you can actually delete the data in question. Then you remove the usb from the system since its no longer needed, a temp cache that will be removed after you are done. Not ideal, but a way ahead.