Magento 2 Down: replica Tales not found when reindexing!

Question

My server went down because the disk was full, after fixing it, I noticed that the magento 2 website never came back: (Magento 2.2.4, PHP 7.0.30)

When re-indexing, i get the following error:

Table "cataloginventory_stock_status_replica" does not exist Table Table "catalog_product_index_eav_replica" does not exist Table Table "cataloginventory_stock_status_replica" does not exist

Exception Logs only shows this:

[2018-10-07 10:00:58] main.CRITICAL: SQLSTATE[HY000]: General error: 3 Error writing file './databse_mag2/catalogrule_product__temp30fd3506.frm' (Errcode: 28 - No space left on device), query was: create table catalogrule_product__temp30fd3506 like catalogrule_product {"exception":"[object] (Zend_Db_Statement_Exception(code: 0): SQLSTATE[HY000]: General error: 3 Error writing file './databse_mag2/catalogrule_product__temp30fd3506.frm

System logs shows this:

[2018-10-08 15:01:05] main.WARNING: Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size. Please update innodb_buffer_pool_size or decrease batch size value (which decreases memory usages for the temporary table). Current batch size: 100000; Allocated memory size: 200000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []

The website is loading in vain, I've no idea where or how to fix it, anyone has an idea how to fix this, please?

Answer 1

1.) Do a fresh install of the same magento version you're having problems with
2.) export the structure of the tables "catalog_product_index_price_replica" and "catalog_product_index_eav_replica"
3.) import those structures into your magento db where you're facing the issue
4.) run the commands
php bin/magento indexer:reset
php bin/magento indexer:reindex
these steps solved the issue for me in magento 2.2.7

Answer 2

Magento 2.2 onward introduced replica tables to temporary store indexed data and then switch to the original table once indexing is completed to avoid errors due to table locking.

Answer 3

Steps listed by user3376139 are correct but sometimes it requires additional actions:

1. Drop catalog_product_index_eav_replica table

2. If you try to re-create now you may get this error:

Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT

That means that you have an orphaned tablename.ibd file without its normal companion tablename.frm file. Go to the directory where the files-per-table are stored, /usr/local/var/mysql (default on OSX) and move that .ibd file to a safe temporary location (just to be safe) that should fix the problem.

3. Follow user3376139 steps