I have found a bug in Magento, but I'am not sure this is bug.
app/code/core/Enterprise/Catalog/Model/Index/Action/Product/Flat/Refresh/Changelog.php
public function execute()
{
if (!$this->_isFlatIndexerEnabled()) {
return $this;
}
$this->_validate();
$changedIds = $this->_selectChangedIds();
if (!empty($changedIds)) {
$stores = Mage::app()->getStores();
foreach ($stores as $store) {
$idsBatches = array_chunk($changedIds, Mage::helper('enterprise_index')->getBatchSize());
foreach ($idsBatches as $ids) {
$this->_reindex($store->getId(), $ids);
}
}
$this->_setChangelogValid();
Mage::dispatchEvent('catalog_product_flat_partial_reindex', array('product_ids' => $changedIds));
}
return $this;
}
So we call _reindex method for all stores by 500 products.
in _reindex method we have this code:
if (!self::$_calls) {
$temporaryEavAttributes = $eavAttributes;
//add status global value to the base table
/* @var $status Mage_Eav_Model_Entity_Attribute */
$status = $this->_productHelper->getAttribute('status');
$temporaryEavAttributes[$status->getBackendTable()]['status'] = $status;
//Create list of temporary tables based on available attributes attributes
foreach ($temporaryEavAttributes as $tableName => $columns) {
$this->_createTemporaryTable($this->_getTemporaryTableName($tableName), $columns);
}
//Fill "base" table which contains all available products
$this->_fillTemporaryEntityTable($entityTableName, $entityTableColumns, $changedIds);
//Add primary key to "base" temporary table for increase speed of joins in future
$this->_addPrimaryKeyToTable($this->_getTemporaryTableName($entityTableName));
unset($temporaryEavAttributes[$entityTableName]);
foreach ($temporaryEavAttributes as $tableName => $columns) {
$temporaryTableName = $this->_getTemporaryTableName($tableName);
//Add primary key to temporary table for increase speed of joins in future
$this->_addPrimaryKeyToTable($temporaryTableName);
//Create temporary table for composite attributes
if (isset($this->_valueTables[$temporaryTableName . $this->_valueFieldSuffix])) {
$this->_addPrimaryKeyToTable($temporaryTableName . $this->_valueFieldSuffix);
}
//Fill temporary tables with attributes grouped by it type
$this->_fillTemporaryTable($tableName, $columns, $changedIds);
}
}
Here we create temporary table "catalog_product_entity_tmp_indexer" (and other entity type tables) if this is the first call of reindex. And we use this tmp table for update rest products (over 500)
But we have in catalog_product_entity_tmp_indexer maximum 500 products.
And partial reindex for catalog_product_flat dosn't work for changed products over 500.
Best Answer
As there isn't any official patch then quick fix is comment out if(!self::$_calls) part