[PATCH] AuctionHouse Bot

[DaemonCantor]

I tried it again today and I got pretty much the same thig again but I remembered about the frozen thread thingy and I let it just sit and waited about an hour later all of a sudden it started working again, I don't know what changed or how but it updated my screen finnaly and all of the AH Auctioneers poped back in and it just started working....but the configs are right and the sql's are set for 10 items min and 50 items max but nothing in any AH. Now I'm afaid to shut it down to try and figgure out what next because the game is running again.

[Unkle Nuke]

I've noticed that it seems Windows based servers have this issue. It may even be a fault with MySQL instead of MaNGOS or the AHBot. When you have at least a few characters that make use of the auction house, the AHBot seems to hang the server when updating its tables. Particularly, it seems to be in regards to deleting expired items and items that have been won or bought out. Using MySQL Administrator, watch the threads and you'll see what I mean. This can cause attempts at logging in to hang just at the character selection screen, before it pulls up your list of characters. The reason being is that AHBot does not update until a character logs in that has used the auction house because it appears it has to get the data from the character as to what items need deleting from the auctions before it completes its cycle.

You can sometimes login by forcibly disconnecting your client and then attempting another login but this can cause AHBot to leave crashed or open tables behind that must be cleaned up with SQL Repair and Optimize. This is due to AHBot suddenly losing the character as it was pulling the data needed for the maintenance cycle.

This is all educated guess, at best, based purely on observation since I do not know enough about exactly how the various functions and modules work.

The only solution I have ever found for this is to reduce the total number of items stocked in the auction houses to a number less than 10,000. On my server, 4,000 to 6,000 seems to work best, for max number of items, with little to no lag.

If you really need to have a massive inventory in the auction houses, you may also try a fixed number by setting the minimum and maximum items to the same value. This should cause AHBot to only delete items bought by characters and itself, instead of dropping thousands of items if AHBot decides on a number lower than the max for the next cycle. I have not done this myself simply because there's no need for much more than 6,000 items from which players can choose on my (very) low pop server.

In regards to housekeeping tasks and cleanup, AHBot might need to be reworked so it allocates resources more efficiently and could suspend large operations until the server is idle. As a general rule, I bring my server online about one hour before my players are expected to log in, allowing for AHBot cycling, arena points distribution, and other maintenance.

[DaemonCantor]

I've found something that seems to help I issued these commands on the item_instance, auctionhousebot, auctionhouse, characters tables and I don't have the freeze as bad anymore

ALTER TABLE table_name ENGINE = InnoDB;

OPTIMIZE TABLE table_name;

it should look like this:

ALTER TABLE auctionhouse ENGINE = InnoDB;
OPTIMIZE TABLE auctionhouse;
ALTER TABLE auctionhousebot ENGINE = InnoDB;
OPTIMIZE TABLE auctionhousebot;
ALTER TABLE characters ENGINE = InnoDB;
OPTIMIZE TABLE characters;
ALTER TABLE item_instance ENGINE = InnoDB;
OPTIMIZE TABLE item_instance;

You can add more tables if you like or run on your other databases like tables in your mangos/world db's also with INNODB you might wanna issue a DISABLE KEYS command but I'm still researching this as to what it really is and how it might help, maybe somebody else out there with more knowledge might advise on this it would be most appreciated.

[skinlayers]

I just updated to the latest evo-X (based on MaNGOS 9613) and now I'm seeing AHbot related hangs and seg faults as well.

I noticed when I issue .ahbotoptions ahexpire it take about 30 - 60 seconds before the sql queries go out, and then the console is unresponsive for several minutes until its finished. The entire time, my hard drive activity light stays solid, so it doing *something*.

I'm testing this on Ubuntu 32-bit, and this definitely wasn't happening before. One of the commits made since 9591 (the last time I updated) must be causing the issue. I'm thinking 9611 since it effects indexes in the character DB. Or 9602 as it affects mail.

I really hope Naicisum comes back soon, or another dev is willing to step up.

UPDATE: Looking at my log, I notice everytime right before mysql goes nuts:

Player: channels cleaned up!

I wonder if there is a connection.

I also ran SHOW FULL PROCESSLIST on in mysql while my hard drive was grinding and saw a bunch of changes being made to auctionhouse and item_instance, so its definitely mysql related.

UPDATE 2: I dropped the indexes that were added in 9611, but that didn't fix it. Maybe it was one of the code changes that went with 9611.

skinlayers

[skinlayers]

Yup, its commit 9611. Not the sql update, but the actual code. I just compiled 9610 with AHbot +playerbot + ScriptDev2 and its not hanging. My hard drive is only active for a few seconds after I get "Player: channels cleaned up!"

So, my advice for now it to build with 9610 or to cherry pick commits later than 9611 into 9610.

skinlayers

Update: spoke too soon. Its acting better, but ahbotoptions ahexpire will cause it to thrash and segfault. I'm giving up for now.

[mrelfire]

