SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_603b_0.MYI'; try to repair it

[jreinke]

Hi!

I have the following issue when running command: php shell/indexer.php --reindex catalog_url

SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_603b_0.MYI'; try to repair it
SQL QUERY: UPDATE ecomdev_urlrewrite_rewrite AS rewrite
INNER JOIN ecomdev_urlrewrite_product_request_path AS request_path ON request_path.store_id = rewrite.store_id AND request_path.id_path = rewrite.id_path
SET rewrite.duplicate_key = request_path.request_path, rewrite.duplicate_index = IF(rewrite.duplicate_index IS NOT NULL AND SUBSTRING_INDEX(rewrite.duplicate_key, '/', -1) = SUBSTRING_INDEX(request_path.request_path, '/', -1), rewrite.duplicate_index, IF(request_path.request_path REGEXP '[0-9]$', 0, NULL)), rewrite.target_path = IF(request_path.category_id IS NULL, REPLACE('catalog/product/view/id/#id','#id',request_path.product_id), REPLACE(REPLACE('catalog/product/view/id/#id/category/#cat','#id',request_path.product_id),'#cat',request_path.category_id)), rewrite.updated = 1, request_path.updated = 0
WHERE (request_path.updated = 1)

Error comes from line 165: return $this->_adapter->query(implode("\n", $parts), $this->getBind());
of file: app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Select.php

Here is the stack trace:
Catalog URL Rewrites index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_603b_0.MYI'; try to repair it' in /home/www/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/www/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/www/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/www/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE ecomdev...', Array) #5 /home/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE ecomdev...', Array)
#6 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Select.php(161): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `ecomdev...', Array)
#7 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(1697): EcomDev_UrlRewrite_Model_Mysql4_Select->crossUpdateFromSelectImproved()
#8 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(2163): EcomDev_UrlRewrite_Model_Mysql4_Indexer->_importFromProductRequestPath()
#9 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Indexer.php(97): EcomDev_UrlRewrite_Model_Mysql4_Indexer->reindexAll()
#10 /home/www/app/code/core/Mage/Index/Model/Process.php(207): EcomDev_UrlRewrite_Model_Indexer->reindexAll()
#11 /home/www/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#12 /home/www/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#13 /home/www/shell/indexer.php(198): Mage_Shell_Compiler->run()
#14 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_603b_0.MYI'; try to repair it' in /home/www/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /home/www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/www/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/www/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE ecomdev...', Array) #4 /home/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE ecomdev...', Array)
#5 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Select.php(161): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `ecomdev...', Array)
#6 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(1697): EcomDev_UrlRewrite_Model_Mysql4_Select->crossUpdateFromSelectImproved()
#7 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(2163): EcomDev_UrlRewrite_Model_Mysql4_Indexer->_importFromProductRequestPath()
#8 /home/www/app/code/community/EcomDev/UrlRewrite/Model/Indexer.php(97): EcomDev_UrlRewrite_Model_Mysql4_Indexer->reindexAll()
#9 /home/www/app/code/core/Mage/Index/Model/Process.php(207): EcomDev_UrlRewrite_Model_Indexer->reindexAll()
#10 /home/www/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#11 /home/www/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#12 /home/www/shell/indexer.php(198): Mage_Shell_Compiler->run()
#13 {main}

Have you got an idea of how to fix this?

Thank you!

NB: I have already tried to delete tables and reinstall module without success.

[IvanChepurnyi]

What is your DB version and Magento version?

[jreinke]

MySQL 5.5.25
Magento 1.6.2

[aligentjim]

We have the same error:
MySQL 5.1.63
Magento 1.6.2.0

Extension version 0.2.1, our error comes from line 162 of EcomDev/UrlRewrite/Model/Mysql4/Select.php

I can post a full call stack if that's helpful.

[IvanChepurnyi]

Only full error stack can help.

[aligentjim]

We are running the reindex from the command line using the command "php shell/indexer.php --reindex catalog_url". PHP version is 5.3.10. The full stack trace is as follows:

Catalog URL Rewrites index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_4c51_0.MYI'; try to repair it' in /home/cheap/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/cheap/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/cheap/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/cheap/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/cheap/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/cheap/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `ecomdev...', Array)
#5 /home/cheap/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `ecomdev...', Array)
#6 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Select.php(161): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `ecomdev...', Array)
#7 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(1697): EcomDev_UrlRewrite_Model_Mysql4_Select->crossUpdateFromSelectImproved()
#8 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(2163): EcomDev_UrlRewrite_Model_Mysql4_Indexer->_importFromProductRequestPath()
#9 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Indexer.php(96): EcomDev_UrlRewrite_Model_Mysql4_Indexer->reindexAll()
#10 /home/cheap/public_html/app/code/core/Mage/Index/Model/Process.php(207): EcomDev_UrlRewrite_Model_Indexer->reindexAll()
#11 /home/cheap/public_html/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#12 /home/cheap/public_html/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#13 /home/cheap/public_html/shell/indexer.php(198): Mage_Shell_Compiler->run()
#14 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_4c51_0.MYI'; try to repair it UPDATE `ecomdev_urlrewrite_rewrite` AS `rewrite`
 INNER JOIN `ecomdev_urlrewrite_product_request_path` AS `request_path` ON request_path.store_id = rewrite.store_id AND request_path.id_path = rewrite.id_path
