0

Good Day

I am very much a novice when it comes to this. I upgraded my store from Magento 2.2.8 to 2.3.3.

All seemed initially to go without issues.

However on reindexing i get the below error.

SQLSTATE[42000]: Syntax error or access violation: 1142 TRIGGER command denied to user 'mydatabaseusername' for table 'catalog_product_index_price_replica', query was: INSERT INTO catalog_product_index_price_replica (entity_id, customer_group_id, website_id, tax_class_id, price, final_price, min_price, max_price, tier_price) SELECT catalog_product_index_price_temp.entity_id, catalog_product_index_price_temp.customer_group_id, catalog_product_index_price_temp.website_id, catalog_product_index_price_temp.tax_class_id, catalog_product_index_price_temp.price, catalog_product_index_price_temp.final_price, catalog_product_index_price_temp.min_price, catalog_product_index_price_temp.max_price, catalog_product_index_price_temp.tier_price FROM catalog_product_index_price_temp ON DUPLICATE KEY UPDATE entity_id = VALUES(entity_id), customer_group_id = VALUES(customer_group_id), website_id = VALUES(website_id), tax_class_id = VALUES(tax_class_id), price = VALUES(price), final_price = VALUES(final_price), min_price = VALUES(min_price), max_price = VALUES(max_price), tier_price = VALUES(tier_price)

The reindex appears to be attempting to use "mydatabaseusername" which is my old username and this is throwing the error.

I have checked app/etc/env.php and database name, username and password are correct.

All other reindexs are successful.

I have cleared and flushed cache.

I have search the internet and cant seem to find anyone with a similar problem.

Does anyone know how to resolve this?

1

following steps will help you.

  1. Export catalog_product_index_price_replica table.
  2. find database user name which is not related to current database.
  3. replace old dbuser to current dbuser.
  4. Take database backup.
  5. drop catalog_product_index_price_replica and Import last modified .sql file

reference chage privillages

Thank you.

| improve this answer | |
  • thanks that worked – Stuart Jan 5 at 21:20

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.