This morning I had to troubleshoot a third party module (Amasty Out of Stock Notifications) that was not sending emails out properly. After wrongfully thinking the module was junk (we used it on M1 and it worked great, but had doubts about the m2 version), I jumped full blown dev into coding / debugging mode.
This lead me to this line:
if ($allProducts && $product->isSalable()) {
So I of course printed out the return values of these. $allProducts returned a collection but isSalable returned nothing. Not a zero and not a 1.
Looking into isSalable is just a very big fun mess that does a whole lot of things that I never really ever wanted to look under the hood about. Luckily, I realized it used a bunch of stuff form the indexer….
That’s when I headed over to my indexer status page in the admin panel and saw they were processing. Then I noticed they have been processing always. Every minute the cron fires off, they pick back up. That’s not right….
So I decided to run them via CLI
[staging@dev htdocs]$ php bin/magento indexer:reindex
Table status for design_config_dummy_cl is incorrect. Can`t fetch version id.
Table status for customer_dummy_cl is incorrect. Can`t fetch version id.
Table status for catalog_product_flat_cl is incorrect. Can`t fetch version id.
Table status for catalog_category_flat_cl is incorrect. Can`t fetch version id.
Table status for catalog_category_product_cl is incorrect. Can`t fetch version id.
Table status for catalog_product_category_cl is incorrect. Can`t fetch version id.
Table status for catalogrule_rule_cl is incorrect. Can`t fetch version id.
Table status for catalog_product_attribute_cl is incorrect. Can`t fetch version id.
Table status for cataloginventory_stock_cl is incorrect. Can`t fetch version id.
Table status for inventory_cl is incorrect. Can`t fetch version id.
Table status for catalogrule_product_cl is incorrect. Can`t fetch version id.
Table status for catalog_product_price_cl is incorrect. Can`t fetch version id.
Google Product Removal Feed index is locked by another reindex process. Skipping.
Google Product Feed index is locked by another reindex process. Skipping.
Table status for catalogsearch_fulltext_cl is incorrect. Can`t fetch version id.
Table status for amasty_feed_entity_cl is incorrect. Can`t fetch version id.
Table status for amasty_feed_product_cl is incorrect. Can`t fetch version id.
What the heck! Luckily, thanks to some stack overflow articles I found out that either the auto_increment value has been corrupted or mysql just needs to refresh some kind of table status cache it has. So the fix here was simply:
analyze table `design_config_dummy_cl`;
analyze table `customer_dummy_cl`;
analyze table `catalog_product_flat_cl`;
analyze table `catalog_category_flat_cl`;
analyze table `catalog_category_product_cl`;
analyze table `catalog_product_category_cl`;
analyze table `catalogrule_rule_cl`;
analyze table `catalog_product_attribute_cl`;
analyze table `cataloginventory_stock_cl`;
analyze table `inventory_cl`;
analyze table `catalog_product_price_cl`;
analyze table `catalogsearch_fulltext_cl`;
analyze table `amasty_feed_entity_cl`;
analyze table `amasty_feed_product_cl`;
ANalyze, causes that “internal cache” nonsense or whatever if it, so update and then our indexer ran just fine on one of our test servers.
On another test server, we were met with more errors:
Catalog Product Rule indexer process unknown error:
SQLSTATE[HY000]: General error: 1419 You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable), query was: CREATE TRIGGER trg_catalog_product_entity_after_insert AFTER INSERT ON catalog_product_entity FOR EACH ROW
BEGIN
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `scconnector_google_remove_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `scconnector_google_feed_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
Product Price indexer process unknown error:
SQLSTATE[HY000]: General error: 1419 You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable), query was: CREATE TRIGGER trg_catalog_product_entity_after_insert AFTER INSERT ON catalog_product_entity FOR EACH ROW
BEGIN
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `scconnector_google_remove_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `scconnector_google_feed_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
Luckily here, the fix was pretty easy too:
just log into mysql from cli, and run:
set global log_bin_trust_function_creators=1;
And then all should be well:
[staging@dev htdocs]$ php bin/magento indexer:reindex
Design Config Grid index has been rebuilt successfully in 00:00:00
Customer Grid index has been rebuilt successfully in 00:00:03
Product Flat Data index has been rebuilt successfully in 00:00:01
Category Flat Data index has been rebuilt successfully in 00:00:00
Category Products index has been rebuilt successfully in 00:00:00
Product Categories index has been rebuilt successfully in 00:00:00
Catalog Rule Product index has been rebuilt successfully in 00:00:00
Product EAV index has been rebuilt successfully in 00:00:00
Stock index has been rebuilt successfully in 00:00:00
Inventory index has been rebuilt successfully in 00:00:00
Catalog Product Rule index has been rebuilt successfully in 00:00:00
Product Price index has been rebuilt successfully in 00:00:04
Google Product Removal Feed index has been rebuilt successfully in 00:00:00
Google Product Feed index has been rebuilt successfully in 00:00:00
Catalog Search index has been rebuilt successfully in 00:00:00
Amasty Feed Rule index has been rebuilt successfully in 00:00:00
Amasty Feed Products index has been rebuilt successfully in 00:00:00
Lastly, for a healthy magento, You’ll want to make sure your indexer are set to run on a schedule:
php bin/magento indexer:set-mode schedule