Out of resources when opening file '/tmp/#sql_26b7_0.MYI' (Errcode: 24)

calvinphanctt

July 21, 2014 19:16

Hi everyone, How are you doing? I am running VBulletin forum on my dedicated server (rent new recently - new set up, my server is: Intel Xeon E3-1270 16GB DDR3, 2 x 3TB HDD), but recently I keep got VBulletin Database error message with:

 Out of resources when opening file '/tmp/#sql_26b7_0.MYI' (Errcode: 24)

or:

Out of resources when opening file './abcdef_forum/vb_post.MYD' (Errcode: 24)

or: 

MySQL Error  : Out of resources when opening file '/tmp/#sql_26b7_0.MYI' (Errcode: 24)
Error Number  : 23

I have access to my dedicated server, please show me in details step by step how to fix this problem ! Thank you very much in advance! Regards, Calvin ============================================================== Here is some example of the full error:

Database error in vBulletin 4.2.2:

Invalid SQL:
SELECT SQL_CALC_FOUND_ROWS node.nodeid AS itemid  ,(node.nodeleft = 1) AS isroot, node.nodeid, node.contenttypeid, node.contentid, node.url, node.parentnode, node.styleid, node.userid,
                        node.layoutid, node.publishdate, node.setpublish, node.issection, parent.permissionsfrom as parentpermissions,
                        node.showrating,
                        node.permissionsfrom, node.publicpreview, node.shownav, node.hidden, node.nosearch, info.description, info.title, info.viewcount, info.creationdate, info.workflowdate,
                    info.workflowstatus, info.workflowcheckedout, info.workflowlevelid, info.associatedthreadid,
                    user.username, sectionorder.displayorder
                   
                   
                FROM vb_cms_node AS node
                INNER JOIN vb_cms_nodeinfo AS info ON info.nodeid = node.nodeid
                LEFT JOIN vb_user AS user ON user.userid = node.userid
                LEFT JOIN vb_cms_sectionorder AS sectionorder ON sectionorder.sectionid = node.parentnode
                AND sectionorder.nodeid = node.nodeid
                LEFT JOIN vb_cms_node AS parent ON parent.nodeid = node.parentnode    
               
                WHERE node.new != 1  AND node.nodeid IN (1,45,5,11,2,3)  AND ( (node.permissionsfrom IN (-1)) OR (node.userid =198306)  OR ( node.permissionsfrom in (1) AND (node.parentnode IN (1) OR node.nodeid = 1) AND
                node.setpublish > 0 AND node.publishdate < 1405971769 ))AND node.new != 1
               
                  ORDER BY CASE WHEN sectionorder.displayorder > 0 THEN sectionorder.displayorder ELSE 9999999 END ASC,
                    node.publishdate DESC;

MySQL Error  : Out of resources when opening file '/tmp/#sql_26b7_0.MYI' (Errcode: 24)
Error Number  : 23
Request Date  : Monday, July 21st 2014 @ 07:42:49 PM
Error Date    : Monday, July 21st 2014 @ 07:42:49 PM
Script        : domain.com/forum/content.php?styleid=27
Referrer      : domain.com/index_frame.html
IP Address    : 42.112.xxx.xxx
Username      : username
Classname    : vB_Database
MySQL Version :

Comments

8 comments

vanessa

July 22, 2014 01:31
root@ocelot [~]# perror 24 OS error code 24: Too many open files
0
calvinphanctt

July 22, 2014 05:01
Hi Vanessa, Thank you for the link, I follow it but I can't do it, especially the commands: vi /etc/my.cnf or vi /etc/security/limits.conf It doesn't work ! Regards, Calvin
0
SS-Maddy

July 22, 2014 07:11
Hello Calvin, Please ensure that the /tmp folder has sufficient space availability. The /tmp can get filled if custom op code cahe such as eAccelerator is configured to use /tmp as data directory. Also check whether your mysql server is optimized with reasonable values for open_files_limit in /etc/my.cnf Checking the disk availability for space and inode using df -h df -i Should be more helpful.
0
cPanelMichael

July 22, 2014 13:16
[quote="calvinphanctt, post: 1691962">I follow it but I can't do it, especially the commands: vi /etc/my.cnf or vi /etc/security/limits.conf It doesn't work !
Hello :) Are you logged in via SSH as the "root" user? If so, could you elaborate on how those commands are not working? Thank you.
0
calvinphanctt

July 23, 2014 00:34
Hi SS-Maddy, This is what I get for df -h & df -i, I'm not sure if /tmp is enough ? [QUOTE]root@server1 [/home2/server/public_html/db]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 2.7T 169G 2.4T 7% / tmpfs 7.8G 0 7.8G 0% /dev/shm /dev/sda1 1008M 89M 869M 10% /boot /dev/sdb1 2.7T 146G 2.5T 6% /home2 /usr/tmpDSK 4.0G 137M 3.7G 4% /tmp root@server1 [/home2/server/public_html/db]# df -i Filesystem Inodes IUsed IFree IUse% Mounted on /dev/sda3 182951936 330097 182621839 1% / tmpfs 2037991 1 2037990 1% /dev/shm /dev/sda1 65536 46 65490 1% /boot /dev/sdb1 183148544 2143 183146401 1% /home2 /usr/tmpDSK 262144 742 261402 1% /tmp root@server1 [/home2/server/public_html/db]#
=============================== Hi cPanelMichael, Yes, I log in as root, but somehow the "vi" show the file my.cnf but won't let me edit ? Could you please help me what to do to resolve this ? I don't want to restart MySQL from WHM everytime I got "VBulletin Database error". Thank you very much ! Sincerely, Calvin
0
SS-Maddy

July 23, 2014 11:06
Hello Calvin, The disk usage appears normal. If you are unable to edit the /etc/my.cnf file as root, check whther attributes are set for the file. You can check it using the command lsattr /etc/my.cnf
0
edigest

July 23, 2014 13:45
[quote="calvinphanctt, post: 1692891">... somehow the "vi" show the file my.cnf but won't let me edit ...
vi is an odd editor. If you have pico or nano available, you may find them easier to use. Otherwise, you can learn how to use vi here: [url=http://www.ibm.com/developerworks/linux/tutorials/l-vi/index.html]vi intro -- the cheat sheet method And here is a handy cheat-sheet:
0
calvinphanctt

July 24, 2014 02:38
Hello SS-Maddy, This is what I got for lsattr but I don't know what the result means [QUOTE]root@server1 [~]# lsattr /etc/my.cnf -------A-----e- /etc/my.cnf root@server1 [~]#
@edigest: I use sudo nano and I can edit it [QUOTE]sudo nano /etc/security/limits.conf
I put in the limits.conf: [QUOTE]mysql soft nofile 24000 mysql hard nofile 32000
and I restart MySQL, but the "open files" still shows (-n) 1024 ? Something is wrong ? [QUOTE]root@server1 [~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 127214 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 127214 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited root@server1 [~]#
So, I don't know what was wrong here, would you please help? Thank you very much ! Sincerely, Calvin
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?