Differences
This shows you the differences between two versions of the page.
|
mysql_views [2012/05/22 13:56] |
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> | ||
