search

EcomDev / EcomDev_UrlRewrite

Alternative implementation of Url Rewrite Index by EcomDev
72 stars 33 forks source link

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

Open jreinke opened 12 years ago

jreinke commented 12 years ago

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 commented 12 years ago

What is your DB version and Magento version?

jreinke commented 12 years ago

MySQL 5.5.25 Magento 1.6.2

aligentjim commented 12 years ago

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 commented 12 years ago

Only full error stack can help.

aligentjim commented 12 years ago

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 commented 12 years ago

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 commented 12 years ago

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 commented 12 years ago

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 commented 12 years ago

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 commented 12 years ago

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

jreinke commented 12 years ago

Ok I'll try this with 200k products.