restoring navicat backup.

[secondattack]

hey guys i'm trying to restore a navicat backup cause i just upgraded the system that hosts my world. i keep hitting a wall with this error.

[Msg] 1305 - FUNCTION peq.GetMobTypeByName does not exist

is my backup screwed? i dumped the whole peq db before i put the new hardware in so i could try to restore from that, was just thinking it go much faster this way.. any ideas here would be great, thnx.

[lockjaws]

You can manually add the sql part, make sure to check and change the root and localhost parts to your username and host if you need too.

DROP FUNCTION IF EXISTS `GetMobTypeByName`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `GetMobTypeByName`(mobname VARCHAR(64)) RETURNS char(1) CHARSET latin1
BEGIN
    DECLARE Result CHAR(1);

    SET Result = NULL;

    IF (select id from character_ where name = mobname) > 0 THEN
      SET Result = 'C';
    ELSEIF (select BotID from bots where Name = mobname) > 0 THEN
      SET Result = 'B';
    END IF;

    RETURN Result;
END;;
DELIMITER ;

[secondattack]

because of the sheer number of errors i was getting from the navicat back i started to restore from a dump. It got all the way to the end and gave this error.

[Err] 1305 - FUNCTION peq.GetMobTypeByName does not exist
[Err] CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vwgroups` AS select `g`.`groupid` AS `groupid`,`GetMobTypeByName`(`g`.`name`) AS `mobtype`,`g`.`name` AS `name`,`g`.`charid` AS `mobid`,ifnull(`c`.`level`,`b`.`BotLevel`) AS `level` from ((`group_id` `g` left join `character_` `c` on((`g`.`name` = `c`.`name`))) left join `bots` `b` on((convert(`g`.`name` using utf8) = `b`.`Name`)));
[Msg] Finished - Unsuccessfully
--------------------------------------------------

i do not understand why this wouldn't work correctly this dump was made moments before i pulled the plug on my old ass hardware, saved to a flash drive and copied to the new hdd.

*edit*

so the world runs, and all the player data seems to have been restored and everything seems to be playing fine.. i sourced in the dump again but selected continue on errors, the above is the only one that i got at like 99%.. i am worried about that error however, what will it effect?

[lockjaws]

I'm not a expert but for a server build with bots you require

Views:

vwbotcharactermobs
vwbotgroups
vwgroups
vwguildmembers

Functions:

GetMobType
GetMobTypeById
GetMobTypeByName

These also break guild lists in guild management, Possibily other things too.

so check views and function underneath tables in your databse.
if you are having issues with bots and such, copy and paste the missing ones
and run them as a query.

-- ----------------------------
-- View structure for `vwbotcharactermobs`
-- ----------------------------
DROP VIEW IF EXISTS `vwbotcharactermobs`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vwbotcharactermobs` AS select 'C' AS `mobtype`,`c`.`id` AS `id`,`c`.`name` AS `name`,`c`.`class` AS `class`,`c`.`level` AS `level`,`c`.`timelaston` AS `timelaston`,`c`.`zoneid` AS `zoneid` from `character_` `c` union all select 'B' AS `mobtype`,`b`.`BotID` AS `id`,`b`.`Name` AS `name`,`b`.`Class` AS `class`,`b`.`BotLevel` AS `level`,0 AS `timelaston`,0 AS `zoneid` from `bots` `b`;

