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> |