MySQL Bugs: #13910: restore of views fails

Bug #13910	restore of views fails
Submitted:	10 Oct 2005 23:55	Modified:	27 Jul 2006 12:14
Reporter:	Carl Schwarcz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.1.3	OS:	Windows (Win XP Pro SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
MySQL server 5.01.13

backed up all schema.  Removed all MySQL software and did a clean install.

When running the restore, views were not restored.  Following errors were reported.  This current instance is configured as InnoDB only.  

THIS IS SERIOUS AS I'VE LOST MY VIEWS!

How to repeat:
see above, merely do a full restore

Thank you for a bug report. It is very easy to repeat. In mysql client do:

mysql> create database db2;
Query OK, 1 row affected (0.02 sec)

mysql> use db2;
Database changed
mysql> create table t1 (c1 int);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.03 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> exit
Bye 

Then in Administrator perform a backup of this database only, drop database in mysql client and try to restore using Administrator. You'll get:

Warning: Do not know how to handle this statement at line 47:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2`.`v1` AS select `db2`.`t1`.`c1` AS `c1` from `db2`.`t1`;
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Tha same backup script can be successfuly executed by simple "mysql <script.sql" command. So, this is MySQL Administrator's bug. Verified with 5.0.13-rc-nt and Adminsitrator 1.1.3.

Changes severity because data can be easily restored by using mysql command line client.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Related to Bug #13842.