Magento: "Cannot reindex."

My "Category Products" index is having problems. It simply won't reindex. I was on 1.4 when the error started, now I'm on 1.5 and it's still there.

I have tried:

1. Repairing the database with the magento database repair tool
2. Clearing var, all cache, locks, sessions
3. Making sure all permissions are right.
4. Ripping my hair out.

Is there anyway I can investigate this issue further? Error logs don't make note of this problem, and I'm somewhat in the dark. Any help would be very, very appreciated.

Edit: After echoing out the error message I get: Report! SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1.

Edit 2: I've run a query log and have the following info. If you don't mind taking a look and seeing if a solution springs to mind I'd be very happy. It's slightly over my head...

[2011-06-14 12:33:26] SQL: SELECT DISTINCT `catalog_product_bundle_selection`.`parent_product_id` FROM `catalog_product_bundle_selection` WHERE (product_id IN('180'))
EXCEPTION 
exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1' in /my-domain.com/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /my-domain.com/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /my-domain.com/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /my-domain.com/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO cat...', Array)
#3 /my-domain.com/lib/Varien/Db/Adapter/Pdo/Mysql.php(335): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO cat...', Array)
#4 /my-domain.com/app/code/core/Mage/Index/Model/Mysql4/Abstract.php(159): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO cat...')
#5 /my-domain.com/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php(522): Mage_Index_Model_Mysql4_Abstract->insertFromSelect('SELECT?        ...', 'catalog_categor...', Array)
#6 /my-domain.com/app/code/core/Mage/Index/Model/Indexer/Abstract.php(125): Mage_Catalog_Model_Resource_Eav_Mysql4_Category_Indexer_Product->reindexAll()
#7 /my-domain.com/app/code/core/Mage/Index/Model/Process.php(139): Mage_Index_Model_Indexer_Abstract->reindexAll()
#8 /my-domain.com/app/code/core/Mage/Index/Model/Process.php(167): Mage_Index_Model_Process->reindexAll()
#9 /my-domain.com/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#10 /my-domain.com/app/code/core/Mage/Core/Controller开发者_如何学JAVA/Varien/Action.php(418): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#11 /my-domain.com/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#12 /my-domain.com/app/code/core/Mage/Core/Controller/Varien/Front.php(177): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#13 /my-domain.com/app/code/core/Mage/Core/Model/App.php(304): Mage_Core_Controller_Varien_Front->dispatch()
#14 /my-domain.com/app/Mage.php(596): Mage_Core_Model_App->run(Array)
#15 /my-domain.com/index.php(81): Mage::run('', 'store')
#16 {main}

Edit 3: The Query itself!

SQL:

INSERT INTO catalog_category_anc_products_index_idx 
SELECT STRAIGHT_JOIN DISTINCT ca.category_id, cp.product_id
FROM catalog_category_anc_categs_index_idx AS ca
    INNER JOIN catalog_category_entity AS ce
        ON ce.path LIKE ca.path OR ce.entity_id = ca.category_id
    INNER JOIN catalog_category_product AS cp
        ON cp.category_id = ce.entity_id
    INNER JOIN catalog_category_product_index_enbl_idx as pv
        ON pv.product_id = cp.product_id

---

This error -always- indicates that an exception was thrown somewhere in the index process.

To verify, check method 'reindexProcessAction' in the following file:

app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php

Please echo and die $e->getMessage() in the second catch block and report back ;)

edit1:

In regards to your exception, according to google, this -may- be due to PHP locale-specific floatval formatting... try to put:

setlocale(LC_ALL, 'en_EN'); 

at the top of your index.php file. Others suggest that you set System->Configuration->General->Locale to 'English'.

edit2:

This is a bug in 1.4.x.x, if you want a proper fix - for the love of god update your installation! :)

Here's a very nasty patch to core file app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Category/Indexer/Product.php

http://pastebin.com/fWYTsYuX

It is backported from 1.5. Hope it helps someone.

---

Magento, sometimes can be very weird. Try to turn off DEVELOPER_MODE (in .htaccess or index.php) and reindex. It helped for me.

---

Check that you have not got any products missing a SKU. You can do that from 'manage products' and then clicking on the SKU column to bring the blanks to the top. Delete them or assign SKU's and see if the reindexer works.

---

After struggling for hours I finally found a solution. In fact, It's a very simple solution:

• Backup your database
• Open phpMyAdmin and truncate (empty) the table catalog_product_flat_1

That's it. After that I was able to index all data and, until now, everything else works perfectly. This worked for me. I hope it works for you too.