Magento – Magento 2 Catalog Product Flat Data not reindexing

magento2reindex

the indexing of magento product Flat data is staying on "index required".The mode is on "Scheme" and the cron is running. I tried several things like:

truncate catalog_product_flat_1, catalog_product_flat_2,
catalog_product_flat_3, catalog_product_flat_4

php bin/magento indexer:reindex
php bin/magento indexer:reset 
php bin/magento indexer:reindex
php bin/magento indexer:reset catalog_product_flat

Error :-

message in shell: State for the same indexer already exists.

update indexer_state set status = 'invalid' where status != 'valid';

Then reindex again

Can anybody help?
Thanks

Best Answer

In your database, try running the below commnand

update indexer_state set status = 'invalid' where status != 'valid';

Then reindex again

Related Solutions

Magento – “Cannot initialize the indexer process.” error while Reindexing Product Flat Data Index

Based on the error you specified in the comment, I can conclude that you cannot use the flat product tables. It's exactly what I was saying here. You have too many attributes (or too large values) used in product listing. Edit your product attributes and change the field Used in product listing to no for the attributes that are not needed in the product list.
If they are not that many, you may want to remove from product list the product description or any other field that has long values.

[EDIT]
If you don't want to remove attributes from product listing you need to disable the flat products. The website will work a little (or a lot) slower, but at least it will work.

Reindex – Product Flat Data Won’t Reindex in Magento

Hoping I understood the problem at hand: and the question...

This part of the error message is telling:

startlondon_live`.`#sql-448_15e`, CONSTRAINT `FK_MAG_MAG_CAT_PRD_FLAT_1_ENTT_ID_MAG_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `mag_catalog_product_entity` (`entity_id`)...

What this is telling me is that the column startlondon_live is referencing a catalog product entity id column (mag_catalog_product_entity) on the entity field but that entry or field no longer exists. The name of the foreign key is not formatted as the Magento installer would name it, so that tells me it is custom.

In essence the constraint you've created from another table is failing in the indexer because the target column either no longer exists or will be deleted, orphaning the child.

This brings me to a few thoughts:

When creating new fields in either custom resource models or to extend the Magento catalog it is unnecessary to modify flat tables.
It may also be unnecessary to create foreign key relationships to entity_id's on flat tables. FK relationships enforce schema and prevent data errors or orphaned content but, at the heart of Magento, EAV makes no effort to enforce this schema. Flat tables are built on-the-fly and attributes which expose themselves as Frontend attributes will automagically create necessary columns for the flat tables.
In the case that this is a custom resource model you don't need to have a FK relationship to the flat table -- instead create the FK relationship to the catalog_product_entity table; after all, that's the entity of record in Magento for a product. You may still define your ON DELETE rules.

In the end you'll need to fix this error. Disabling foreign key checks is a recipe for disaster.

Instead, remove your schema edits from Magento DB. Disable/enable flat catalog - or in the worst-case scenario drop your catalog_product_flat* tables and rebuild them.

Best Answer

Related Solutions

Magento – “Cannot initialize the indexer process.” error while Reindexing Product Flat Data Index

Reindex – Product Flat Data Won’t Reindex in Magento

Related Topic