Description:
Created table & view as below:
-------------------------------
create database part_db;
use part_db;
create table test_table(i int);
create view test_view as select i from test_table;
Now dump as below ('part_db' is used along with a unknown database 'privdb'):
------------------------------
bash-3.2$ bin/mysqldump --user=root --socket=/tmp/mysql.sock --databases privdb part_db --force > mydump
_with_error
mysqldump: Got error: 1049: Unknown database 'privdb' when selecting the database
As --force option is used to continue the dump in case of SQL errors, it is assumed that the error is printed but the dump continues executing and is created fine.
On loading the dump back, view in part_db is seen but considered a table not a view.
mysql> show tables;
+-------------------+
| Tables_in_part_db |
+-------------------+
| test_table |
| test_view |
+-------------------+
2 rows in set (0.00 sec)
mysql> show create view test_view;
ERROR 1347 (HY000): 'part_db.test_view' is not VIEW
mysql> insert into test_table values(1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test_table;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> select * from test_view;
Empty set (0.00 sec)
On looking at the table dump, created by mysqldump in the above case shows that for views only table structure definitions are present but the actual view definitions are lost for the known database 'part_db'.
Only this is seen.
--
-- Temporary table structure for view `test_view`
--
DROP TABLE IF EXISTS `test_view`;
/*!50001 DROP VIEW IF EXISTS `test_view`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `test_view` (
`i` int(11)
) ENGINE=MyISAM */;
and the below definition is missing.
--
-- Final view structure for view `test_view`
--
/*!50001 DROP TABLE IF EXISTS `test_view`*/;
/*!50001 DROP VIEW IF EXISTS `test_view`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = latin1 */;
/*!50001 SET character_set_results = latin1 */;
/*!50001 SET collation_connection = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `test_view` AS select `test_table`.`i` AS `i` from `test_table` */;
How to repeat:
As mentioned in the description.
Suggested fix:
Workaround is to remove the unknown dbs from the --databases list, but should be fixed as this is misleading for the --force option and causes data inconsistency.
Description: Created table & view as below: ------------------------------- create database part_db; use part_db; create table test_table(i int); create view test_view as select i from test_table; Now dump as below ('part_db' is used along with a unknown database 'privdb'): ------------------------------ bash-3.2$ bin/mysqldump --user=root --socket=/tmp/mysql.sock --databases privdb part_db --force > mydump _with_error mysqldump: Got error: 1049: Unknown database 'privdb' when selecting the database As --force option is used to continue the dump in case of SQL errors, it is assumed that the error is printed but the dump continues executing and is created fine. On loading the dump back, view in part_db is seen but considered a table not a view. mysql> show tables; +-------------------+ | Tables_in_part_db | +-------------------+ | test_table | | test_view | +-------------------+ 2 rows in set (0.00 sec) mysql> show create view test_view; ERROR 1347 (HY000): 'part_db.test_view' is not VIEW mysql> insert into test_table values(1), (2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test_table; +------+ | i | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> select * from test_view; Empty set (0.00 sec) On looking at the table dump, created by mysqldump in the above case shows that for views only table structure definitions are present but the actual view definitions are lost for the known database 'part_db'. Only this is seen. -- -- Temporary table structure for view `test_view` -- DROP TABLE IF EXISTS `test_view`; /*!50001 DROP VIEW IF EXISTS `test_view`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; /*!50001 CREATE TABLE `test_view` ( `i` int(11) ) ENGINE=MyISAM */; and the below definition is missing. -- -- Final view structure for view `test_view` -- /*!50001 DROP TABLE IF EXISTS `test_view`*/; /*!50001 DROP VIEW IF EXISTS `test_view`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = latin1 */; /*!50001 SET character_set_results = latin1 */; /*!50001 SET collation_connection = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `test_view` AS select `test_table`.`i` AS `i` from `test_table` */; How to repeat: As mentioned in the description. Suggested fix: Workaround is to remove the unknown dbs from the --databases list, but should be fixed as this is misleading for the --force option and causes data inconsistency.