Jump to content

[Request] Account & Char wipe

Guest Betaman2k

By Guest Betaman2k
in Old3rd party projects

 Share

Recommended Posts

Betaman2k Advanced Member

Betaman2k

Posted
Posted

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 Advanced Member

VladimirMangos

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

VladimirMangos Advanced Member

VladimirMangos

Posted
Posted

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 Advanced Member

Betaman2k

Posted
Posted

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

Mordaunt Advanced Member

Mordaunt

Posted
Posted

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 Advanced Member

Betaman2k

Posted
Posted

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 Advanced Member

Diablox

Posted
Posted

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 Advanced Member

herrtrigger

Posted
Posted
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 Advanced Member

Mordaunt

Posted
Posted

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

Mordaunt Advanced Member

Mordaunt

Posted
Posted
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 Advanced Member

DasBlub

Posted
Posted
and how you will can use it when none from us have special GM client version?
maybe he has access to that special client? :P

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?

 Share
Go to topic listing

Contact Us

To contact us click here
You can also email us at [email protected]

Privacy Policy | Terms & Conditions

Repositories

The Link to the master list
of MaNGOS repositories:
Copyright © getMaNGOS. All rights Reserved.

This website is in no way associated with or endorsed by Blizzard Entertainment®
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Privacy Policy Terms of Use