一网友使用mysqldump导出数据的时候,由于试图引用的表被drop,导出过程出现问题,模拟如下:
[root@goolen ~]# mysqldump -uroot -pxxx goolen > goolen.sql
mysqldump: Got error: 1356: View 'goolen.v_dbstatus' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES
[root@goolen ~]# mysqldump -uroot -pxxx --force goolen > goolen.sql
mysqldump: Got error: 1356: View 'goolen.v_dbstatus' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES
mysqldump: Couldn't execute 'SHOW FIELDS FROM `v_dbstatus`': View 'goolen.v_dbstatus' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
查看试图的定义:
mysql> show create view v_dbstatus;
ERROR 1046 (3D000): No database selected
mysql> use goolen;
Database changed
mysql> show create view v_dbstatus;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_dbstatus | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dbstatus` AS select `goolen`.`dbstatus`.`id` AS `id`,`goolen`.`dbstatus`.`host` AS `host`,`goolen`.`dbstatus`.`db_type` AS `db_type` from `dbstatus` | utf8 | utf8_general_ci |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.00 sec)
检查发现该试图引用的表已经不存在;
mysql> desc dbstatus;
ERROR 1146 (42S02): Table 'goolen.dbstatus' doesn't exist
删除该试图:
mysql> drop view v_dbstatus;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@goolen ~]# mysqldump -uroot -pxxx goolen > goolen.sql
[root@goolen ~]# mysqldump -uroot -pxxx goolen > goolen.sql
mysqldump: Got error: 1356: View 'goolen.v_dbstatus' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES
[root@goolen ~]# mysqldump -uroot -pxxx --force goolen > goolen.sql
mysqldump: Got error: 1356: View 'goolen.v_dbstatus' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES
mysqldump: Couldn't execute 'SHOW FIELDS FROM `v_dbstatus`': View 'goolen.v_dbstatus' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
查看试图的定义:
mysql> show create view v_dbstatus;
ERROR 1046 (3D000): No database selected
mysql> use goolen;
Database changed
mysql> show create view v_dbstatus;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_dbstatus | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dbstatus` AS select `goolen`.`dbstatus`.`id` AS `id`,`goolen`.`dbstatus`.`host` AS `host`,`goolen`.`dbstatus`.`db_type` AS `db_type` from `dbstatus` | utf8 | utf8_general_ci |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.00 sec)
检查发现该试图引用的表已经不存在;
mysql> desc dbstatus;
ERROR 1146 (42S02): Table 'goolen.dbstatus' doesn't exist
删除该试图:
mysql> drop view v_dbstatus;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@goolen ~]# mysqldump -uroot -pxxx goolen > goolen.sql
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23249684/viewspace-1481698/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23249684/viewspace-1481698/