Character Purge Tool?

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