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:
- Create a backup of the database (mysqldump or phpadmin)
- Open the backup file in your favourite editor (i.e. Notepad – not recommend for large databases mind you).
- Find `user`@`localhost` (i.e. what Magento reports in the error as the first screenshot of this post).
- Replace that with `newuser`@`newhost` (a user that has permissions for the database).
- i.e. `root`@`localhost` or `root`@`127.0.0.1` or `root`@`dbserveripaddress`
- Save the Sql file
- 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
Hi Gurdeep,
Thanks for such useful post. I tried 3rd solution and it worked great for me.
Keep writing such posts.
Glad to have helped
Should I remove the “DEFINER” part from my DB follow this post: https://support.plesk.com/hc/en-us/articles/360005033014-Unable-to-import-a-MySQL-dump-ERROR-1227-42000-at-line-1421-Access-denied-you-need-at-least-one-of-the-SUPER-privilege-s-for-this-operation
Hello Jared,
Yes you can remove DEFINER from your database dump after you have dumped it or you can now skip the definer when you do an mysqldump (mysql v5.7+):
--skip-definer
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer