Bug #57215 View definitions are lost in mysqldump's output on error: 1049 with --force
Submitted: 4 Oct 2010 12:34
Reporter: Nidhi Shrotriya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.1.50 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[4 Oct 2010 12:34] Nidhi Shrotriya
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.
[4 Oct 2010 13:06] Peter Laursen
Just a comment:

IMHO opinion yet another example that the 'temporary dummy tables' created to handle 'views defined on views' is an unhappy construction. In my opinion there should be a VIEW_CHECKS variable in the server similar to FOREIGN_KEY_CHECKS so that this 'temporary dummy tables'-construction is not necessary. It also fails it the view has too many columns for a 'temporary dummy' MYISAM table for instance (I think this bug was never fixed?).

There is no client-side solution to those problems.  The only 100% solution will have to be implemented server-side. 

A backup (with mysqldump or other clients) may be (will must often be) a scheduled job and a database may be dropped once.  If it is overlooked that this database is specified in a backup job then it will happen like this if --force/'continue on error' is used in the client.  

Peter
(not a MySQL person)