Character Purge Tool?

[thepoetwarrior]

My server has had over 100,000 characters created so far, by looking at the auto incrementing ids. We do daily backups which are now about 8 GB each.

Does anyone have or can make a tool to purge characters based on certain criteria such as last time logged in, total time played, level, etc? Need to delete stuff from obviously character table, but other tables as well such as inventory table, and quest globals table, etc.


Either as a Query for mysql or even better some GUI that can be easily configured values with preview of what will be deleted, etc? Would be very useful tool.

[joligario]

Took an initial swipe at this challenge. PEQ editor r278. http://code.google.com/p/peqphpedito...e/detail?r=278

I plan to add pages to it later for the heavily populated servers. Then integrate some of the other neat tools that are floating around.

EDIT: Went ahead and capped it at 500 results for the large servers for now.

[trevius]

It isn't exactly the answer you were looking for, but I do have a couple of suggestions that might help your potential storage issues considerably:

1. Zip up your database after doing a backup if you don't already. On my Linux box, my whole database zips down to about 50MBs lol. I think even on Windows it goes from a few GBs down to maybe 100MBs.

2. Empty out some of the larger tables from time to time that you don't make use of. One example is to empty out your character_backups table which is probably quite large considering that it holds 2 or 3 backups for each character in your character_ table. It is only used for restoring characters, so unless you regularly have to restore characters, it is probably fairly safe to empty it out from time to time to free up space.

If you use Navicat, you can right click any table and select "Object Information", which will open a box that displays some useful info including the size of the table. Then you can go through each table and see which ones are the biggest. To empty a table, just right click it and select "Empty Table". It will prompt you for if it is ok to empty that table and just make sure to read that box and that it is the table you intend to empty. It is probably best to preform a DB backup prior to doing any table emptying though just in case.

As far as table sizes go; from the review I just did of my own, here is what I see:

1. The biggest table is character_
2. Second biggest table is character_backup, but I clear mine out every couple months, so yours may be your biggest table.
3. Inventory is less than 1/10th the size of the character_ table.
4. Grid Entries is about 25MBs
5. Items is about 22MBs
6. Spells_New is about 10MBs
7. NPC_Types is only about 6MBs
8. Player Corpses is about 5MBs, but I also clear this table out occasionally since corpses don't have items on them on my server. Your table may be much larger.
9. Spawn2 is about 5MBs
10. Quest_Globals is about 3MBs, but probably varies from server to server.

All other tables were 2MBs or less (most were in the KB range).

Most of the tables mentioned in that top 10 are not going to vary too much from server to server whether they are small servers or servers with huge populations. So, the only tables I noted that are a concern are character_, character_backup, inventory, and player_corpse. You obviously wouldn't want to empty out character_ or inventory. The character_backup table should be ok to empty, and for servers with no items left on corpses, the player_corpses table should be ok as well.

I am pretty sure that you can empty out your character_backups table right now and cut your backup size down to 4GBs or less. Then if you zip that up, you should be at maybe 200MBs or less.

Hopefully that info was relevant and useful.

[SqlDev01]

The following code should insert into a table those that match the requirements. This does not delete them from the tables. Will leave that exercise to another user.

/* 
General Script Information:
	The script will attempt to find users to delete in several different levels.
	1.  By last account / char login date (the maximum of the two).
	2.  By level.
	3.  By platinum amount.
	
Instructions:
	1.  Tweak the filter criteria as you see fit.  See the lines
		below as the filter criteria is described.  You can / should
		play around with this until you decide how you want to filter this.
	2.  Run the entirety of the -uncommented- script.
	3.  You will see a list of the characters that are going to be removed.
	
	-- NOTE:  This is not added to the script yet.
	-- 3.  If you are OK with this change, highlight and run
	-- the entirety of the commented out section (the actual delets).
*/

-- drop table characterDeletionFilterCriteria
CREATE TEMPORARY TABLE IF NOT EXISTS characterDeletionFilterCriteria(
	FROM_DATE DATETIME -- Earliest date, inclusive, that this filter row should apply to.
	, TO_DATE DATETIME -- Latest date, exclusive, that this filter row should apply to.
	, MAXIMUM_CHARACTER_LEVEL INT -- Inclusive Highest level character this filter should apply to.
	, MAXIMUM_LIQUID_PLAT INT	-- Highest amount of liquid plat this filter should apply to.
);
DELETE FROM characterDeletionFilterCriteria;

-- Insert deletion filters.
INSERT INTO characterDeletionFilterCriteria
-- Very lightweight restriction while tesitng... not logged in the last 1 year and their char is <= 55 and they have less than 50k plat.
	SELECT DATE_ADD(SYSDATE(), INTERVAL -10 YEAR), DATE_ADD(SYSDATE(), INTERVAL -6 MONTH), 55, 50000

