No products on site after backup/restore database from one server to another

Question

I'm trying to copy the database data from my production Magento 2.3.2 site to a local development site.

I created a backup of my production MySQL database with phpmyadmin like in the screenshot below.

enter image description here

Then on my local server, I created a new database named magento and restored my production database (named ad2f5134_newlive) into it with mysql -u root -p magento < ad2f5134_newlive.sql

Then I updated the URLs in core_config_data for my local development site.

The problem is that no products show up on my local development site. When I go to Catalog> Products in the Admin section, I get this message:

enter image description here

I checked catalog_product_entity on my local development site, and all the products from my production site are in the table.

One thought I had is that it is an indexer problem, so I ran bin/magento indexer:reindex. That gives me the following error:

General error: 1449 The user specified as a definer ('ad2f5134_newlive'@'%' does not exist...

So my two questions are:

• Why are no products on the site -- even though they are in the database -- and why don't any products show up in the Admin section under Catalog> Products?
• The user ad2f5134_newlive is the database user from my production site. Something in the database must now be telling my local development site to be using the ad2f5134_newlive user to run those reindexing queries. How do I change that user to by my local user?

• Nickool: I believe you have some procedure which is using the production user go to your selected database in phpmyadmin, what do you have in your Routins tab?

Answer 1

Option 1 : for this error, you can try with below process :

1. Run this SQL to generate the necessary ALTER statements

   SELECT CONCAT("ALTER DEFINER='your-user@host' VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name';

2. Copy and run the ALTER statements

Example:

UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%'

Option 2 :

Create the missing user If you've found following error while using MySQL database:

The user specified as a definer ('some-user'@'%') does not exist` Then you can solve it by using following :

GRANT ALL ON *.* TO 'some-user'@'%' IDENTIFIED BY 'complex-password';
FLUSH PRIVILEGES;

• Ben Rubin: Thank you. I got permission denied errors when I tried to update the view, so I added that user and now it works.