If you run or develop a Magento store you may have faced the issue of seeing Product Flat Data getting Stuck in Processing or completely fail to continue by getting the error message “Some problem with reindexing process” if you run the indexer through Magento Backend or face a long error message starting with “Product Flat Data index process unknown error: exception ‘PDOException’…” if you run the shell indexer . This may look like a big error but the solution for this error is very simple!

There are many reasons this error can occur and I wont go into details, instead I will give you a small script to fix the issue as I am sure you are probably having this problem right now and need it fixed as soon as possible.

How does this script work:

The script has two main sections

1 – Clearing the database – The script first grabs a list of all your magento stores(MultiStore Support) within the magento installation and then goes through each of their catalog_product_flat table and cleans them using truncate command

getStores();
//Setup database connection
$db = Mage::getSingleton('core/resource')->getConnection('core_read');
//Go through each store and Clear it's database
foreach ($allStores as $_eachStoreId => $val) {
    $_storeId = Mage::app()->getStore($_eachStoreId)->getId();
    $db->query("TRUNCATE catalog_product_flat_" . $_storeId);
}
?>

2 – Clear var/locks Directory – In this part the script simply deletes all of the content within your var/locks directory in order to let Magento allow the reindexing to happen again.

<?php
if (file_exists("var/locks")) {
    echo "<p>Clearing var/locks</p>";
    cleandir("var/locks");
}
?>

How to run this script:

1. Download the Product Flat Data Indexer script from the link below
2. Unzip flatindexerfix.php to the root direcotry where Magento is installed.
3. Browse to http://yourdomain.com/magento/flatindexerfix.php
4. If the process finish without any problems you will see a message saying Cleaning process done.
5. That’s all, enjoy indexing.