Deleted orders are not removed from grid

Question

When I delete an order, it is still listed in orders grid. When I attempt to open it, I get the message

This order no longer exists.

This behaviour was seemingly introduced by a Magento update: I downloaded the latest version of Magento and overrode the existing files.

Why do the orders still show up in the grid and how can I adjust this?

Answer 1

As others have said, you have orphaned records in your sales_flat_order_grid table that are not in your main order table, sales_flat_order. I'm noting two non-native behaviors on your instance of Magento.

1. You can delete orders. Default Magento does not let you delete order. I don't recommend allowing orders to be deleted in live environment.

2. Your MySQL constraint (CONSTRAINT... ON DELETE CASCADE) on the grid table has been removed. When an order record is deleted (via any method), The DB is supposed to automatically remove all associated records in sales_flat_order_* tables, which include the grid.

You need to do two things to restore integrity to your instance.

1. Re-establish the MySQL constraint. This will prevent orphan order records. This seems more off-topic, I'll offer a solution to your current problem below.

2. Remove the orphan order grid records. Run the following query to remove them.

Back up DB before running. Test it on a development environment first. I'm not responsible for your data loss.

DELETE 
FROM `sales_flat_order_grid` 
WHERE entity_id IN  (
    SELECT * FROM (
        SELECT g.entity_id
        FROM `sales_flat_order_grid` AS g
            INNER JOIN `sales_flat_order` AS o
                ON g.`entity_id` = o.`entity_id`
        WHERE g.entity_id IS NULL
    ) AS t
)

Answer 2

Mour, The order has been already deleted from system in table sales_flat_order.You need to check here. I guess that order is in sales_flat_order_grid database table but it doesn't exits on sale_flat_order table which is main table of sales order model[Mage::getModel('sales/order')]

see at class Mage_Adminhtml_Sales_OrderController where magento is check order is exit or not.

on function initorder

protected function _initOrder()
    {
        $id = $this->getRequest()->getParam('order_id');
        $order = Mage::getModel('sales/order')->load($id);
        if (!$order->getId()) {
            $this->_getSession()->addError($this->__('This order no longer exists.'));
            $this->_redirect('*/*/');
            $this->setFlag('', self::FLAG_NO_DISPATCH, true);
            return false;
        }
        Mage::register('sales_order', $order);
        Mage::register('current_order', $order);
        return $order;
    }

That means order does not delete properly.

You need to delete those order from sales_flat_order_grid.

Answer 3

The problem is

The order entry exists in the sales_flat_order_grid table but missing in sales_flat_order table

The solution is

Delete the entries from sales_flat_order_grid table which has no reference in sales_flat_order table

Run this SQL Query in your database:

DELETE FROM `sales_flat_order_grid`  
WHERE entity_id NOT IN (
   SELECT `entity_id` FROM `sales_flat_order`
);

Add a trigger on sales_flat_order table on event AFTER DELETE ON sales_flat_order if not exists,

DELETE FROM sales_flat_order_grid WHERE OLD.entity_id=sales_flat_order_grid.entity_id; 

Answer 4

A bit late to the party, But I had the same problem recently. One of my clients has iteratively upgraded from 1.4 up to 1.9 and I have found some orphaned records.

My approach was to check if there are any first.

SELECT COUNT(*) FROM `sales_flat_order_grid` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `sales_flat_order`);

Then, delete them:

DELETE FROM `sales_flat_order_grid` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `sales_flat_order`);

You can find all orphaned records like this:

// `-s` means no clutter around the result, `-N` means no header.

> mysql -s -N -umy_user -p

SELECT 
  CONCAT(
    'SELECT COUNT(*) FROM `', TABLE_NAME, '` ',
    'WHERE `', COLUMN_NAME, '` NOT IN (',
    'SELECT `', REFERENCED_COLUMN_NAME, '` FROM `', REFERENCED_TABLE_NAME, '`',
    ');'
  )
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE
  REFERENCED_TABLE_NAME IS NOT NULL
AND 
  CONSTRAINT_SCHEMA = '__PUT_DATABASE_NAME_HERE__'
  ; 

Then you can generate this beauty and finish the job.

SELECT 
  CONCAT(
    'DELETE FROM `', TABLE_NAME, '` ',
    'WHERE `', COLUMN_NAME, '` NOT IN (',
    'SELECT `', REFERENCED_COLUMN_NAME, '` FROM `', REFERENCED_TABLE_NAME, '`',
    ');'
  )
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE
  REFERENCED_TABLE_NAME IS NOT NULL
AND 
  CONSTRAINT_SCHEMA = '__PUT_DATABASE_NAME_HERE__'
  ;

Kudos to kozie for this suggestion; and dfelton for the orignal idea.

You could go one more step and create a batch file that iterates over the result using --execute="DELETE ...", but that's definitely out of scope of this answer.