Solving Magento Re-indexing Error on Replica Database

A common issue that face many developers who are working on magento 2.x, is indexing error that arise when importing and existing website database to a replica website that acts as back up for the original one, which impact cron jobs.

A quick solution provided by magento CLI is to use the following reindexing command in magento project root:

bin/magento indexer:reindex indexer_name

If everything was working fine so you should see this:

indexer_name has been rebuilt successfully in x time

The other scenario is having error like this:

SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'dummy_user'@'localhost' (using password: YES), query was: "What ever SQL statement"

sample error:

This error indicates an issue with view "inventory_stock_1".

The issue is that the DEFINER has wrong user.

To solve this

Through phpmyadmin interface:

1. Navigate to the required view, in our case "inventory_stock_1".
2. Go to Structure Tab.
3. Select Edit View

1. Add the right db user name in definer field

Note: you can get the right username navigating from magento project root directory to app/etc/env.php.

Now hopefully you can navigate back to your magento project root directory and use rendinxing command.

A Treat

In some cases after reindxing you may face invalidated cache disclaimer like this one.

All you need is to click on Cache Management and select the invalidated cache(s).

Then choose refresh, click submit and all is fine!