0

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?
  • 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? – Nickool Jul 11 '19 at 22:27
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;
| improve this answer | |
  • Thank you. I got permission denied errors when I tried to update the view, so I added that user and now it works. – Ben Rubin Jul 12 '19 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.