1

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

  • Can u please drop table customer_grid_flat and try again? – Amit Bera May 16 '17 at 19:31
  • Is it safe? Because it's a productive system. I can't do that if I lose data. Thanks for a quick answer. Mirko – Mirko Hufnagel May 17 '17 at 20:09
  • @MirkoHufnagel were you able to figure out the fix? I having a similar issue, only in my case it is the "Product Flat Data" not re-indexing. I already tried to truncate flat catalog table, and so far no luck. On top of all, after the update I can't even add products, gets me to the 500 error page. – crashtestxxx May 30 '17 at 22:25
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.