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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s