Order status out of sync between sales_flat_order and sales_flat_order_grid

Question

I've a handful of orders (3 out of about 16,000) where the order status shown in the sales_flat_order table differsfrom that in thesales_flat_order_grid table`.

The three orders have the processing status in the sales_flat_order_grid table, but closed, on hold and complete in the sales_flat_order table.

I'd like to tidy this up. Is there an automatic method which will push the true state of the order contained in sales_flat_order into sales_flat_order_grid. Failing that is it safe, sensible to modify the database directly, adjusting the status of these orders in the sales_flat_order_grid table?

Answer 1

I found this solution by Igor Furseev at Atwix, The full code is:

<?php
/**
 * @author Atwix Team
 * @copyright Copyright (c) 2016 Atwix (https://www.atwix.com/)
 * @package Atwix_Shell
 */
require_once 'abstract.php';

class Atwix_Shell_Free_Order_Grid_Update extends Mage_Shell_Abstract
{
    public function run()
    {
        try {
            Mage::getModel('sales/order')->getResource()->updateGridRecords(
                Mage::getResourceModel('sales/order_collection')->getAllIds()
            );
            echo 'Done!' . PHP_EOL;
        } catch (Exception $e) {
            echo $e->getMessage() . PHP_EOL;
        }
    }
}

$shell = new Atwix_Shell_Free_Order_Grid_Update();
$shell->run();

which you save to a file in the shell directory of your Magento installation and then execute with php from the command line with php shell/orders_grid_update.php.

I've taken a bit of time to analyse this code, to try and reassure myself that I'm not just implementing a random solution from the internet, which may or may not be right.

The above code gets a Magento collection object for all orders in the system, uses it to generate a list of all the entity IDs (not the same as order ids), which it then passes to Mage_Sales_Model_Resource_Order_Abstract::updateGridRecords.

The Mage_Sales_Model_Resource_Order_Abstract::updateGridRecords method is the method Magento uses itself to update the grid records any time a sales/order object is saved. The save method is defined higher up the models hierarchy in Mage_Core_Model_Abstract::save, where the method generates a call to Mage_Sales_Model_Abstract::afterComitCallback, which in turn calls the Mage_Sales_Model_Resource_Order_Abstract::updateGridRecords method.

Looking at Mage_Sales_Model_Resource_Order_Abstract::updateGridRecords, we can see that it builds an insert select query, which will insert (or update) data in sales_flat_order_grid based on data from from a select query which joins together sales_flat_order and sales_flat_order_address (actually joins twice onto sales_flat_order_address to get both billing and shipping addresses associated with the order).

I tried the above on a copy of a store and then diffed the databases before and afterwards. The only tables which had changed was sales_flat_order_grid as I'd expected. It had modified this table to sort out the orders which were in an inconsistent state.

The only other change the database diff reveated were some double spaces between the firstname and surname on both the billing and shipping addresses. I tracked this down to a code change in Mage_Sales_Model_Resource_Order::_initVirtualGridColumns sometime between version 1.6 and 1.9. The earlier version just concatenated a customers first and surname, but the later version concatenated a customers first, middle and surname but mistakenly appeneded a space to both first and middle names during the concatenation, even if the middle name was not present, hence the double space.

I'm not to concerned if the above code messes up the sales_flat_order_grid table as this answer says that the grid table is just for presentation to the admin, and is not the source of truth about an order.

Answer 2

Try reindexing all.

The command line gives you access to some reindex processes that are not available in the admin. From the Magento root, you can see a list of them by running php shell/indexer.php info and run them all with php shell/indexer.php reindexall.

There is no problem with running reindexall in production, but if you have not done it before, make sure you take a DB backup first. Running in production is generally considered safe, unless you have an underlying issue. An example to check for in you development DB, after reindexing is that all of your URL rewrites still work properly.