Magento 2 : We cant place the order : SQLSTATE[HY000]: General error: 1449 The user specified as a definer your_db_user@% does not exist

Question

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}}

Answer 1

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.

Answer 2

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` )));

Answer 3

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

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

Answer 4

Solve it by using following :

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

MySQL> FLUSH PRIVILEGES;