Upgrade to mysql 5.5 - SQLSTATE[HY000]: General error: 23 Out of resources

cavamondo

Member
May 21, 2011
23
0
51
System: WHM Accellerated 2 v. 11.32.2
CPU: 24 x Intel(R) Xeon(R) CPU X5690 @ 3.47GHz
Ram 48GB
Cloudlinux

Ok so upgraded from mysql 4 to 5.5 and now i get the following error:
SQLSTATE[HY000]: General error: 23 Out of resources when opening file '/tmp/#sql_559e_0.MYD' (Errcode: 24)

Any clue where to look for a solution:
my.cnf :
-- [mysqld]
local-infile=0
innodb_file_per_table=1
default-storage-engine=MyISAM

---
Code:
[B]Heres some output from running a Magento site:[/B]
#0 /home/demo/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/demo/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/demo/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/demo/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('DESCRIBE `mage_...', Array)
#4 /home/demo/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('DESCRIBE `mage_...', Array)
#5 /home/demo/public_html/lib/Zend/Db/Adapter/Pdo/Mysql.php(156): Varien_Db_Adapter_Pdo_Mysql->query('DESCRIBE `mage_...')
#6 /home/demo/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(1566): Zend_Db_Adapter_Pdo_Mysql->describeTable('mage_log_visito...', NULL)
#7 /home/demo/public_html/app/code/core/Mage/Core/Model/Resource/Abstract.php(206): Varien_Db_Adapter_Pdo_Mysql->describeTable('mage_log_visito...')
#8 /home/demo/public_html/app/code/core/Mage/Log/Model/Resource/Visitor.php(152): Mage_Core_Model_Resource_Abstract->_prepareDataForTable(Object(Varien_Object), 'mage_log_visito...')
#9 /home/demo/public_html/app/code/core/Mage/Log/Model/Resource/Visitor.php(108): Mage_Log_Model_Resource_Visitor->_saveVisitorInfo(Object(Mage_Log_Model_Visitor))
#10 /home/demo/public_html/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(458): Mage_Log_Model_Resource_Visitor->_afterSave(Object(Mage_Log_Model_Visitor))
#11 /home/demo/public_html/app/code/core/Mage/Core/Model/Abstract.php(318): Mage_Core_Model_Resource_Db_Abstract->save(Object(Mage_Log_Model_Visitor))
#12 /home/demo/public_html/app/code/core/Mage/Log/Model/Visitor.php(167): Mage_Core_Model_Abstract->save()
#13 /home/demo/public_html/app/code/core/Mage/Core/Model/App.php(1303): Mage_Log_Model_Visitor->initByRequest(Object(Varien_Event_Observer))
#14 /home/demo/public_html/app/code/core/Mage/Core/Model/App.php(1284): Mage_Core_Model_App->_callObserverMethod(Object(Mage_Log_Model_Visitor), 'initByRequest', Object(Varien_Event_Observer))
#15 /home/demo/public_html/app/Mage.php(416): Mage_Core_Model_App->dispatchEvent('controller_acti...', Array)
#16 /home/demo/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(505): Mage::dispatchEvent('controller_acti...', Array)
#17 /home/demo/public_html/app/code/core/Mage/Core/Controller/Front/Action.php(59): Mage_Core_Controller_Varien_Action->preDispatch()
#18 /home/demo/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(409): Mage_Core_Controller_Front_Action->preDispatch()
#19 /home/demo/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#20 /home/demo/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#21 /home/demo/public_html/app/code/core/Mage/Core/Model/App.php(349): Mage_Core_Controller_Varien_Front->dispatch()
#22 /home/demo/public_html/app/Mage.php(640): Mage_Core_Model_App->run(Array)
#23 /home/demo/public_html/index.php(80): Mage::run('', 'store')
#24 {main}
- other apps such ass wordpress etc. keeps running just fine.
-- could it be the Magento installation needs DB / reindexing or something?

Any suggestions on solution?
 
Last edited:

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,606
33
238
somewhere over the rainbow
cPanel Access Level
Root Administrator
Re: Upgrade to mysql 5.5 - SQLSTATE[HY000]: General error: 23 Out of resour

This likely means that you've reached the open files limit for MySQL. Check to see how many open files:

Code:
sudo -u mysql bash
ulimit -a | grep open
You'll likely see something such as this:

Code:
bash-4.1$ ulimit -a | grep open
open files                      (-n) 2048
If that's the case, exit that user's login:

Code:
exit
Now, add the following to /etc/security/limits.conf:

Code:
echo -e "mysql soft nofile 10000\nmysql hard nofile 20000" >> /etc/security/limits.conf
Check it added the lines to the file:

Code:
cat /etc/security/limits.conf
At that point, log back in as the user and check open files limit again:

Code:
sudo -u mysql bash
ulimit -a | grep open
Exit again and now also increase the limit in /etc/my.cnf by editing the file to include this line under the [mysqld] section:

Code:
open_files_limit=20000
Restart MySQL and check it has updated:

Code:
/etc/init.d/mysql restart
mysqladmin var | grep open_files_limit
If that doesn't work, it might be disk space itself, although that error is typically a different one when /tmp has run out of space. You should check the size of /tmp when the error is occurring:

Code:
df -kh | grep /tmp