mysqldump error 1449

Standard

mysqldump error: 1449: The user specified as a definer (fixed)

 

It happend to me that I tried to make a dump of my database and this horrible error came up. The user definer was different for one of the views I had declared in the DB.  So, I decided to write it down on order to remember if this happens again or maybe it could help someone else.

First, check your views ot make sure what is going on.

SELECT table_catalog, table_schema, table_name, check_option, is_updatable, definer, security_type, character_set_client, collation_connection FROM information_schema.views;

In my case, there was created an unnecessary view, so I drop the view;

DROP VIEW my_view_name;

If you want to change the definer of the view that is causing problems, then, you need to change the definer:

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='root@foobar'

With this step, you need to be careful because it changes the definer for all the views of all the databases.

This link in  stackoverflow.com might help as well. It explains some other things about this error.

Hope this help!