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

Improve this question
5
  • 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, 2020 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, 2020 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, 2020 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, 2020 at 14:04
  • The MySQL 1064 error is a syntax error. MySQL 5.7.31 doesn't understand that command. Confirm if you really need undo tablespaces, if not, that command is right. You can do what Rick said above or learn more and adjust the configuration of undo tablespaces dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html
    – mysql_user
    Mar 2 at 17:40

1 Answer 1

Reset to default
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 and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.