2

What could cause cataloginventory_stock_status to have missing entries I have to fix manually? It makes the product appear out of stock even though it isn't. I've tried replicating by flushing, cleaning, and re-indexing and running cron but none of these cause the missing entries. The products will exist for weeks, even months and just randomly have this issue.

1

So far, unfortunately the only true answer I have found is to write my own cataloginventory_stock indexer.

Note Vendor/Module is for whatever your custom module is, plugin your own names.

In your Vendor/Module/etc/di.xml file

<preference for="Magento\CatalogInventory\Model\Indexer\Stock\Action\Full" type="Vendor\Module\Indexer\CatalogInventoryFull" />

Vendor/Module/Indexer/CatalogInventoryFull.php

<?php
namespace Vendor\Module\Indexer;

use Magento\Catalog\Model\ResourceModel\Indexer\ActiveTableSwitcher;
use Magento\Framework\App\ResourceConnection;
use Magento\CatalogInventory\Model\ResourceModel\Indexer\StockFactory;
use Magento\Catalog\Model\Product\Type as ProductType;
use Magento\Framework\Indexer\CacheContext;
use Magento\Framework\Event\ManagerInterface as EventManager;
use Magento\Framework\EntityManager\MetadataPool;
use Magento\Framework\Indexer\BatchSizeManagementInterface;
use Magento\Framework\Indexer\BatchProviderInterface;
use Magento\Framework\App\ObjectManager;
use Magento\Framework\Exception\LocalizedException;
use Magento\CatalogInventory\Model\Indexer\Stock\AbstractAction;
use Magento\CatalogInventory\Model\ResourceModel\Indexer\Stock\StockInterface;

class CatalogInventoryFull extends \Magento\CatalogInventory\Model\Indexer\Stock\Action\Full {
    public function execute($ids = null){
        //custom indexer code goes here
        return;
    }
}

As for the custom indexer code it will vary for you. Basically however you decide to determine stock and stock status is fine. Here's my script but it depends on there being the amasty multiinventory warehouse module.

<?php
require_once("bootstrapm2.php");

//now use the total field to make sure the cataloginventory_stock_status and cataloginventory_stock_status_idx tables are correct
$sql = "select product_id,available_qty from amasty_multiinventory_warehouse_item where warehouse_id=1";
$rows = $connection->fetchAll($sql);
$count = 0;
$missing = $fixed = $unchanged = 0;
$missing2 = $fixed2 = $unchanged2 = 0;
$sql = "SET FOREIGN_KEY_CHECKS=0;";
$connection->query($sql);
foreach($rows as $k=>$v){
    echo "working on $pid\n";
    $pid = $v['product_id'];
    $qty = (int)$v['available_qty'];
    $sql = "select * from cataloginventory_stock_status where product_id=$pid";
    $r = $connection->fetchAll($sql);
    if(!empty($r)) $r = $r[0];

    $status = 0;
    if($qty > 0) $status = 1;
    if(empty($r)){
        $missing++;
        $sql = "insert into cataloginventory_stock_status values($pid,0,1,$qty,$status)";
        echo "\n$sql\n";
        $connection->query($sql);
    }
    else if($r['qty'] !== $qty || ($qty > 0 && $r['stock_status'] == 0) || ($qty == 0 && $r['stock_status']==1)){
        $fixed++;
        $sql = "replace into cataloginventory_stock_status values($pid,0,1,$qty,$status)";
        echo "\n$sql\n";
        $connection->query($sql);
    }
    else $unchanged++;

    $sql = "select * from cataloginventory_stock_item where product_id=$pid";
    $r2 = $connection->fetchAll($sql);
    if(!empty($r2)) $r2 = $r2[0];

    if(empty($r2)){
        $missing2++;
        $sql = "insert into cataloginventory_stock_item values(null,$pid,1,$qty,0,1,0,0,1,1,1,10000,1,1,null,1,1,1,1,0,1,1,1,0,0,0)";
        echo "\n$sql\n";
        $connection->query($sql);
    }
    else if($qty !== $r2['qty']){
        $fixed2++;
        $sql = "update cataloginventory_stock_item set qty=$qty where product_id=$pid";
        echo "\n$qty != $r2[qty] r2\n";
        echo "\n$sql\n";
        $connection->query($sql);
    }
    else $unchanged2++;

    $count++;
}

$sql = "truncate cataloginventory_stock_status_idx";
$connection->query($sql);
$sql = "insert into cataloginventory_stock_status_idx select * from cataloginventory_stock_status";
$connection->query($sql);
echo "\nrebuilt cataloginventory_stock_status_idx\n";

$sql = "SET FOREIGN_KEY_CHECKS=1;";
$connection->query($sql);
echo "\ncataloginventory_stock_status $missing missing $fixed fixed $unchanged unchanged\n";
echo "\ncataloginventory_stock_item $missing2 missing $fixed2 fixed $unchanged2 unchanged\n";
}
$sql = "update cataloginventory_stock_status set stock_status=1 where qty > 0";
echo "$sql\n";
$connection->query($sql);
$sql = "update cataloginventory_stock_status_idx set stock_status=1 where qty > 0";
echo "$sql\n";
$connection->query($sql);

This is actually called by my script above via exec, because this is also a standalone cron script if I need it to be. I haven't had issues with the stock status since this change and inventory levels are actually correct now. The bootstrapm2 script it calls is really long, but anything that can just setup the dependencies and bootstrap m2 is fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.