MySQL Bugs: #47706: Views in a mysqldump can overwrite tables even with skip-add-drop-table

Bug #47706	Views in a mysqldump can overwrite tables even with skip-add-drop-table
Submitted:	29 Sep 2009 10:54	Modified:	29 Sep 2009 11:18
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	mysql-5.1.39, 5.0, 5.1 bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
With the fix for bug #28524 restoring a view from a dump 
created with --skip-add-drop-table now works, but now a 
view with the same name as an existing table will overwrite
that table due to the unconditional DROP TABLE command for
the dummy table in the dump even when creating the dummy 
table didn't work as a table with the same name already 
existed

How to repeat:
the final output of the script below should be a CREATE TABLE statement but it actually shows a CREATE VIEW instead as the v1 table was overwritten when restoring the dump:

#! /bin/bash

mysql -u root <<END
 DROP DATABASE IF EXISTS mydb;
 CREATE DATABASE mydb;
 USE mydb;
 CREATE TABLE t1 (id int);
 CREATE VIEW v1 AS SELECT * FROM t1;
END

mysqldump -u root --no-data --routines --skip-add-drop-table mydb > mydb.sql

mysql -u root mydb <<END
  DROP TABLE t1;
  DROP VIEW  v1;

  CREATE TABLE t1 (id int, m int);
  CREATE TABLE v1 (id int, m int);
END

mysql -u root --force mydb < mydb.sql

mysql -u root -e "SHOW CREATE TABLE mydb.v1;" mydb

Suggested fix:
No idea, looks like a Catch22 situation with the current "create dummy table, then drop dummy table and create view" approach ...

Thank you for the report.

Verified as described.

I'll repeat what I have written elsewhere, :-)

The 'dummy tables' was a stupid idea from the beginning.  A view_checks variable similar to foreign_key_checks is required to solve all the issues reported with failures when restoring VIEWs.