Mordaunt

Awesome work guys! Soon (within two weeks I guess) I will start the tests, but is there something particularly we need to focus on? In other words what kind of feedback do you want from me?

Just download the source from googlecode: https://code.google.com/p/mangoswebv3/

WoW! This is awesome Wilson! Good job

is the last version better ( faster ) ?

Last version is more complete and faster. One more query regarding character_social and the item_instance query has been switched with the query from Diablox which is much faster. The first post I made is not bad, but the second is more complete

Thanks for the addition Diablox!

So this will be the complete cleanup query with your additions.

First query to run on Realmd database:

-- Delete all accounts which haven't been used after 1 january 2010(change date which fits yours ofcourse).
-- For various reasons I don't want to delete the accounts which has a permanent ban. If you don't care you can just delete the AND part till the end.
-- Select your Realmd database ofcourse.
DELETE FROM `account` WHERE `last_login` < TIMESTAMP('2010-01-01') AND `id` NOT IN (SELECT `id` FROM `account_banned` WHERE (`bandate` = `unbandate`));

Second set of queries to run on the Character Database:

-- All queries beneath are for the character Database.
-- Delete all characters where no account exist for (you did delete them in the first query)
DELETE FROM `characters` WHERE `account` NOT IN(SELECT `id` FROM `realmd`.`account`);

-- Delete all account_data which has no account to connect to.
DELETE FROM `account_data` WHERE `account` NOT IN(SELECT id FROM `realmd`.`account`);

-- Delete all character_* records which has no Character to relate to.
DELETE FROM `character_account_data` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_achievement` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_achievement_progress` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_action` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_aura` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_battleground_data` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_declinedname` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_equipmentsets` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_gifts` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_glyphs` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_homebind` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_instance` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_inventory` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_pet` WHERE owner NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_pet_declinedname` WHERE owner NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus_daily` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus_weekly` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus_monthly` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_reputation` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_skills` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_social` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_social` WHERE `friend` NOT IN( SELECT `guid` FROM `characters` );
DELETE FROM `character_spell` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_spell_cooldown` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_stats` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_talent` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_ticket` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_tutorial` WHERE account NOT IN(SELECT id FROM `realmd`.`account`);

-- Delete Guild related records. The first query deletes guild_members which has no character to relate to.
DELETE FROM `guild_member` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `guild` WHERE (SELECT COUNT(*) FROM `guild_member` WHERE `guildid` = `guild`.`guildid`) = 0;         
DELETE FROM `guild_bank_eventlog` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_bank_item` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_bank_right` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_bank_tab` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_eventlog` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_member` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_rank` WHERE guildid NOT IN(SELECT guildid FROM `guild`);

-- Delete pet related records
DELETE FROM `pet_aura` WHERE guid NOT IN(SELECT id FROM `character_pet`);
DELETE FROM `pet_aura` WHERE caster_guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `pet_spell` WHERE guid NOT IN(SELECT id FROM `character_pet`);
DELETE FROM `pet_spell_cooldown` WHERE guid NOT IN(SELECT id FROM `character_pet`);


-- Delete items which is not related to any existing records in the DB. Posted by Diablox.
CREATE TABLE `tmp_table` (
 `guid` bigint unsigned NOT NULL,
 PRIMARY KEY (`guid`)
) ENGINE=InnoDB;
REPLACE INTO `tmp_table` SELECT      `item` FROM `character_inventory`;
REPLACE INTO `tmp_table` SELECT  `itemguid` FROM             `auction`;
REPLACE INTO `tmp_table` SELECT `item_guid` FROM     `guild_bank_item`;
REPLACE INTO `tmp_table` SELECT `item_guid` FROM          `mail_items`;
REPLACE INTO `tmp_table` SELECT `item_guid` FROM     `character_gifts`;
DELETE FROM `item_instance` WHERE `guid` NOT IN( SELECT `guid` FROM `tmp_table` );
DROP TABLE IF EXISTS `tmp_table`;

Here you go. It works fine on latest revisions. But as always:

- Make a backup

- Try it on a testserver

- After the cleanup it is recommended to rebuild your indexes. There are several ways to do that. With optimize or just dump the character database and import it again. It will then rebuild your indexes.

Have fun with it

-- Delete all accounts which haven't been used after 1 january 2010(change date which fits yours ofcourse).
-- For various reasons I don't want to delete the accounts which has a permanent ban. If you don't care you can just delete the AND part till the end.
-- Select your Realmd database ofcourse.
DELETE FROM `account` WHERE `last_login` < TIMESTAMP('2010-01-01') AND `id` NOT IN (SELECT `id` FROM `account_banned` WHERE (`bandate` = `unbandate`));

-- All queries beneath are for the character Database.
-- Delete all characters where no account exist for (you did delete them in the first query)
DELETE FROM `characters` WHERE `account` NOT IN(SELECT `id` FROM `realmd`.`account`);

-- Delete all account_data which has no account to connect to.
DELETE FROM `account_data` WHERE `account` NOT IN(SELECT id FROM `realmd`.`account`);

