Gsyfuy

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.

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="MagentoCatalogInventoryModelIndexerStockActionFull" type="VendorModuleIndexerCatalogInventoryFull" />

Vendor/Module/Indexer/CatalogInventoryFull.php

<?php

namespace VendorModuleIndexer;



use MagentoCatalogModelResourceModelIndexerActiveTableSwitcher;

use MagentoFrameworkAppResourceConnection;

use MagentoCatalogInventoryModelResourceModelIndexerStockFactory;

use MagentoCatalogModelProductType as ProductType;

use MagentoFrameworkIndexerCacheContext;

use MagentoFrameworkEventManagerInterface as EventManager;

use MagentoFrameworkEntityManagerMetadataPool;

use MagentoFrameworkIndexerBatchSizeManagementInterface;

use MagentoFrameworkIndexerBatchProviderInterface;

use MagentoFrameworkAppObjectManager;

use MagentoFrameworkExceptionLocalizedException;

use MagentoCatalogInventoryModelIndexerStockAbstractAction;

use MagentoCatalogInventoryModelResourceModelIndexerStockStockInterface;



class CatalogInventoryFull extends MagentoCatalogInventoryModelIndexerStockActionFull {

    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 $pidn";

    $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$sqln";

        $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$sqln";

        $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$sqln";

        $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] r2n";

        echo "n$sqln";

        $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_idxn";



$sql = "SET FOREIGN_KEY_CHECKS=1;";

$connection->query($sql);

echo "ncataloginventory_stock_status $missing missing $fixed fixed $unchanged unchangedn";

echo "ncataloginventory_stock_item $missing2 missing $fixed2 fixed $unchanged2 unchangedn";

}

$sql = "update cataloginventory_stock_status set stock_status=1 where qty > 0";

echo "$sqln";

$connection->query($sql);

$sql = "update cataloginventory_stock_status_idx set stock_status=1 where qty > 0";

echo "$sqln";

$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.