Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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

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

  1. calvinphanctt

    calvinphanctt Active Member

    Joined:
    Mar 27, 2007
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    156
    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:
    830
    Likes Received:
    26
    Trophy Points:
    178
    Location:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. calvinphanctt

    calvinphanctt Active Member

    Joined:
    Mar 27, 2007
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    156
    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:
    104
    Likes Received:
    6
    Trophy Points:
    68
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #4 SS-Maddy, Jul 22, 2014
    Last edited: Jul 22, 2014
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,367
    Likes Received:
    1,857
    Trophy Points:
    363
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. calvinphanctt

    calvinphanctt Active Member

    Joined:
    Mar 27, 2007
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    156
    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:
    104
    Likes Received:
    6
    Trophy Points:
    68
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. edigest

    edigest Active Member

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

    calvinphanctt Active Member

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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice