MySQL Error : Can’t create/write to file ‘/tmp/#sql_839_1.MYI’ (Errcode: 28) Error Number : 1

Recently one of our users told us they were not able to update their Database due to the following error:

Database error in vBulletin 4.2.2:

Invalid SQL:
SELECT node.nodeid AS itemid, parent.nodeid, parent.url, parent.styleid,
parent.layoutid, parent.publishdate,
parent.setpublish, parent.hidden, info.title, info.description
FROM cms_node AS node
INNER JOIN cms_node AS parent ON (node.nodeleft >= parent.nodeleft AND
node.nodeleft <= parent.noderight)
INNER JOIN cms_nodeinfo AS info ON info.nodeid = parent.nodeid
LEFT JOIN cms_sectionorder AS ord ON ord.nodeid = node.nodeid AND
ord.sectionid = node.parentnode
WHERE node.nodeid IN (1)
AND parent.nodeid != node.nodeid
ORDER BY parent.nodeleft, ord.displayorder;

MySQL Error : Can't create/write to file '/tmp/#sql_839_1.MYI' (Errcode:
Error Number : 1

 This error means that The MySQL  engine on your server has run out of (allowed)  space for storing its own temporary files. 

To resolve the error first thing we can try to increase the size of tmp/ partition,If you are not able to enhance your /tmp/ partition you can do something in MySQL settings, modify the my.cnf file and look for this line tmpdir = /tmp/

Modify it for whatever you want, just be sure to have space and allocate write permissions for the MySQL database user in the new directory.

Second it might be possible that MySQL logs are taking all the space/memory, if is it so we can remove the old log files through following command:

du -sh /var/log/mysql

 Once done restart the server and error will be gone.



Leave a Reply

Register to Get FREE Offers

Why Pay for Software when you can GET it for FREE