Recently one of our users told us they were not able to update their Database due to the following error:
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: 28) 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.