Magento 2 error when trying to reindex

Question

I am currently running Magento 2.3.2. The process went smoothly but after installing some extensions, it appears that some of the reindex tables have been deleted. This is the message I get when trying to reindex:

Design Config Grid indexer process unknown error:
Table status for `design_config_dummy_cl` is incorrect. Can`t fetch version id.
Customer Grid indexer process unknown error:
Table status for `customer_dummy_cl` is incorrect. Can`t fetch version id.
Category Products indexer process unknown error:
Table status for `catalog_category_product_cl` is incorrect. Can`t fetch version id.
Product Categories indexer process unknown error:
Table status for `catalog_product_category_cl` is incorrect. Can`t fetch version id.
Catalog Rule Product indexer process unknown error:
Table status for `catalogrule_rule_cl` is incorrect. Can`t fetch version id.
Product EAV indexer process unknown error:
Table status for `catalog_product_attribute_cl` is incorrect. Can`t fetch version id.
Stock indexer process unknown error:
Table status for `cataloginventory_stock_cl` is incorrect. Can`t fetch version id.
Inventory indexer process unknown error:
Table status for `inventory_cl` is incorrect. Can`t fetch version id.
Catalog Product Rule index has been rebuilt successfully in 00:00:14
Product Price indexer process unknown error:
Table status for `catalog_product_price_cl` is incorrect. Can`t fetch version id.
Catalog Search indexer process unknown error:
Table status for `catalogsearch_fulltext_cl` is incorrect. Can`t fetch version id.

Even after using the reset function then reindexing giving same error. Is there a way to resolve this issue?

Answer 1

OK I think I have the answer here.

The issue is that Magento checks table status and doesn't find the key as an auto_increment value (even though it is.)

If you run this:

show table status;

And scroll down to your offending table, you may find that the AUTO_INCREMENT value is set to null. That's what Magento means by "Table status incorrect".

My fix was to run this (changing table name for each one obviously):

alter table `my_table_cl` auto_increment = 1;

Potentially you may also have a caching issue described in the accepted answer to this stack overflow question. If my line still doesn't fix the problem try changing that cache setting.

Also if something were to go wrong where the version_id really wasn't auto_increment for some reason, you'll get this error. The solution there would be to just alter the column to make it AUTO_INCREMENT.

Later Edit: An alternative to turning off the status caching is to run this command after your index update:

analyze table `my_table_cl` 

Which will force a refresh.

Answer 2

Reset all index

bin/magento ind:reset

and try again.