Bug #26587 Error on restoring a backup with views
Submitted: 23 Feb 2007 9:38 Modified: 26 Feb 2007 16:02
Reporter: Martin Huber Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:1.2.9 OS:Windows (Windows 2003)
Assigned to: Peter Lavin CPU Architecture:Any

[23 Feb 2007 9:38] Martin Huber
Description:
If I create a backup from a database with a view and I want to restore the backup I get this error: MySQL Error Nr.1050-Table 'testview' already exists.

How to repeat:
Create database, table and view:

CREATE DATABASE test;

CREATE TABLE test.testtable
(
  testtable_id INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  testtable_name VARCHAR(45) NOT NULL,
  PRIMARY KEY (`testtable_id`)
)
ENGINE = InnoDB;

INSERT INTO test.testtable (testtable_id, testtable_name) VALUES (1, "test");

CREATE VIEW test.testview AS SELECT * FROM test.testtable;

Backup this database and restore it (with MySQL Administrator):

1. Create new project
2. Put the database test in the backup content
3. Set the following options:
   - InnoDB OnlineBackup
   - Complete backup: Yes
   - Backup type: SQL Files
   - Complete INSERTs, Comment, Disable keys: Yes (all other options: No)
4. Execute the backup
5. Delete the database test
6. Restore the database test with the option Create database(s) if they don't exist
7. Error: The MySQL Server returned this Error: MySQL Error Nr.1050-Table 'testview' already exists

Suggested fix:
Here is the dump from the sql file. First the view will be restored as a table (I think this is the problem) then as a view:

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.27-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Create schema test
--

CREATE DATABASE IF NOT EXISTS test;
USE test;

--
-- Temporary table structure for view `testview`
--
CREATE TABLE `testview` (
  `testtable_id` int(10) unsigned,
  `testtable_name` varchar(45)
);

--
-- Definition of table `testtable`
--
CREATE TABLE `testtable` (
  `testtable_id` int(10) unsigned NOT NULL auto_increment,
  `testtable_name` varchar(45) NOT NULL,
  PRIMARY KEY  (`testtable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `testtable`
--

/*!40000 ALTER TABLE `testtable` DISABLE KEYS */;
INSERT INTO `testtable` (`testtable_id`,`testtable_name`) VALUES 
 (1,'test');
/*!40000 ALTER TABLE `testtable` ENABLE KEYS */;

--
-- Definition of view `testview`
--
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS select `testtable`.`testtable_id` AS `testtable_id`,`testtable`.`testtable_name` AS `testtable_name` from `testtable`;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
[23 Feb 2007 10:40] MySQL Verification Team
Thank you for the bug report. Verified as described.

The MySQL Server returned this Error:
MySQL Error Nr.1050-Table 'testview' already exists
[26 Feb 2007 8:58] Mike Lischke
It's not a bug, it's a feature :-) Actually, if you ommit the "add DROP" option (for whatever reason) then it cannot work. This would be the same if the schema already existed. You could not create the tables then. Is there a good reason why you leave out the DROP statements? They are particularly important for views, because, in order to resolve cross reference issues, views are created in two steps (first as dummy tables, then as real views) and hence *require* the DROP statements. Otherwise the dummy tables could not be removed (and tables and views share the same name space). 

I'll forward this issue also to the docs team, so they can make a note.
[26 Feb 2007 16:02] Peter Lavin
Documented that the DROP statement is a requirement when the backup project includes views.