Magento 2 The user specified as a definer does not exist

This is a strange problem I have not come across before with Magento in general – it looks like it’s a potential problem for Magento 2 developers copying from production sites back to dev / stage / testing.
The user specified as a definer does not exist

How did I come across this problem?

I recently setup a development environment for a site I have been working on. The plan was to move this development database to another dev.

This has been accomplished easily in the past with php cms systems I’ve worked with, namely WordPress, Magento and Joomla.

I copied the database from one development environment to another. The second development environment had different MySQL users compared to my dev.

The second developer tried to save a product but received the error.

Why does this appear in Magento 2?

I’m not certain this is isolated to Magento 2 and if it exists in Magento 1. Most of the Magento 1 websites I have come across, use a root MySQL user for connectivity. This isn’t best practice for security since a root login can compromise the entire database server. In retrospect, I usually setup new MySQL users and limit access to only the database that user needs access to.

The off chance of this problem existing in Magento 1 might be common but my lack of experience of this is probably due to working with root users for production & development environments.

Alert: This issue has been documented and discussed very briefly in the Magento ‘cloud’ documentation: http://devdocs.magento.com/guides/v2.0/cloud/live/stage-prod-migrate.html

What is “user specified as a definer does not exist”?

Since my experience with SQL began MS SQL – I used to frequently create stored procedures. Stored procedures are automated processes (like cron jobs) that run on the SQL server.

When I started looking into this particular issue, it appears that Magento 2 is using MySQL triggers. A MySQL trigger is similar to a Magento event handler – i.e. “listen” for x and “run code” y as a result.

You can find out exactly if your install of Magento has triggers setup by running this mysql query in your mysql terminal:

use database_name;
show triggers;

Hopefully you’ll see an output similar to the following screenshot:

definer_user_does_not_exist_magento_2_mysql
Highlighted in yellow, are the triggers restricted to run by the definer user@localhost – sort of like permissions of which MySQL user is allowed to run these triggers. You can read more on definers here: https://dev.mysql.com/doc/refman/5.7/en/stored-programs-security.html

As I said previously, the database was moved between environments, and each environment has different MySQL users. The database was migrated between devs by exporting (mysqldump) and re-importing into the second dev.

Summary: This error is happening because the new dev does not have ‘user@localhost’ created. Therefore the triggers do not have permission to run.

The solution

There are three – the first is quick and dirty, the second is ‘proper’ as recommended by Magento and the third is an alternative if the second fails.

Disclaimer: Do not attempt this on production! Please try it on your dev / test environment.

Solution 1 – Quick

Simply create the missing MySQL user and that should solve the problem.

Solution 2 – Magento Official

The official Magento solution involves running a MySQL dump but using an empty string for the definer. Give this a try first before using Solution 3.

http://devdocs.magento.com/guides/v2.0/cloud/live/stage-prod-migrate.html

Solution 3

Solution 3 has the same concept of replacing the definer string – but instead using the find and replace function in your favourite text editor after the database backup file has been created.

This process successfully worked for me:

  1. Create a backup of the database (mysqldump or phpadmin)
  2. Open the backup file in your favourite editor (i.e. Notepad – not recommend for large databases mind you).
  3. Find `user`@`localhost` (i.e. what Magento reports in the error as the first screenshot of this post).
  4. Replace that with `newuser`@`newhost` (a user that has permissions for the database).
    1. i.e. `root`@`localhost` or `root`@`127.0.0.1` or `root`@`dbserveripaddress`
  5. Save the Sql file
  6. Re-import the database backup

Alternatively you can replace step 3 & 4 with this linux sed console command:


sed -i -- 's/`user`@`localhost`/`root`@`localhost`/g' database_file_name.sql

 

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *