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

[calvinphanctt]

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 :

 

[vanessa]

[email protected] [~]# perror 24
OS error code  24:  Too many open files

http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

 

[calvinphanctt]

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

 

[SS-Maddy]

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.

 

[cPanelMichael]

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.

 

[calvinphanctt]

Hi SS-Maddy,

This is what I get for df -h & df -i , I'm not sure if /tmp is enough ?

[email protected] [/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

[email protected] [/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
[email protected] [/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

 

[SS-Maddy]

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

 

[edigest]

... 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:

vi intro -- the cheat sheet method

And here is a handy cheat-sheet:

http://www.atmos.albany.edu/daes/atmclasses/atm350/vi_cheat_sheet.pdf

 

[calvinphanctt]

Hello SS-Maddy,

This is what I got for lsattr but I don't know what the result means

[email protected] [~]# lsattr /etc/my.cnf
-------A-----e- /etc/my.cnf
[email protected] [~]#

@edigest: I use sudo nano and I can edit it

sudo nano /etc/security/limits.conf

I put in the limits.conf:

mysql soft nofile 24000
mysql hard nofile 32000

and I restart MySQL, but the "open files" still shows (-n) 1024 ? Something is wrong ?

[email protected] [~]# 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
[email protected] [~]#

So, I don't know what was wrong here, would you please help? Thank you very much !

Sincerely,
Calvin