4

I have migrated a Magento 2 site to a new server and getting the below error when placing order

We cant place the order : SQLSTATE[HY000]: General error: 1449 The user specified as a definer your_db_user@% does not exist, query was: UPDATE cataloginventory_stock_item SET qty = CASE product_id WHEN 65464 THEN qty-1 ELSE qty END WHERE (product_id IN (65464)) AND (website_id = 0)"}],"data_id":1563289789}}

4 Answers 4

7

After migrating the site I got the above error on placing the order. The user that was trying to access the DB is an old server DB user which didn't exist in the new server.

I searched for the user in DB and Code to make sure something wasn't hard coded and nothing was hardcoded.

Finally, after googling a bit I found a solution :

Problem: Magento creates a view which has a definer which has the name of the old user.

Solution :

  1. Go to PHPMyAdmin

  2. Select the table giving the error

  3. Click on Triggers

  4. Edit the trigger and Change the OLD user to the new DB user

Definer: [email protected] Change as Definer: [email protected]

And it's solved.

Thank you.

5

As far as I'm aware they've created only one view table

If you use mysqldump you end up with this


CREATE ALGORITHM=UNDEFINED DEFINER=`dbusername`@`localhost` SQL SECURITY INVOKER VIEW `inventory_stock_1`  AS  select distinct `legacy_stock_status`.`product_id` AS `product_id`,`legacy_stock_status`.`website_id` AS `website_id`,`legacy_stock_status`.`stock_id` AS `stock_id`,`legacy_stock_status`.`qty` AS `quantity`,`legacy_stock_status`.`stock_status` AS `is_salable`,`product`.`sku` AS `sku` from (`dbname`.`cataloginventory_stock_status` `legacy_stock_status` join `dbname`.`catalog_product_entity` `product` on((`legacy_stock_status`.`product_id` = `product`.`entity_id`)));

Which will either fail to import or create issues. Instead you should swap it in dump with this

CREATE
OR REPLACE
VIEW `inventory_stock_1` AS select
    distinct `legacy_stock_status`.`product_id` AS `product_id`,
    `legacy_stock_status`.`website_id` AS `website_id`,
    `legacy_stock_status`.`stock_id` AS `stock_id`,
    `legacy_stock_status`.`qty` AS `quantity`,
    `legacy_stock_status`.`stock_status` AS `is_salable`,
    `product`.`sku` AS `sku`
from
    ( `cataloginventory_stock_status` `legacy_stock_status`
join `catalog_product_entity` `product` on
        (( `legacy_stock_status`.`product_id` = `product`.`entity_id` )));
1
  • Upvote, It's helped me to find my issue. Thank You. May 25, 2023 at 9:35
2

Please follow the below steps to fix this issue:

  1. mysqldump -uroot -p --triggers --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt test > /tmp/triggers.sql

    Note: Here test is the DB name

  2. Open triggers.sql file in your favorite editor and use Find and Replace feature to change DEFINERs. Save updated file.

  3. Recreate triggers from the updated file by importing it via sql

    mysql> source triggers.sql

0

Solve it by using following :

mysql> GRANT ALL ON . TO 'your_db_user'@'%' IDENTIFIED BY 'root';

MySQL> FLUSH PRIVILEGES;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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