Yup, its commit 9611. Not the sql update, but the actual code. I just compiled 9610 with AHbot +playerbot + ScriptDev2 and its not hanging. My hard drive is only active for a few seconds after I get "Player: channels cleaned up!"

So, my advice for now it to build with 9610 or to cherry pick commits later than 9611 into 9610.

skinlayers

Update: spoke too soon. Its acting better, but ahbotoptions ahexpire will cause it to thrash and segfault. I'm giving up for now.

Not for sure, on my side I have a freeze with playerbot and not auctionbot at the latest version

[AkerdTeh]

Not having any of the described problems on 9611; Win7 x86 running only ahbot. Server briefly hangs when running an ahexpire but this is expected behavior when dropping several thousand auctions.

[Unkle Nuke]

Could it be possible that AHBot has exposed some flaw in the MaNGOS core, regarding large table operations?

I don't know of any other core mods that make such extensive use of table I/O as AHBot. These issues with lag and hangs has affected AHBot with MaNGOS for a long time, way before this latest round of complaints, but, it has never been as severe as now.

It seems the mail system changes are not the issue, thanks to Kreegoth's observations, so now one must wonder what changes were made in the server core dealing with SQL data handling.

Does using an older or newer version of MySQL (or PGSQL) make a difference? Have your MySQL settings been optimized for best performance? Has anyone checked the MySQL forums for similar reports from other users of lags and crashes in their applications when performing large table operations?

Asking questions like these, and many more, will help to ferret out the root cause and shed some light on what possible fixes, if any, are needed.

[mrelfire]

I have tested with different combinations of AHbot on a windows X64 core i7 and on my test AHbot has no problems

[Skirnir]

So far I only see people using highly customized MaNGOS versions having this problems. If you use a core patched by someone else, throwing in dozens of custom patches and you run into a freeze, I'd say it's your very own fault. Patches like ahbot are made for a clean MaNGOS core (w/o any patches). In this thread we have a bunch of users using latest MaNGOS with ahbot w/o problems on different platforms. The way it is right now, I see a direct link between patches and crashes.

Anyway some of you will never agree to this point, it's not that popular to point at oneself if asked for whom to be blamed. So blow up this thread for 3 or 4 pages, complaining about freezes, that way this thread at least can easily be spotted ...

Skirnir

PS: "it freezes 30 - 40 seconds right after the first person logs into the world" this def. doesn't look like a ahbot failure :-\\

[vladex]

yea it is not from ahbot ,tested it yersturday without ahbot and still crashes

[DaemonCantor]

I found it!.....but how to fix now???? it's in the x64 windows build... some how ACE is messing up the packett transport between the server and the Client, I'm getting between 70 to 300 errors from ACE during compile and it's gets worse as the core rev's change. these are errors and warnings of data loss because of argument switches, as of rev 9618 I can't even compile SD2 into the core with out total a total fail. I've downloaded the newest rev of the TAO-ACE libraries but haven't a clue as to how to update the old ACE in the core.

[mrelfire]

I found it!.....but how to fix now???? it's in the x64 windows build... some how ACE is messing up the packett transport between the server and the Client, I'm getting between 70 to 300 errors from ACE during compile and it's gets worse as the core rev's change. these are errors and warnings of data loss because of argument switches, as of rev 9618 I can't even compile SD2 into the core with out total a total fail. I've downloaded the newest rev of the TAO-ACE libraries but haven't a clue as to how to update the old ACE in the core.

If you found it, open a bug report

[DaemonCantor]

ok how do I do that...sorry never need to in the past

[Unkle Nuke]

Actually, it wasn't already done, mrelfire. That topic you linked is in regard to some spells, especially mage Missle Barrage, freezing the client on core builds after revision 9606.

DaemonCantor's posting about TAO-ACE does adequately cover the issue, though. It's now up to everyone to report in that topic so the devs can take notice and work this out. I'm virtually certain this is not an issue that is caused by AHBot.

[skinlayers]

Then maybe I'm having a different issue then the rest of you. I just did some extensive testing and my problem is without a doubt caused by ahbot being enabled. I compiled MaNGOS 9620 + SD2 1653 + Ahbot, and rebuilt my DBs from scratch using UDB and I'm still having hangs on login & logout as well as seg faults caused by the anti-freeze mechanism. If I disable ahbot in mangosd.conf the problem goes away. I also installed mytop so I can watch what the mysql server is doing. Sure enough, every time my hard drive thrashes, tables are being created/deleted in characters->auctionhouse or characters->item_instance. The hangs occur whenever you try to login/logout or quit mangos-worldd while its processing those queries. Using the default setting of processing 200 items at a time, which generates 600 items per minute (200 x 3 AHs), it takes ~25 sec for it to complete a cycle until it reaches the minimum number of items as set in characters->auctionhousebot. This is waaaaaaay longer that it has in the past. Since I started using AHbot, I've had it set to process 1000 items per cycle (3000 queries per minute). At that rate, with the slow sql processing, it doesn't have time to finish the previous cycle before starting a new one, so the queries get backed up, which after a while will cause anti-freeze to segfault mangos-worldd. I think UnkleNuke is right that the problem isn't with AHbot itself, but is a problem in MaNGOS being exposed by its heavy use of sql queries.

