Magento 2 Reindex failed on customer grid

Question

Magento 2 (Version 2.1.6) shows allways 2 or 3 Indexes with the processing state - and it never ends. It stays for days under processing.

After a php bin/magento indexer:reset all the indexes are out of date - which is fine I think.

But after a php bin/magento indexer:reindex I got the fallowing message:

php bin/magento indexer:reindex Design Config Grid index has been rebuilt successfully in 00:00:00 Customer Grid indexer process unknown error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento2.customer_grid_flat' doesn't exist, query was: CREATE TABLE IF NOT EXISTS customer_grid_flat ( entity_id int UNSIGNED NOT NULL COMMENT 'Entity ID' , name text NULL COMMENT 'Name' , email varchar(255) NULL COMMENT 'Email' , group_id int NULL COMMENT 'Group_id' , created_at timestamp NULL default NULL COMMENT 'Created_at' , website_id int NULL COMMENT 'Website_id' , confirmation varchar(255) NULL COMMENT 'Confirmation' , created_in text NULL COMMENT 'Created_in' , dob date NULL COMMENT 'Dob' , gender int NULL COMMENT 'Gender' , taxvat varchar(255) NULL COMMENT 'Taxvat' , lock_expires timestamp NULL default NULL COMMENT 'Lock_expires' , shipping_full text NULL COMMENT 'Shipping_full' , billing_full text NULL COMMENT 'Billing_full' , billing_firstname varchar(255) NULL COMMENT 'Billing_firstname' , billing_lastname varchar(255) NULL COMMENT 'Billing_lastname' , billing_telephone varchar(255) NULL COMMENT 'Billing_telephone' , billing_postcode varchar(255) NULL COMMENT 'Billing_postcode' , billing_country_id varchar(255) NULL COMMENT 'Billing_country_id' , billing_region varchar(255) NULL COMMENT 'Billing_region' , billing_street varchar(255) NULL COMMENT 'Billing_street' , billing_city varchar(255) NULL COMMENT 'Billing_city' , billing_fax varchar(255) NULL COMMENT 'Billing_fax' , billing_vat_id varchar(255) NULL COMMENT 'Billing_vat_id' , billing_company varchar(255) NULL COMMENT 'Billing_company' , PRIMARY KEY (entity_id), INDEX CUSTOMER_GRID_FLAT_GROUP_ID (group_id), INDEX CUSTOMER_GRID_FLAT_CREATED_AT (created_at), INDEX CUSTOMER_GRID_FLAT_WEBSITE_ID (website_id), INDEX CUSTOMER_GRID_FLAT_CONFIRMATION (confirmation), INDEX CUSTOMER_GRID_FLAT_DOB (dob), INDEX CUSTOMER_GRID_FLAT_GENDER (gender), INDEX CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID (billing_country_id), FULLTEXT FTI_8746F705702DD5F6D45B8C7CE7FE9F2F (name, email, created_in, taxvat, shipping_full, billing_full, billing_firstname, billing_lastname, billing_telephone, billing_postcode, billing_region, billing_city, billing_fax, billing_company) ) COMMENT='customer_grid_flat' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci Category Products index has been rebuilt successfully in 00:00:01 Product Categories index has been rebuilt successfully in 00:00:00 Product Price index has been rebuilt successfully in 00:00:00 Product EAV index has been rebuilt successfully in 00:00:03 Stock index has been rebuilt successfully in 00:00:00 Catalog Rule Product index has been rebuilt successfully in 00:00:00 Catalog Product Rule index has been rebuilt successfully in 00:00:00 Catalog Search index has been rebuilt successfully in 00:00:13 Search Spell-Correction index has been rebuilt successfully in 00:00:05 www-data@dicota-com:~/magento$ www-data@dicota-com:~/magento$ www-data@dicota-com:~/magento$ php bin/magento indexer:reindex Design Config Grid index has been rebuilt successfully in 00:00:00 Customer Grid indexer process unknown error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento2.customer_grid_flat' doesn't exist, query was: CREATE TABLE IF NOT EXISTS customer_grid_flat ( entity_id int UNSIGNED NOT NULL COMMENT 'Entity ID' , name text NULL COMMENT 'Name' , email varchar(255) NULL COMMENT 'Email' , group_id int NULL COMMENT 'Group_id' , created_at timestamp NULL default NULL COMMENT 'Created_at' , website_id int NULL COMMENT 'Website_id' , confirmation varchar(255) NULL COMMENT 'Confirmation' , created_in text NULL COMMENT 'Created_in' , dob date NULL COMMENT 'Dob' , gender int NULL COMMENT 'Gender' , taxvat varchar(255) NULL COMMENT 'Taxvat' , lock_expires timestamp NULL default NULL COMMENT 'Lock_expires' , shipping_full text NULL COMMENT 'Shipping_full' , billing_full text NULL COMMENT 'Billing_full' , billing_firstname varchar(255) NULL COMMENT 'Billing_firstname' , billing_lastname varchar(255) NULL COMMENT 'Billing_lastname' , billing_telephone varchar(255) NULL COMMENT 'Billing_telephone' , billing_postcode varchar(255) NULL COMMENT 'Billing_postcode' , billing_country_id varchar(255) NULL COMMENT 'Billing_country_id' , billing_region varchar(255) NULL COMMENT 'Billing_region' , billing_street varchar(255) NULL COMMENT 'Billing_street' , billing_city varchar(255) NULL COMMENT 'Billing_city' , billing_fax varchar(255) NULL COMMENT 'Billing_fax' , billing_vat_id varchar(255) NULL COMMENT 'Billing_vat_id' , billing_company varchar(255) NULL COMMENT 'Billing_company' , PRIMARY KEY (entity_id), INDEX CUSTOMER_GRID_FLAT_GROUP_ID (group_id), INDEX CUSTOMER_GRID_FLAT_CREATED_AT (created_at), INDEX CUSTOMER_GRID_FLAT_WEBSITE_ID (website_id), INDEX CUSTOMER_GRID_FLAT_CONFIRMATION (confirmation), INDEX CUSTOMER_GRID_FLAT_DOB (dob), INDEX CUSTOMER_GRID_FLAT_GENDER (gender), INDEX CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID (billing_country_id), FULLTEXT FTI_8746F705702DD5F6D45B8C7CE7FE9F2F (name, email, created_in, taxvat, shipping_full, billing_full, billing_firstname, billing_lastname, billing_telephone, billing_postcode, billing_region, billing_city, billing_fax, billing_company) ) COMMENT='customer_grid_flat' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci Category Products index has been rebuilt successfully in 00:00:02 Product Categories index has been rebuilt successfully in 00:00:00 Product Price index has been rebuilt successfully in 00:00:01 Product EAV index has been rebuilt successfully in 00:00:03 Stock index has been rebuilt successfully in 00:00:00 Catalog Rule Product index has been rebuilt successfully in 00:00:00 Catalog Product Rule index has been rebuilt successfully in 00:00:00 Catalog Search index has been rebuilt successfully in 00:00:11 Search Spell-Correction index has been rebuilt successfully in 00:00:05 `

I can repeat that - the same error message. I checked the database table and we have a magento2.customer_grid_flat - so it looks ok for me. After a few days the index goes on status READY - without any interaction from our side. But then - 2 or 3 (mostly customer grid and catalog search) indexes are again the processing state and will not be updated longer (until we kill all indexes with the reset command)

Any hints?

PHP Version: PHP 7.0.15-0ubuntu0.16.04.4 (cli) ( NTS ) Copyright (c) 1997-2017 The PHP Group Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies with Zend OPcache v7.0.15-0ubuntu0.16.04.4, Copyright (c) 1999-2017, by Zend Technologies

Answer 1

In my case I had two tables that were preventing a re-index. The first one was orphaned (https://stackoverflow.com/a/10622738/1055492): customer_grid_flat In /opt/bitnami/mysql/data/mydbname, I found only a customer_grid_flat.ibd file and no customer_grid_flat.frm file. I renamed the ibd file to ibd.old and this fixed the reindex for this table.

My second table design_config_grid_flat had both a .ibd and .frm file which I assume had become corrupted, to get this working I had to do four things (https://stackoverflow.com/a/18034693/1055492):

1. Rename both files
2. Copied the SQL that failed in the reindex and ran it it another temporary db
3. Copied the .idb and .frm file from /opt/bitnami/mysql/data/tmpdbname to /opt/bitnami/mysql/data/mydbname
4. Reindexed

Disclaimer

• The Magento 2 instance in my case is a Bitnami cloud virtual machine and your mysql location may vary.
• I take no responsibility for your lost data