Another Bot Zoning question

[Gaflack]

I have been looking over the forums about a bot zoning issue and would love any input before I start playing with the database.

Windows Server works great.

Move to new hardware (dump database, do install, copy Server folder over, source database dump).

All works great but bots do not zone.

This was the second time this has happened. Last time I sourced the load_bots.sql to fix per some postings on the forums (killed all the bot equipment tho).

Questions:
Is there something that I can do to solve the bot zoning issue other than sourcing the load_bots.sql.

Also, since my sql is really stale (working to fix that), should not a database dump (via mysqldump) and source pull in the correct info.

Any input will be appreciated before I start my experimental phase.

Thanks,

Michael

[c0ncrete]

i've only ever had an issue with bots zoning if two clients are in a group with bots, and that's been an issue for quite some time.

[bad_captain]

Are the bots in your group, or in an outside botgroup? I don't believe botgroups have ever zoned.

Check the logs to see if anything shows up, or check the changelog to see if there's any other sql that is missing.

[Gaflack]

Bots are in my group.

Bot-groups and 2 client + Bots have never zoned for me either.

Bots in group did zone until I just re-did Server.

Nothing in logs.

Main question is: Am I missing something about the Database. I do a mysqldump of the database then source it back in (this is from a working Server). Only issue is bots zoning.

Any input on that question will be appreciated.

Now that I am home I will mess around with the test server, get it to fail (no bot zoning) and try sourcing back in the load_bot.sql. Last time I did that it killed Bot equipment so I will see about trying to back up those tables.

Thanks for the replies,

Michael

[Lillu]

Hello Michael,

I believe you are missing some SQL views, vwbotgroups, vwbotcharactermobs, etc.

The lack of those can result in bots not zoning. Hope it helps!

[bad_captain]

Yeah, besides the load_bots.sql, double check the changelog and svn sql folder for bot related sql files. I'm not sure they all were incorporated into the load_bots file on peq, since they don't use bots.

[Gaflack]

Thanks for the post, I will hit the test server this week and see if I can figure out what to re-source etc. to get the bots working.

I really want to make sure that my Backup-Restore process is correct. Is there anything I am missing about that?

Rebuild server, install apps, copy Server folder and source sqldump.

Thanks,

Michael

[bad_captain]

There may be an option to backup views or not? I'm not as familiar with MySQL, so you may want to double-check mysqldump or sourcing options.

[cavedude]

Mysqldump will dump Views if the whole database is dumped, but it will not if individual tables are. And unfortunately, there are no options for dealing with Views directly. It's either all or nothing.

[Gaflack]

Yea, I am just doing a full dump of the database and sourcing it back in, so I guess my process is not to screwed-up.

I will do a trial and error test sourcing the various tables to see which if any of them solve the bot issue. Then maybe I can figure out what I need to do to get a restore up and running without jumping thru hoops.

Thanks again for the posts, refreshing the old brain cells with every hack into the database.

Michael

[Lillu]

Originally Posted by Gaflack

Yea, I am just doing a full dump of the database and sourcing it back in, so I guess my process is not to screwed-up.

I will do a trial and error test sourcing the various tables to see which if any of them solve the bot issue. Then maybe I can figure out what I need to do to get a restore up and running without jumping thru hoops.

Thanks again for the posts, refreshing the old brain cells with every hack into the database.

Michael

Once again, you are missing views, not tables. Trust me. It will fix your problem. Been there, done that.

[Gaflack]

Originally Posted by Lillu

Once again, you are missing views, not tables. Trust me. It will fix your problem. Been there, done that.

Thanks Lillu, my old memory thought that the Views were just Temporary Tables created from physical tables in the database. I guess it is time to pull the book off the shelf and stop just poking around hoping to find the answer

Thanks again,

Michael

[Gaflack]

Compare between working server (bots zone) and server where bots fail to zone shows the VIEW:

vwgroups

is missing from the server.

Time to get out in traffic and head home so plenty time for me to think about it.

Thanks again for the replies (Perl was so much easier to pick back up),

Michael

[Gaflack]

Just posting info as I can, maybe some will find it useful.

mysqldump need to add --routines option to get the views dumped.

Did that to my working test server and get the 4 bot Views but have an issue sourcing the vmgroups VIEW.

Final Structure from dump:

-- Temporary table structure for view `vwgroups`
--

DROP TABLE IF EXISTS `vwgroups`;
/*!50001 DROP VIEW IF EXISTS `vwgroups`*/;
/*!50001 CREATE TABLE `vwgroups` (
  `groupid` int(4),
  `mobtype` varchar(1),
  `name` varchar(64),
  `mobid` int(4),
  `level` int(8) unsigned
) ENGINE=MyISAM */;


-- Final view structure for view `vwgroups`
--

/*!50001 DROP TABLE `vwgroups`*/;
/*!50001 DROP VIEW IF EXISTS `vwgroups`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 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`))) */;

The other 3 VIEW's get created fine but dies here, starts with "GetMobTypeByName".

Will look over skema and see what I come up with. At least I think the mysqldumps with the --routines will get all the database so next time....

Michael

[bad_captain]

Besides the views (I have 5 - 2 bots ones, groups, and guilds, plus 1 for mercs), there are also 2 routines, which, by the sound of it, should be included with --routines. Make sure they are added before the view creation. They are:

delimiter $$

CREATE DEFINER=`root`@`localhost` FUNCTION `GetMobTypeById`(mobid INTEGER UNSIGNED) RETURNS char(1) CHARSET utf8
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$$

and

delimiter $$

CREATE DEFINER=`root`@`localhost` FUNCTION `GetMobTypeByName`(mobname VARCHAR(64)) RETURNS char(1) CHARSET utf8
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$$