On Ubuntu 9.10 32-bit test system (Core 2 Duo 3GHz 2GB RAM). I'm about to test it on my Debian Lenny 64-bit production machine and see if I get the same results. I'm guessing this problems was introduced a while back but since my AH was already stocked, I wasn't noticing it, where as with my testbed, the AH starts out empty..

skinlayers

[X-Savior]

So what is happening with AHBOT? IS it working and is there a proper patch for the core.

[xeross155]

So what is happening with AHBOT? IS it working and is there a proper patch for the core.

Starting my AHBot branch now to see what happens.

Seems I can't even get it to put stuff into the auction house

[Skirnir]

Well guys, this commit: this breaks ahbot. At my console this pops up before a crash:

SQL: SELECT guid, data, name, position_x, position_y, position_z, map, totaltime, leveltime, at_login, zone, level FROM characters WHERE guid = '30'
query ERROR: Unknown column 'data' in 'field list'

this is caused by removing the 'data' blob in mangos. Minimal load function (ahbot uses it, but mangos already removed it's code a while ago) searches for the data table.

{
       //                                        0     1     2     3           4           5           6    7          8          9         10    11
       result = CharacterDatabase.PQuery("SELECT guid, data, name, position_x, position_y, position_z, map, totaltime, leveltime, at_login, zone, level FROM characters WHERE guid = '%u'",guid);
       if (!result)
           return false;
   }

The second field is the "data" blob. Now there is no data blob, what would be required would be, make this line use all now available fields, but I'm on linux compiling takes still "ages" and I'm not quite sure if this would fix the problem or just creates new problems.

With mangos revs higher than 9630 you can't use ahbot any longer.

[xeross155]

it still works if you don't remove the data blob yet.

Somehow it won't start filling my auction house any idea why that happens ?

[kreegoth]

Forgive my stupidity but im tired LOL

Anyone test what happens when you simply remove the data from the list in player.cpp such as this?

{
       //                                        0     1     2     3           4           5           6    7          8          9         10 
       result = CharacterDatabase.PQuery("SELECT guid, name, position_x, position_y, position_z, map, totaltime, leveltime, at_login, zone, level FROM characters WHERE guid = '%u'",guid);
       if (!result)
           return false;
   }

I dont see the point in adding the other "new values" as it already doesnt load other values from the Database such as money and such on load. doesnt seem to be any real reason to add them unless it continues to have issues due to one not being present.

If not ill test it tonight

[Skirnir]

I did and I didn't run into crashes like before, but I get a constant warning ... some lines down Player.cpp Minimal Load function. I removed that part and changed numbers according to numbers of characters table and now it instantly crashes.

As said before compilation takes some time, windows and precompiled headers can't beat 'em. I won't mess around with code I don't understand, maybe I'll have a deeper look tomorrow.

[kreegoth]

What warning are you getting? and sorry but what numbers did you switch?

[Skirnir]

sLog.outError("Player #%d have broken data in `data` field. Can't be loaded for character list.",GUID_LOPART(guid)); <- this one, of course filled out. "Player ..."

and with numbers I ment this part:

- m_name = fields[2].GetCppString();

+ m_name = fields[1].GetCppString();

- Relocate(fields[3].GetFloat(),fields[4].GetFloat(),fields[5].GetFloat());

- SetLocationMapId(fields[6].GetUInt32());

+ Relocate(fields[2].GetFloat(),fields[3].GetFloat(),fields[4].GetFloat());

+ SetLocationMapId(fields[5].GetUInt32());

// the instance id is not needed at character enum

- m_Played_time[PLAYED_TIME_TOTAL] = fields[7].GetUInt32();

- m_Played_time[PLAYED_TIME_LEVEL] = fields[8].GetUInt32();

+ m_Played_time[PLAYED_TIME_TOTAL] = fields[6].GetUInt32();

+ m_Played_time[PLAYED_TIME_LEVEL] = fields[7].GetUInt32();

obvious it didn't work out as expected :-)

[Skirnir]

it still works if you don't remove the data blob yet.

Somehow it won't start filling my auction house any idea why that happens ?

hi xeross155,

the thing is, MaNGOS devs removed the data blob for reasons (maybe beyond my horizon, but they have some). That whole minimalload thing was removed since no one used it anymore or maybe even ever and I don't like carrying around all that stuff. Maybe it'll work for some weeks or months, but then you need that new structure for something else, like dual talents whatsoever (I don't know, maybe you never need it ...), then you will start looking for another ugly solution. I personally just want to avoid that.

If your tables aren't filled, you either have messed up with your database configuration or with your mangosd.conf file. Import ahbot.sql files from the patch, play with some of the configuration options, create an account, log into world once, fill the account information inside your mangosd.conf file and restart the server.

Regards

Skirnir