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.

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.

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:

Highlighted in yellow, are the triggers restricted to run by the definer [email protected] – 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.

The solution

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

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

Solution 4 – new

This is the solution I recommend if you haven’t done it already. If you’re using MySQL 5.7 or above, you can now ignore exporting the definer in your database dump.

https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer

Step 1

Dump the database where you are facing this problem but use the new parameter available in MySQL 5.7+ to ignore the definer when you dump your database:

mysqldump --uuser -p --skip-definer database_name > dump_without_definer.sql

Step 2

After the dump has been made it should no longer have definer set.

Report this new dump back into your database and you should be good to go:

mysql --uuser -p database_name < dump_without_definer.sql