SET `rewrite`.`duplicate_key` = `request_path`.`request_path`, `rewrite`.`duplicate_index` = IF(rewrite.duplicate_index IS NOT NULL  AND SUBSTRING_INDEX(rewrite.duplicate_key, '/', -1) = SUBSTRING_INDEX(request_path.request_path, '/', -1),  rewrite.duplicate_index,  IF(request_path.request_path REGEXP '[0-9]$', 0, NULL)), `rewrite`.`target_path` = IF(request_path.category_id IS NULL, REPLACE('catalog/product/view/id/#id','#id',request_path.product_id), REPLACE(REPLACE('catalog/product/view/id/#id/category/#cat','#id',request_path.product_id),'#cat',request_path.category_id)), `rewrite`.`updated` = 1, `request_path`.`updated` = 0
 WHERE (request_path.updated = 1)' in /home/cheap/public_html/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /home/cheap/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/cheap/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/cheap/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/cheap/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `ecomdev...', Array)
#4 /home/cheap/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `ecomdev...', Array)
#5 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Select.php(161): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `ecomdev...', Array)
#6 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(1697): EcomDev_UrlRewrite_Model_Mysql4_Select->crossUpdateFromSelectImproved()
#7 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Mysql4/Indexer.php(2163): EcomDev_UrlRewrite_Model_Mysql4_Indexer->_importFromProductRequestPath()
#8 /home/cheap/public_html/app/code/community/EcomDev/UrlRewrite/Model/Indexer.php(96): EcomDev_UrlRewrite_Model_Mysql4_Indexer->reindexAll()
#9 /home/cheap/public_html/app/code/core/Mage/Index/Model/Process.php(207): EcomDev_UrlRewrite_Model_Indexer->reindexAll()
#10 /home/cheap/public_html/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#11 /home/cheap/public_html/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#12 /home/cheap/public_html/shell/indexer.php(198): Mage_Shell_Compiler->run()
#13 {main}

[aligentjim]

This system has a very large number of products, and a small /tmp folder (100Mb). We're currently investigating the possibility that the server may be running out of /tmp space during the reindex.

[IvanChepurnyi]

Can you please post this explain query result here:

EXPLAIN 
    SELECT rewrite.id_path 
        FROM ecomdev_urlrewrite_rewrite AS rewrite
        INNER JOIN ecomdev_urlrewrite_product_request_path AS request_path 
                ON request_path.store_id = rewrite.store_id AND request_path.id_path = rewrite.id_path
        WHERE request_path.updated = 1

This one might help in resolving the issue, maybe we can see some information why on your server a disc tmp table was used.

[aligentjim]

The output of the explain statement is as follows;

+----+-------------+--------------+--------+---------------------+-------------+---------+------------------------------------------------------------------+------+-------------+
| id | select_type | table        | type   | possible_keys       | key         | key_len | ref                                                              | rows | Extra       |
+----+-------------+--------------+--------+---------------------+-------------+---------+------------------------------------------------------------------+------+-------------+
|  1 | SIMPLE      | request_path | ref    | PRIMARY,IDX_UPDATED | IDX_UPDATED | 2       | const                                                            |    1 | Using index |
|  1 | SIMPLE      | rewrite      | eq_ref | PRIMARY             | PRIMARY     | 100     | cheap_mage.request_path.store_id,cheap_mage.request_path.id_path |    1 | Using index |
+----+-------------+--------------+--------+---------------------+-------------+---------+------------------------------------------------------------------+------+-------------+

The ecomdev_urlrewrite_rewrite table contains 190348 rows, and ecomdev_urlrewrite_product_request_path contains 189190 rows.

[aligentjim]

We have confirmed that in our case this issue was caused by insufficient disk space available in the /tmp folder. Previously the server had /tmp mounted as a 100Mb tmpfs file system. When this was increased to 2Gb the reindex works correctly. The database contains approx 47000 products.

[IvanChepurnyi]

Hey, @jreinke, I think you have the same kind of issue as Aligent guys have. Could you please try the same?

[jreinke]

Ok I'll try this with 200k products.