Magento 2 - Programmatically recreate the missing Catalog Search index table

After a Magento upgrade, the mg3_catalogsearch_fulltext_scope1 table disappeared from the MySQL database of my Magento 2-based webshop. As a result, all product-related pages were returning HTTP 500 error code, and I could not create or update products in the admin panel.

SQLSTATE[42S02]: Base table or view not found: 1146 Table <...>.mg3_catalogsearch_fulltext_scope1' doesn't exist

TLDR

The original database was broken, so I’ve cloned it and used this script to recreate the missing index.

It was a dark and stormy night

Because I had no clue about the exact schema of the missing database table, I decided to recreate it programmatically using Magento’s built-in indexer service. Also, I could not use any command-line tools to fix the situation, as the webshop is running on a cheap host provider with no SSH access to the machine where Magento or the underlying MySQL database is hosted.

Access to Magento's services

As a first step, I created a simple PHP script based on Magento’s index.php to have access to Magento’s classes and services.

try {
    require __DIR__ . '/app/bootstrap.php';
} catch (\Exception $e) {
    echo $e->getMessage();
    exit(1);
}

$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);
$app = $bootstrap->createApplication('Magento\Framework\App\Http');

Recreate the index table

Next, based on this Stack Exchange thread I’ve added a snippet to create the Catalog Search index table using IndexerHandlerFactory.

$indexerHandlerFactory = \Magento\Framework\App\ObjectManager::getInstance()->get("Magento\\CatalogSearch\\Model\\Indexer\\IndexerHandlerFactory");
$indexerConfig = \Magento\Framework\App\ObjectManager::getInstance()->get("Magento\\Framework\\Indexer\\ConfigInterface");
$dimensionFactory = \Magento\Framework\App\ObjectManager::getInstance()->get("Magento\\Framework\\Search\\Request\\DimensionFactory");

try {
    $dimensions = [
        $dimensionFactory->create(['name' => 'scope', 'value' => 'default'])
    ];
    $configData = $indexerConfig->getIndexer('catalogsearch_fulltext');
    $indexHandler = $indexerHandlerFactory->create(['data' => $configData]);
    $indexHandler->cleanIndex($dimensions);
} catch(Exception $e) {
    echo 'Error reindexing catalogsearch_fulltext<br/>';
    print($e->getMessage());
}

I’ve put this script next to the index.php, into the /home/<username>/public_html directory, hoping that after executing it, I get my indexes back.

Detour: what is a tablespace?

But then I faced with another issue: the upgrade only did a half-ass job deleting mg3_catalogsearch_fulltext_scope1. An attempt to recreate the table also failed:

Tablespace for table '`<...>`.`mg3_catalogsearch_fulltext_scope1`' exists. Please DISCARD the tablespace before IMPORT.

Trying to drop the said tablespace via phpMyAdmin also failed:

DROP TABLESPACE `<...>.mg3_catalogsearch_fulltext_scope1`;

# > Access denied; you need (at least one of) the CREATE TABLESPACE privilege(s) for this operation

According to Wikipedia, "a tablespace is a storage location where the actual data underlying database objects can be kept". Based on this Stackoverflow discussion, in case of MySQL they can get “stuck”, and the best chance to remove them is to delete the corresponding file and restart MySQL. As I don’t have SSH access this was not an option.

So I decided to leave the original database behind and create a clone with phpMyAdmin, hoping that it copies the schema and the data, but not the faulty tablespaces.

Reindex products

After I reconfigured Magento to use the new database and ran the script, it indeed created the missing database table, and the website started to work again. However, I quickly realized that while all products are editable in the admin panel, they are not visible in the webshop UI. No wonder why: although now the index table is in place, it’s empty.

So I edited the PHP script to populate the indexes as well: (source)

$indexerFactory = \Magento\Framework\App\ObjectManager::getInstance()->get("Magento\Indexer\Model\IndexerFactory");
$indexerIds = array(
    'catalog_category_product',
    'catalog_product_category',
    'catalog_product_price',
    'catalog_product_attribute',
    'cataloginventory_stock',
    'catalogrule_product',
    'catalogsearch_fulltext',
);
foreach ($indexerIds as $indexerId) {
    try {
        echo " create index: ".$indexerId."\n";
        $indexer = $indexerFactory->create();
        $indexer->load($indexerId);
        $indexer->reindexAll();

    } catch (\Exception $e) {
        echo 'Error reindexing ' . $indexerId . '<br/>';
        echo $e->getMessage();
        echo "<br/>";
    }
}

End game

For some reason, the Category caches were not refreshed, so I had to save on one of the products in the admin panel to get the menus working again. Finally, I removed my PHP script from the server, and the Webshop lived happily ever after.

Despite I had no SSH access I could solve the problem by deploying custom PHP scripts. If you do so, make sure to clean up troubleshooting related scripts at the end.

The end.