The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Database Discussions' started by calvinphanctt, Jul 21, 2014.

  1. calvinphanctt

    calvinphanctt Active Member

    Joined:
    Mar 27, 2007
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    6
    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:

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


    Code:
    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 :
     
    #1 calvinphanctt, Jul 21, 2014
    Last edited: Jul 21, 2014
  2. vanessa

    vanessa Well-Known Member
    PartnerNOC

    Joined:
    Sep 26, 2006
    Messages:
    817
    Likes Received:
    22
    Trophy Points:
    18
    Location:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
  3. calvinphanctt

    calvinphanctt Active Member

    Joined:
    Mar 27, 2007
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  4. SS-Maddy

    SS-Maddy Well-Known Member

    Joined:
    Mar 28, 2009
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    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.
     
    #4 SS-Maddy, Jul 22, 2014
    Last edited: Jul 22, 2014
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  6. calvinphanctt

    calvinphanctt Active Member

    Joined:
    Mar 27, 2007
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    6
    Hi SS-Maddy,

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



    ===============================

    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
     
  7. SS-Maddy

    SS-Maddy Well-Known Member

    Joined:
    Mar 28, 2009
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    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
     
  8. edigest

    edigest Active Member

    Joined:
    Nov 24, 2010
    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    S. Pole
    cPanel Access Level:
    Root Administrator
  9. calvinphanctt

    calvinphanctt Active Member

    Joined:
    Mar 27, 2007
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    6
    Hello SS-Maddy,

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

    @edigest: I use sudo nano and I can edit it
    I put in the limits.conf:
    and I restart MySQL, but the "open files" still shows (-n) 1024 ? Something is wrong ?

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

    Sincerely,
    Calvin
     
Loading...

Share This Page