0

I'm trying to remove some data to get some extra free space, but running the "DELETE FROM table ..." query gives me this error:

Error Code: 3019 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 found this:
https://stackoverflow.com/questions/42628687/undo-log-error-no-more-space-left-over-in-system-tablespace-for-allocating-undo
The recommend to run this query:

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

But it gives me an Error Code: 1064 MySQL version: 5.7.31

  • 1
    that works only with mysql 8 , with 5.7 it is somewhat more complicated see mysqlserverteam.com/online-truncate-of-innodb-undo-tablespaces – nbk Aug 20 at 21:25
  • do you know some solution to delete the rows without writting undo logs? that article says I need to restart mysql to change innodb-undo-tablespaces. Or maybe there is some way to truncate the undo logs manually? in that case I could delete the rows in smaller chunks and remove the undo logs in between – Enrique Aug 20 at 21:59
  • no that actually it, innodb removes automatically, so there is normally no need for it. but as i mentioned this gets more complicated – nbk Aug 20 at 22:01
  • In the long run, consider alternatives to a big DELETE: mysql.rjweb.org/doc.php/deletebig . In the short run, please provide more details: disk size, log size, SHOW VARIABLES LIKE 'innodb%'; – Rick James Aug 21 at 14:04
0
  1. Is possible to TRUNCATE TABLE ? If yes, please try that.

  2. Is MySQL server binary log enabled ? If YEs, Purge binarylogs & then delete records.

  3. Is MySQL Server enabled with general log or Audit Log ? If Yes, Purge general log

  4. Check error log size & path if needed purge it.

Note: Delete statement will not release space & if your server is INNODB_FILE_PER_TABLE=OFF then TRUNCATE or DROP statement will also not reclaim the space

| improve this answer | |

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.