-- Delete all character_* records which has no Character to relate to.
DELETE FROM `character_account_data` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_achievement` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_achievement_progress` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_action` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_aura` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_battleground_data` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_declinedname` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_equipmentsets` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_gifts` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_glyphs` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_homebind` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_instance` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_inventory` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_pet` WHERE owner NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_pet_declinedname` WHERE owner NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus_daily` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus_weekly` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_queststatus_monthly` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_reputation` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_skills` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_social` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_spell` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_spell_cooldown` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_stats` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_talent` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_ticket` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `character_tutorial` WHERE account NOT IN(SELECT id FROM `realmd`.`account`);

-- Delete Guild related records. The first query deletes guild_members which has no character to relate to.
DELETE FROM `guild_member` WHERE guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `guild` WHERE (SELECT COUNT(*) FROM `guild_member` WHERE `guildid` = `guild`.`guildid`) = 0;         
DELETE FROM `guild_bank_eventlog` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_bank_item` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_bank_right` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_bank_tab` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_eventlog` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_member` WHERE guildid NOT IN(SELECT guildid FROM `guild`);
DELETE FROM `guild_rank` WHERE guildid NOT IN(SELECT guildid FROM `guild`);

-- Delete pet related records
DELETE FROM `pet_aura` WHERE guid NOT IN(SELECT id FROM `character_pet`);
DELETE FROM `pet_aura` WHERE caster_guid NOT IN(SELECT guid FROM `characters`);
DELETE FROM `pet_spell` WHERE guid NOT IN(SELECT id FROM `character_pet`);
DELETE FROM `pet_spell_cooldown` WHERE guid NOT IN(SELECT id FROM `character_pet`);


-- Delete items which is not related to any existing records in the DB.
-- Warning: This query will run very long. On my database almost an hour.
DELETE FROM `item_instance` WHERE guid NOT IN(SELECT item FROM `character_inventory`) AND guid NOT IN(SELECT item_guid FROM `guild_bank_item`) AND guid NOT IN(SELECT item_guid FROM `mail_items`);

I did a cleanup a while ago. If you are interested I can post the whole SQL for you and translate the comments in the file to English. Just say the words

I guess you have done it already but just to be sure...GDB is installed? You are running the script/mangos as root and you compiled with --with-debug-info?

setting "GridUnload = 0" increases stability, but breaks mob respawn times.

This is definitely true. I have the same issue. Is there a workaround for this? Except setting gridunload back to 1

Hmm that is kinda strange. Well I'll try to update my server and I am going to compile it in debug. This way I am sure what the global reason is for the crash if it occurs many times in de ACE_Message_Block I know enough

I have 2000+ =(... A little problem become huge problem in my environment

Is there nobody of the devs here who can help you out with a fix or something like a workaround? Darn 2000 is a little few more Did you consider a rollback?

5 days without crash and 10720 (only 50 players) - on debian

I think you should just try and take a look how it turns out on your environment

Thanks for your fast reaction. And exactly what I wanna hear

5 days uptime is a very nice achievement. Even with 50 testers. My environment is debian (ubuntu server 64bit) based too. Although I have three times more testers, I am confident in updating the server to the latest revs. Otherwise a backup is always an option

Does this problem still occurs in latest revisions? The messages from you guys just gives me a distant to update...10 crashes a day is quite a lot. I am still using the 'old' ACE version. Do many people have problems like this or is this maybe due to a custom core?

Who is gonna enlighten me

Do you mean the join and leave messages in world?

Seems promising Ambal. Good work.

Is this patch going to interfere with the mmap redux patch or can this be applied next to yours? Just curious

another

http://paste2.org/p/961409

I have exactly the same. Is there a solution for this crash? Or any help in pointing me to a (good) direction

Is there anybody here, who can help me out? I am running Ubuntu server 64bits. I am using the restartscript one post up. But the logs are always empty.

1. GDB installed

2. configure with --with-debug-info

3. Loglevel 3 in mangos config file

4. ulimit == 0

restartscript + mangos-worldd are inside a Screen.

I really, really appreciate if someone can help me! Thanx in advance

edit: Nevermind got it working. In the script I just had to remove .* (core.*)

I thought this was fixed by Vladmir a while a go...but I could be mistaken...

Thanks Vladex. I will see what I can do

Can anyone point me to CWN's relaxed anti-cheat or FH3095's anti-cheat? Preferably on github. I've searched forum but can't find this project....

Рatch from 86 post work - 100%

For example: patch from 86 post don't contain Unit::m_TotemSlot

Does this mean that you simply removed the check of totems?

edit: Nevermind I found it

Is this patch still needed? Does it still work?

Hmm, look like "Problem 10K" ^^

Oh no! Not again the millenium bug!

I think you should delete the screenshot in your first page ;-)

hmmm. where is code ?

seem that many codes where lost after maintenance

Yeah I figured it out too. After the maintenance we lost quite some code overall in forum.