-- If they have not logged into the account or the character in 3 years and have less than a million on them get rid of them.
-- SELECT DATE_ADD(SYSDATE(), INTERVAL -10 YEAR), DATE_ADD(SYSDATE(), INTERVAL -3 YEAR), 9999, 1000000

-- If they have last logged in between 3 years and 2 years and are not at least level 70 and have less then 500k on them get rid of them.
-- UNION SELECT DATE_ADD(SYSDATE(), INTERVAL -3 YEAR), DATE_ADD(SYSDATE(), INTERVAL -2 YEAR), 70, 500000

-- If they have last logged in between 2 years and 1 year ago but are not at least level 55 and have less than 200k on them get rid of them.
-- UNION SELECT DATE_ADD(SYSDATE(), INTERVAL -2 YEAR), DATE_ADD(SYSDATE(), INTERVAL -1 YEAR), 55, 200000

-- If they have last logged in in the last year, leave them be.

;

-- Find users that at least appear to fit into the broad filter categories.
-- Temp table used to speed up subsequent queries as opposed to trying to join
--	every possible table needed right now.
CREATE TEMPORARY TABLE IF NOT EXISTS CharactersToConsiderForDeletionPendingOtherFilterCriteria(
	CHARACTER_ID INT
	, CHARACTER_NAME VARCHAR(64)
	, LastAccountAccessDate DATETIME
	, MinimumPlatAmountCharacterNeedsToAvoidDeletion INT
);
DELETE FROM CharactersToConsiderForDeletionPendingOtherFilterCriteria; 

INSERT INTO CharactersToConsiderForDeletionPendingOtherFilterCriteria
SELECT
outerchar.ID
, outerChar.NAME
, accountAccessDates.LastAccessDate
, MIN(filter.MAXIMUM_LIQUID_PLAT)
FROM
	(
		SELECT 
			c.ID AS CHARACTER_ID
			, CASE 
				WHEN aip.lastused IS NULL THEN FROM_UNIXTIME(c.timelaston)
				WHEN FROM_UNIXTIME(c.timelaston) > aip.lastused THEN FROM_UNIXTIME(c.timelaston)
				ELSE aip.lastused
			END AS LastAccessDate

		FROM
			character_ c
			INNER JOIN (SELECT accid, MAX(lastused) AS lastused FROM account_ip GROUP BY accid) aip
				ON c.account_id = aip.accid
	) accountAccessDates
	INNER JOIN character_ outerchar
		ON outerchar.id = accountAccessDates.CHARACTER_ID
	INNER JOIN characterDeletionFilterCriteria filter
		ON accountAccessDates.LastAccessDate >= filter.FROM_DATE AND accountAccessDates.LastAccessdate < filter.TO_DATE
		AND outerchar.LEVEL <= filter.MAXIMUM_CHARACTER_LEVEL
GROUP BY outerchar.ID, outerChar.NAME, accountAccessDates.LastAccessDate
;

CREATE TEMPORARY TABLE IF NOT EXISTS CharactersToBeDeleted(
	CHARACTER_ID INT
	, CHARACTER_NAME VARCHAR(64)
	, LEVEL INT
	, LastAccountAccessDate DATETIME
	, CurrentLiquidPlatinumAmount INT
);
DELETE FROM CharactersToBeDeleted; 

INSERT INTO CharactersToBeDeleted
SELECT 
	IndividualCharacterSlots.CHARACTER_ID
	, IndividualCharacterSlots.CHARACTER_NAME
	, IndividualCharacterSlots.LEVEL
	, IndividualCharacterSlots.LastAccountAccessDate
	, 
		PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
		+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
		+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
		+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
	(
		SELECT
			filter.CHARACTER_ID
			, filter.CHARACTER_NAME
			, filter.LastAccountAccessDate
			, filter.MinimumPlatAmountCharacterNeedsToAvoidDeletion
			, c.LEVEL
			
			/* Player Platinum! */
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
			
			/* Bank Platinum */
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
			
			/* Cursor Platinum */
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
			
			/* Shared Bank Platinum */
			,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1)) 
				+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1)) 
				+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1)) 
				+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
		 

		FROM 
			character_ c
			INNER JOIN CharactersToConsiderForDeletionPendingOtherFilterCriteria filter
				ON c.id = filter.character_id
	) IndividualCharacterSlots 
	WHERE MinimumPlatAmountCharacterNeedsToAvoidDeletion > (
			PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
			+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
			+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
			+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000
		);


-- select count(*) as NumberOfCharactersToBePurged from CharactersToBeDeleted;
SELECT 
	* 
