Customer Grid Empty
The customer list in Magento 2 may appear empty despite the fact there are many rows/customers in the customer_entity
table. The grid depends on an indexer called the customer_grid index that stores data in the customer_grid_flat
table. In this case, the flat table has not been properly populated by the indexer.
By tracing the indexer, it may show a "hang" on a query such as the following:
sendto(5, ",\7\0\0\3SELECT `e`.*, `e`.`entity_id`, TRIM(CONCAT_WS(' ', IF(`e`.`prefix` <> '', `e`.`prefix`, NULL), IF(`e`.`firstname` <> '', `e`.`firstname`, NULL), IF(`e`.`middlename` <> '', `e`.`middlename`,<br />
NULL), IF(`e`.`lastname` <> '', `e`.`lastname`, NULL), IF(`e`.`suffix` <> '', `e`.`suffix`, NULL))) AS `name`, `e`.`email`, `e`.`group_id`, `e`.`created_at`, `e`.`website_id`, `e`.`confirmation`,<br />
`e`.`created_in`, `e`.`dob`, `e`.`gender`, `e`.`taxvat`, (SELECT `lookup`.`last_visit_at` FROM `customer_visitor` AS `lookup` WHERE (`lookup`.`customer_id` = `e`.`entity_id`) ORDER BY `lookup`.`last_visit_at`<br />
DESC LIMIT 1) AS `last_visit_at`, TRIM(CONCAT_WS(' ', IF(`shipping`.`street` <> '', `shipping`.`street`, NULL), IF(`shipping`.`city` <> '', `shipping`.`city`, NULL), IF(`shipping`.`region` <> '',<br />
`shipping`.`region`, NULL), IF(`shipping`.`postcode` <> '', `shipping`.`postcode`, NULL))) AS `shipping_full`, TRIM(CONCAT_WS(' ', IF(`billing`.`street` <> '', `billing`.`street`, NULL), IF(`billing`.`cit"...,<br />
1840, MSG_DONTWAIT, NULL, 0) = 1840
The problem, in this case, is the customer_visitor
table. By inspection, if the table has several hundred thousands of rows, it should be emptied and reindexing again attempted.
The status in the
indexer_state
table may need updating.
update indexer_state set status="invalid" where state_id=1;
The customer grid should now populate.