0

I'm importing a fairly large set of databases into a system which has a / partition only 10GB large. After a previous failed import, I dropped the database, but noticed /var/lib/mysql didn't exactly empty out - Now I'm watching the root partition fill up with this file:

$ sudo lsof / | grep deleted
mysqld    53058     mysql   41u   REG  253,0 951599104   5523 /tmp/MLR7HVLj (deleted)

This file is growing large enough (2GB) that I'm starting to worry that it'll crash my system. Is this a known problem (Old centos 6 mysql version 5.1) with trying to import a database after it's been dropped already, and is mysql aware of limited space in the partition it's filling up with a deleted file?

1

SHOW FULL PROCESSLIST -- there is probably a complex query running, creating a huge tmp table.

Kill that process (go into mysql commandline tool, do SHOW PROCESSLIST to get the pid, then do kill, in the tool, with that number.)

Then scratch your head as to why it is taking so much room. A likely case:

SELECT ... FROM a, b;

and failing to restrict how a and b should be JOINed. This is called a CROSS JOIN; it can be huge.

  • I found out what it was checking that, and it turned out to just be the insert query running while that was going up. I also eliminated that it was 'deleting' the old dropped tables by restarting it after deleting everything in /var/lib/mysql and reinstalling mysql again - No luck there either, but now I've managed to allocate way more space to that partition, which should alleviate the issue of it growing on that on a sheer insert operation from a particularly large dumpfile. – Dmitri DB Dec 29 '16 at 2:01
  • Thank the --ignore feature for me being able to continue without this taking forever to import – Dmitri DB Dec 29 '16 at 2:02
  • But how much of the data is missing? – Rick James Dec 29 '16 at 2:04
  • Absolutely none after taking the careful, conservative option and redoing it from the start, getting replication right this time around on top of it – Dmitri DB Jan 3 '17 at 17:56

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.