-- ----------------------------
-- View structure for `vwbotgroups`
-- ----------------------------
DROP VIEW IF EXISTS `vwbotgroups`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vwbotgroups` AS select `g`.`BotGroupId` AS `BotGroupId`,`g`.`BotGroupName` AS `BotGroupName`,`g`.`BotGroupLeaderBotId` AS `BotGroupLeaderBotId`,`b`.`Name` AS `BotGroupLeaderName`,`b`.`BotOwnerCharacterID` AS `BotOwnerCharacterId`,`c`.`name` AS `BotOwnerCharacterName` from ((`botgroup` `g` join `bots` `b` on((`g`.`BotGroupLeaderBotId` = `b`.`BotID`))) join `character_` `c` on((`b`.`BotOwnerCharacterID` = `c`.`id`))) order by `b`.`BotOwnerCharacterID`,`g`.`BotGroupName`;

-- ----------------------------
-- View structure for `vwgroups`
-- ----------------------------
DROP VIEW IF EXISTS `vwgroups`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vwgroups` AS select `g`.`groupid` AS `groupid`,`GetMobTypeByName`(`g`.`name`) AS `mobtype`,`g`.`name` AS `name`,`g`.`charid` AS `mobid`,ifnull(`c`.`level`,`b`.`BotLevel`) AS `level` from ((`group_id` `g` left join `character_` `c` on((`g`.`name` = `c`.`name`))) left join `bots` `b` on((`g`.`name` = `b`.`Name`)));

-- ----------------------------
-- View structure for `vwguildmembers`
-- ----------------------------
DROP VIEW IF EXISTS `vwguildmembers`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vwguildmembers` AS select 'C' AS `mobtype`,`cm`.`char_id` AS `char_id`,`cm`.`guild_id` AS `guild_id`,`cm`.`rank` AS `rank`,`cm`.`tribute_enable` AS `tribute_enable`,`cm`.`total_tribute` AS `total_tribute`,`cm`.`last_tribute` AS `last_tribute`,`cm`.`banker` AS `banker`,`cm`.`public_note` AS `public_note`,`cm`.`alt` AS `alt` from `guild_members` `cm` union all select 'B' AS `mobtype`,`bm`.`char_id` AS `char_id`,`bm`.`guild_id` AS `guild_id`,`bm`.`rank` AS `rank`,`bm`.`tribute_enable` AS `tribute_enable`,`bm`.`total_tribute` AS `total_tribute`,`bm`.`last_tribute` AS `last_tribute`,`bm`.`banker` AS `banker`,`bm`.`public_note` AS `public_note`,0 AS `alt` from `botguildmembers` `bm`;

and this for functions

-- ----------------------------
-- Function structure for `GetMobType`
-- ----------------------------
DROP FUNCTION IF EXISTS `GetMobType`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `GetMobType`(mobname VARCHAR(64)) RETURNS char(1) CHARSET latin1
BEGIN
    DECLARE Result CHAR(1);

    SET Result = NULL;

    IF (select count(*) from character_ where name = mobname) > 0 THEN
      SET Result = 'C';
    ELSEIF (select count(*) from bots where Name = mobname) > 0 THEN
      SET Result = 'B';
    END IF;

    RETURN Result;
END;;
DELIMITER ;

-- ----------------------------
-- Function structure for `GetMobTypeById`
-- ----------------------------
DROP FUNCTION IF EXISTS `GetMobTypeById`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `GetMobTypeById`(mobid INTEGER UNSIGNED) RETURNS char(1) CHARSET latin1
BEGIN
    DECLARE Result CHAR(1);

    SET Result = NULL;

    IF (select id from character_ where id = mobid) > 0 THEN
      SET Result = 'C';
    ELSEIF (select BotID from bots where BotID = mobid) > 0 THEN
      SET Result = 'B';
    END IF;

    RETURN Result;
END;;
DELIMITER ;

-- ----------------------------
-- Function structure for `GetMobTypeByName`
-- ----------------------------
DROP FUNCTION IF EXISTS `GetMobTypeByName`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `GetMobTypeByName`(mobname VARCHAR(64)) RETURNS char(1) CHARSET latin1
BEGIN
    DECLARE Result CHAR(1);

    SET Result = NULL;

    IF (select id from character_ where name = mobname) > 0 THEN
      SET Result = 'C';
    ELSEIF (select BotID from bots where Name = mobname) > 0 THEN
      SET Result = 'B';
    END IF;

    RETURN Result;
END;;
DELIMITER ;