MySQL Bugs: #20715: view migration fails

Bug #20715	view migration fails
Submitted:	27 Jun 2006 8:26	Modified:	29 Jun 2006 16:16
Reporter:	Anthony Watts	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.25	OS:	Windows (win xp)
Assigned to:		CPU Architecture:	Any

Description:
performing a migration from one MySQL 5.01 d/base to another. The table migration performs OK, but the view migration fails with a syntax is not correct error. Most of the sql that maskes up the view is missing and yoiu can't cut and paste the original in.

The only way I have been able to do a migration is to backup the original and restore it over the top of the target. Not very satisfactory as the source d/base is getting large and cumbersome.

How to repeat:
2 seperate xp machines with mysql 5.01 loaded. Use the migratoin tool from either to try and migrate views from one to another. The views must already exist on the target as we are trying to update the view (e.g dev -> production).

The migration will fails with syntax error aronud the create view line.

Suggested fix:
1. better text wrapping within the tex view box.
2. Show view SQL before and after so migtration staff can assess if the "copy" is exact.
3. make window resizing work properly.

thanks.

Thank you for a bug report. Verified just as described, on a database with 2 table and 2 views. Even simplest CREATE VIEW v1 AS SELECT 1; is enough to get migration problems. The following statement is generated:

CREATE OR REPLACE VIEW `test2`.`v1` AS
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1`;

While something like:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`v1` AS select 1 AS `1`;

is needed:

mysql> CREATE 
    -> ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`v1` AS select 1 AS `1`;
Query OK, 0 rows affected (0.00 sec)

It is a bug.

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 bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html