FROM 
	CharactersToBeDeleted 
ORDER BY 
	CharactersToBeDeleted.LEVEL DESC
	, CurrentLiquidPlatinumAmount DESC 
LIMIT 1000;

[thepoetwarrior]

Good info, thanks very much.

Would be nice if someone can put that query into a tool to checkbox and edit fields as to a preview/purge tool.

I'll try to empty out the char backup table and zip up the backups too see if that works.

The character table is over 1.6 GB with auto increment id over 123,000 now.

[GeorgeS]

Actually this is pretty simple. I can whip this up fast, but the only problem I have so far is decoding the INT field timelaston in character_ table.
Anyone have an idea how to decode this value?
For ex. value = '1249590234' decodes how and to what?

GeorgeS

[lerxst2112]

That field is a unix_timestamp which is the number of seconds since January 1, 1970.

If I recall correctly you're using VB or something like that. I'm not sure what sort of time and date manipulation functions you have available, but one of these might help:

http://codeclimber.net.nz/archive/20...-datetime.aspx
http://www.colincochrane.com/post/20...-datetime.aspx

[GeorgeS]

Thanks, looked it up and had to find the vb rather than the .net conversion

So it's

longdate=1312991680
datestring=CDate(longdate/ 86400 + DateSerial(1970, 1, 1))

I've coded the program already, and should have a working beta tomorrow nite. Currently it works, but I am testing it tonight..

/edit
These are the tables that hold the toon info. I am planning to delete any selected toon from these tables.
If I should not touch any of these then let me know.

' "DELETE FROM character_ WHERE id=nnn"
' "DELETE FROM inventory WHERE charid=nnn"
' "DELETE FROM character_activities WHERE charid=nnn"
' "DELETE FROM character_enabledtasks WHERE charid=nnn"
' "DELETE FROM character_tasks WHERE charid=nnn"
' "DELETE FROM completedtasks WHERE charid=nnn"
' "DELETE FROM faction_values WHERE charid=nnn"
' "DELETE FROM guild_members WHERE char_id=nnn"
' "DELETE FROM group_id WHERE charid=nnn"
' "DELETE FROM char_recipe_list WHERE char_id=nnn"
' "DELETE FROM buyer WHERE charid=nnn"
' "DELETE FROM adventure_members WHERE charid=nnn"
' "DELETE FROM aa_timers WHERE charid=nnn"




GeorgeS

[joligario]

Actually, if you want to be complete:

adventure_members
adventure_stats
buyer
character_
character_activities
character_backup
character_enabledtasks
character_tasks
char_recipe_list
completed_tasks
faction_values
friends
group_id
guild_members
instance_lockout_player
inventory
keyring
mail
petitions
player_corpses
player_corpses_backup
player_titlesets
quest_globals
raid_members
reports
timers
trader
zone_flags

Obviously, some would be empty already.

[thepoetwarrior]

Wow, this is going to be awsome! Can't wait!

Hopefully we can configure the values, like everyone that hasn't logged in for 1 year, or everyone that hasn't logged in for 1 year + under level 60, etc.

So what would be all the configurable fields?

1) Last time logged in
2) Level
3) Time Played

etc?

[SqlDev01]

Would you do this just by level and last-login-time?

Just thinking about those level 70's that have level 1 banks scattered around with all their stuff on them...

-possibly- better to base it on last-login-date per account and max-level-per-account than per character?

[trevius]

It probably wouldn't be a bad idea to tie the account_ip table in there. That is the only way to tell which accounts/chars are tied to other active accounts/chars. Then, you could keep from deleting an active player's old character(s) just because they haven't used them in a long time, or an alt banker type of character.

[thepoetwarrior]

Figured delete just by character basis, and leave the account alone, but I guess someone could have a level 1 character as a "bank" account. If someone hasn't logged in their bank alt for like 1-2 years, then probably won't matter if I delete it. Would probably post the limits that would get players on the purge list before I do it so people can quickly log in their characters to save them.

I think total time played and level would be good values as well.

Maybe AA's spent/unspent, and total plat on player + bank could be useful too.

[SqlDev01]

Originally Posted by thepoetwarrior

Figured delete just by character basis

Originally Posted by thepoetwarrior

Would probably post the limits that would get players on the purge list before I do it so people can quickly log in their characters to save them.

The EZServer is probably the server with the most alternative accounts. I know that some players there have 6+ accounts... Assuming maxed account banks on each one, the user may have to log into 48 accounts to make sure that none of their accounts would be flagged for deletion.

Just something to consider...

[lerxst2112]

I know when I saw this question I logged in all my toons hoping they won't be deleted.