Description:
Created script using the MySQL Administrator Backup functionality. The backup includes a single view. When running this script through MySQL Query Browser the script fails during the creation of the script with the following error:
Line 347 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
/*!40101 SET SQL MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@O' at line 21
ErrorNr.=1064
This script has not been modified at all this is the error that is generated from the script that is exported from MySQL Administrator.
How to repeat:
The following is the section of the script that creates the view. Note this is the very last thing in the script after the tables have been created, there is nothing after the last statement.
--
-- View structure for view `trendx`.`alltagsview`
--
DROP VIEW IF EXISTS `alltagsview`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `trendx`.`alltagsview`
AS select
`trendx`.`tag`.`Id` AS `Id`,`trendx`.`tag`.`Name` AS `Name`,`trendx`.`tag`.`Description` AS `Description`,`trendx`.`tag`.`Type` AS `Type`,
`trendx`.`tag`.`DataSrcType` AS `DataSrcType`,`trendx`.`tag`.`Security` AS `Security`,`trendx`.`tag`.`ExternalRefs` AS `ExternalRefs`,
`trendx`.`tag`.`ReadOnly` AS `ReadOnly`,`trendx`.`tag`.`ParentId` AS `ParentId`,`trendx`.`tag`.`ParentType` AS `ParentType`,
`trendx`.`tag`.`Retentive` AS `Retentive`,`trendx`.`anatag`.`NativeType` AS `AnaNativeType`,`trendx`.`anatag`.`ValueType` AS `AnaValueType`,
`trendx`.`anatag`.`MinValue` AS `AnaMinValue`,`trendx`.`anatag`.`MaxValue` AS `AnaMaxValue`,`trendx`.`anatag`.`InitValue` AS `AnaInitValue`,
`trendx`.`anatag`.`Scale` AS `AnaScale`,`trendx`.`anatag`.`Offset` AS `AnaOffset`,`trendx`.`anatag`.`Units` AS `AnaUnits`,
`trendx`.`digtag`.`OffLabel` AS `DigOffLabel`,`trendx`.`digtag`.`OnLabel` AS `DigOnLabel`,`trendx`.`digtag`.`InitValue` AS `DigInitValue`,
`trendx`.`strtag`.`Length` AS `StrLength`,`trendx`.`strtag`.`InitValue` AS `StrInitValue`,
`trendx`.`blktag`.`ElementSize` AS `BlkElementSize`,`trendx`.`blktag`.`NumElements` AS `BlkNumElements`,
`trendx`.`blktag`.`InitValue` AS `BlkInitValue`,`trendx`.`devsrc`.`DevAddress` AS `DevAddress`,`trendx`.`syssrc`.`ItemName` AS `SysItemName`,
`trendx`.`syssrc`.`ItemIndex` AS `SysItemIndex`
from ((((((`trendx`.`tag`
left join `trendx`.`anatag` on((`trendx`.`tag`.`Id` = `trendx`.`anatag`.`Id`)))
left join `trendx`.`digtag` on((`trendx`.`tag`.`Id` = `trendx`.`digtag`.`Id`)))
left join `trendx`.`strtag` on((`trendx`.`tag`.`Id` = `trendx`.`strtag`.`Id`)))
left join `trendx`.`blktag` on((`trendx`.`tag`.`Id` = `trendx`.`blktag`.`Id`)))
left join `trendx`.`devsrc` on((`trendx`.`tag`.`Id` = `trendx`.`devsrc`.`Id`)))
left join `trendx`.`syssrc` on((`trendx`.`tag`.`Id` = `trendx`.`syssrc`.`Id`)));
/*!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 */;
What is interesting is that if I remove all of the SET statements and execute the script it executes fine and the view is created. So I don't think it is the create view per-se that is causing the problem, but something interpreting the SETS after the create view......
