0

So, my setup is a little weird. I've got a laptop with 250 GB of memory and a table with roughly 100 million rows (InnoDB), which occupies around 130 GB of memory. The thing is, this table includes too many redundant columns which are not required anymore. But somehow during the drop operation I'm suddenly out of disk space. I assume that in order to drop the columns, InnoDB just copies the table in order to swap it with a new one without these columns. So, in effect, I can't delete the columns. But I'd love too. However, I've got an external drive. But Mysql Workbench export wizard won't allow me to export the table to the external drive, in effect it still creates a copy on my local drive, which make my computer run out of memory. I assume it will move the resulting file to the external drive, but it can't happen because of the memory limitations. What's the best strategy in these circumstances?

Improve this question
3
  • May be you can run mysqldump from the command line and redirect its output as desired?
    – mustaccio
    Commented Apr 15, 2022 at 18:08
  • @mustaccio actually it doesn't allow you to manually pick columns, so it doesn't seem to be a good idea either
    – Simullacra
    Commented Apr 15, 2022 at 18:56
  • Is the 130GB the amount of space used on disk? How much spare disk space is there? Was the table created when innodb_file_per_table was ON? Workbench is not the only way to get things done; are you using some kind of cloud system with limitations?
    – Rick James
    Commented Apr 15, 2022 at 20:42

1 Answer 1

Reset to default
1

I was able to resolve my own issue I select <required columns> into outfile'ed my database to the external drive, truncated it, dropped columns and reloaded the outfile back.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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