Differences

This shows you the differences between two versions of the page.

Link to this comparison view

mysql_views [2012/05/22 13:56] (current)
Line 1: Line 1:
 +====== MySQL Views ======
  
 +  * [[MySQL]]
 +  * [[mysqldump]]
 +
 +== Display a list of the views ==
 +
 +<​code>​
 +SELECT table_schema,​ table_name FROM information_schema.views;​
 +</​code>​
 +
 +== Display a view creation statement ==
 +
 +<​code>​
 +SELECT view_definition FROM information_schema.views WHERE table_name = <​view>;​
 +</​code>​
 +
 +== mysqldump notes ==
 +
 +When ''​mysqldump''​ dumps a database containing views, it will also export the user that created the view.
 +
 +When restoring the database dump, the views will be created successfully,​ but they also can only be executed by the same user.
 +
 +Trying to access the view using an SQL statement, or trying to dump the database will throw an error:
 +
 +<​code>​
 +ERROR 1449 (HY000): The user specified as a definer ('​username'​@'​hostname'​) does not exist
 +</​code>​
 +
 +There are a few ways to avoid this error:
 +
 +  - Delete the lines containing ''​DEFINER=`username`@`hostname` SQL SECURITY DEFINER''​ from the dump file.  This will create the views as the same user that is executing the restore.
 +  - Create the user on the target database
 +  - Modify the user to be something else
 +
 +If you want to recreate the user, here's how to get the statement to insert into the ''​mysql''​ database:
 +
 +<​code>​
 +mysqldump --no-create-info --compact mysql --tables user -w "​User='​username'"​
 +</​code>​