Bug #30262 Backup of Views creates a file containing create table statements of view defs
Submitted: 7 Aug 2007 1:23 Modified: 7 Aug 2007 7:04
Reporter: Craig Everard Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Administrator Severity:S3 (Non-critical)
Version:1.2.12 OS:Windows (2003 Server R2)
Assigned to: CPU Architecture:Any
Tags: administrator, backups, create, restore, Views

[7 Aug 2007 1:23] Craig Everard
Description:
When creating an SQL file using the backup project facility in the Administrator application, if I wish to create a backup file of just the view definitions, the file that gets created contains sql "CREATE TABLE" statements for tables using the names of the views that I have requested in the backup. If you attempt to restore the views from the backup file, then tables of the same name as the views get created, in addition to the actual view definitions. I then end up with a view and a table of the same name, where the table didn't exist in the original backed-up schema.

How to repeat:
Use or create a backup project for a schemata that contains both tables and views.
On the "Backup Project" tab, in the backup content, data directory de-select all tables in the schemata and make sure that only the VIEWS are checked.
On the "Advanced Options" tab I have the following selections (all other options not selected):
-Normal Backup
-Backup type = SQL Files
-Add drop statements
-Comment

Save the project and Execute Backup now to a local file.

Open the file and examine the SQL statements. You should note there are erroneous CREATE TABLE statements for your view names as well as the correct CREATE VIEW statements.
[7 Aug 2007 1:28] Craig Everard
Example of the backup file created

Attachment: SCF P Views.sql (application/octet-stream, text), 61.20 KiB.

[7 Aug 2007 1:29] Craig Everard
screen shot of backup project

Attachment: mysql-admin-views.JPG (image/jpeg, text), 121.47 KiB.

[7 Aug 2007 4:46] Valeriy Kravchuk
Thank you for a problem report. Are you really sure that:

"I then end up with a view and a table of the same name, where the table didn't exist in the original backed-up schema."

Please, check, by restoring your sql file to a new, empty database. Note that you have DROP TABLE v1 before each and every CREATE VIEW v1 in your file.
[7 Aug 2007 5:19] Craig Everard
OK, I know what has happened here, my apologies. The behaviour must have changed since I upgraded to the latest version of Administrator. In my original test (and all previous backups) I did not have the "Add DROP statements" option checked. Consequently when the backup runs against views, it appears to create a temporary table to build the view against, but doesn't drop in at the end of the script execution. The file I sent you I created after ticking the drop option and so we have DROP statements which would solve my problem. My previous scripts were separated into different files for tables, data and views and carefully ordered so that errors were avoided when building the views. Thanks for your time, by adding the DROP option the problem should be solved.