[Request] Account & Char wipe

[Betaman2k]

Heya

who can make me a Sql script for this function ( latest mangos core )

I wanna cleanup all accounts & chars & guilds and gabage data files ( spells,bags etc ) from X day

example

cleaunp start from 15.02.2010 ( all chars + accounts etc are protected after 15.02.2010, from 14.02.2010 - 14.02.2009 are wiped all datas )

i hope u understand me

and i hope the great community can help me with the sql script for the latest core

regards

betaman

[VladimirMangos]

Syntax: .character deleted old [#keepDays]

Completely deletes all characters with deleted time longer #keepDays. If #keepDays not provided the used value from mangosd.conf option 'CharDelete.

KeepDays'. If referenced config option disabled (use 0 value) then command can't be used without #keepDays.

[Betaman2k]

hmmm

accounts delete too ?

i try ingame with this command

.character deleted old 180

he sayed this command not avaible only list and restore

[VladimirMangos]

No, accounts not deleted. Maybe similar account command can be writed: deelted accound that have all characters older some time (in days).

Hmm, But for account maybe better own command base at last login time. Because this more clear in case recently added accounts without characters for example.

[Betaman2k]

this comamnd dont work

.character deleted old 180 ( ingame ) i check the table from my mysql all ok, very strange this bug or wrong command ?

but sql script is the best way, i think

regards

betaman2k

[VladimirMangos]

this is security not safe commands, by default allowed only from console.

[Betaman2k]

i try on console ( telnet ) no effect no delete chars

[Mordaunt]

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

[Betaman2k]

yeah iam very interesting on a sql script but works for the latest core too ?

[Mordaunt]

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`);

[Betaman2k]

yeah thx

mysql 100 % load, wipes my chars, nice

ops i see one error what is wrong

ERROR 1146 (42S02): Table 'chars.account' doesn't exist

i used this sql script on my char DB, is that wrong ?

[Diablox]

Here is alternative way to delete old accounts

DELETE FROM `realmd`.`account` WHERE DATE_SUB(CURDATE(),INTERVAL 365 DAY) > `last_login`;

On the first look at your character_* query's you should do this one

DELETE FROM `character_social` WHERE `friend` NOT IN( SELECT `guid` FROM `characters` );

Yours item delete query is very slow (at least for me). This is my version

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`;

[herrtrigger]

ops i see one error what is wrong

ERROR 1146 (42S02): Table 'chars.account' doesn't exist

i used this sql script on my char DB, is that wrong ?

|

|

|

v

-- 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.

[Mordaunt]

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`;

[Betaman2k]

is the last version better ( faster ) ?

[kbz]

i got a feeling that this is what CMSG_GM_NUKE_ACCOUNT is supposed to do... (may be mistaken)

[VladimirMangos]

and how you will can use it when none from us have special GM client version?

[Mordaunt]

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

[DasBlub]

and how you will can use it when none from us have special GM client version?

maybe he has access to that special client?

but there is at least one project out there which implements a client for mangos (forgot its name, but it's somewhere around in this forum), so they could implement any OPCode they want, right?

[VladimirMangos]

Ofc...