MySQL Bugs: #18599: views backed up can't be restored

Bug #18599	views backed up can't be restored
Submitted:	29 Mar 2006 3:10	Modified:	4 Apr 2006 17:35
Reporter:	Jim Michaels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.1.7	OS:	Windows (Windows XP Pro MP)
Assigned to:		CPU Architecture:	Any

Description:
Warning: Do not know how to handle this statement at line 85101:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jmichae3`.`workhours` AS select `jmichae3`.`work`.`id` AS `id`,`jmichae3`.`work`.`name` AS `name`,`jmichae3`.`work`.`prj` AS `prj`,`jmichae3`.`work`.`tasknum` AS `tasknum`,timediff(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`) AS `elapsed`,`jmichae3`.`work`.`church` AS `church` from `jmichae3`.`work` order by `jmichae3`.`work`.`name`,`jmichae3`.`work`.`prj`,`jmichae3`.`work`.`tasknum`;
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Warning: Do not know how to handle this statement at line 85109:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jmichae3`.`workhourstot` AS select `jmichae3`.`work`.`id` AS `id`,`jmichae3`.`work`.`name` AS `name`,`jmichae3`.`work`.`prj` AS `prj`,((sum(minute(timediff(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`))) DIV 60) + sum(hour(timediff(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`)))) AS `hour`,(sum(minute(timediff(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`))) % 60) AS `minute`,`jmichae3`.`work`.`church` AS `church` from `jmichae3`.`work` group by `jmichae3`.`work`.`name`,`jmichae3`.`work`.`prj`;
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

I can't get mysql client to do a 7MB script without a disconnect & lockout, so maybe this should be bumped up to the serious level?

How to repeat:
CREATE VIEW `jmichae3`.`workhours` AS SELECT `jmichae3`.`work`.`id` AS `id`,`jmichae3`.`work`.`name` AS `name`,`jmichae3`.`work`.`prj` AS `prj`,`jmichae3`.`work`.`tasknum` AS `tasknum`,TIMEDIFF(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`) AS `elapsed`,`jmichae3`.`work`.`church` AS `church` from `jmichae3`.`work` ORDER BY `jmichae3`.`work`.`name`,`jmichae3`.`work`.`prj`,`jmichae3`.`work`.`tasknum`;
CREATE VIEW `jmichae3`.`workhourstot` AS SELECT `jmichae3`.`work`.`id` AS `id`,`jmichae3`.`work`.`name` AS `name`,`jmichae3`.`work`.`prj` AS `prj`,((SUM(MINUTE(TIMEDIFF(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`))) DIV 60) + SUM(HOUR(TIMEDIFF(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`)))) AS `hour`,(SUM(MINUTE(TIMEDIFF(`jmichae3`.`work`.`end_time`,`jmichae3`.`work`.`start_time`))) % 60) AS `minute`,`jmichae3`.`work`.`church` AS `church` from `jmichae3`.`work` GROUP BY `jmichae3`.`work`.`name`,`jmichae3`.`work`.`prj`;

Suggested fix:
modify the parser to handle what BACKUP generates.

Thank you for a problem report. Please, specify the exact MySQL server versions you had backed up from and tried to restore to?

backed up 5.0.18 (mysql5018Setup.exe - I am sure I renamed it). don't have the URL.
tried to restore to 5.0.19 GA community edition (http://dev.mysql.com/downloads/mysql/5.0.html) downloaded windows essentials x86 (http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-essential-5.0.19-win32.msi/from/http://...)

Now that I think about it, I should have downloaded the bigger one - that would have come with a manual.

Thank you for the additional information. Please, try to restore with newer version of MySQL Administrator, 1.1.9, and inform about the results.

no errors with 1.1.9

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Thank you for the feedback.