PDA

View Full Version : Character Purge Tool?


thepoetwarrior
08-15-2011, 10:21 PM
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
08-16-2011, 06:42 AM
Took an initial swipe at this challenge. PEQ editor r278. http://code.google.com/p/peqphpeditor/source/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
08-16-2011, 11:34 AM
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
08-16-2011, 01:13 PM
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 CharactersToConsiderForDeletionPendingOtherFilterC riteria(
CHARACTER_ID INT
, CHARACTER_NAME VARCHAR(64)
, LastAccountAccessDate DATETIME
, MinimumPlatAmountCharacterNeedsToAvoidDeletion INT
);
DELETE FROM CharactersToConsiderForDeletionPendingOtherFilterC riteria;

INSERT INTO CharactersToConsiderForDeletionPendingOtherFilterC riteria
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.MinimumPlatAmountCharacterNeedsToAvoidDelet ion
, 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 CharactersToConsiderForDeletionPendingOtherFilterC riteria 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
08-18-2011, 03:17 PM
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
08-18-2011, 09:44 PM
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
08-18-2011, 10:12 PM
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/2007/07/10/convert-a-unix-timestamp-to-a-.net-datetime.aspx
http://www.colincochrane.com/post/2008/03/07/converting-a-unix-timestamp-to-system-datetime.aspx

GeorgeS
08-19-2011, 12:20 AM
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
08-19-2011, 07:03 AM
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
08-20-2011, 12:04 AM
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
08-20-2011, 09:26 AM
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
08-20-2011, 11:43 AM
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
08-20-2011, 04:18 PM
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
08-20-2011, 06:13 PM
Figured delete just by character basis
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
08-20-2011, 06:20 PM
I know when I saw this question I logged in all my toons hoping they won't be deleted.

Tabasco
08-20-2011, 06:35 PM
Since you probably want to avoid active users, it might be nice to make a script to touch each character based on an account name and then UPDATE character_ SET timelaston = UNIX_TIMESTAMP(NOW()) WHERE account_id = X

This is easily done in PHP and could then be added to your website. If you want such a thing I could whip it up in a few minutes, as could most of the current thread contributors I'm sure.

revloc02c
08-20-2011, 09:27 PM
I know when I saw this question I logged in all my toons hoping they won't be deleted.
Heh, me too.

thepoetwarrior
08-21-2011, 06:09 AM
I'll post something on our forums now just so users have a chance to log in, etc.

thepoetwarrior
08-21-2011, 11:39 AM
I posted a message on EZ Server forums about this to give people time to log in their characters, etc.

I'll probably test the tool on a backup database, then give players a week, or maybe even a month, notice to log in to save their characters.

Still eagerly waiting for the tool by GeorgeS!!

GeorgeS
08-21-2011, 01:24 PM
I just uploaded the beta version. I did check and it does work, but please backup first. The only tables it purges are the ones I listed in my post above.

Features -
You can sort fields, and select multiple rows and delete.

Please list features you need so i can get them in the tool.

http://www.georgestools.eqemulator.net/character_manager.rar


Have fun

GeorgeS

thepoetwarrior
08-23-2011, 10:36 PM
No window / GUI popped up.

I'm running Windows 7 64-bit.

I tried Run as Admin + Compatibility for XP.

Nothing shows up, except in process list which I kill after not seeing any window pop up.

ini file is configured properly and I ran the *.bat file for ocxs.

Any ideas?

lerxst2112
08-23-2011, 10:45 PM
It worked for me on Windows 7 x64.

Instead of the batch file, open an administrator command prompt and cd to the resources directory then type:

for %i in (*.ocx) do regsvr32 %i

That should register them properly.

thepoetwarrior
08-24-2011, 03:24 AM
The command prompt thing worked fine but the window doesn't show up still.

GeorgeS other tools work though =(

BrandeX
08-24-2011, 04:32 AM
VB.NET ftw ;)

lerxst2112
08-24-2011, 07:13 AM
The command prompt thing worked fine but the window doesn't show up still.

GeorgeS other tools work though =(

Hmm, did you just copy the db.ini from another one of the tools? That's what I did. I don't know if this one is picky about it, but I remember one of them didn't work right when I had an invalid client directory specified.

thepoetwarrior
08-24-2011, 10:06 AM
Maybe thats what happened. I'll try to use the orig ini and edit values manually this time.

Edit: Nope, didn't help. Register bat works, then run exe as admin just like I did with the bat file, but no window shows up. It does how ever get listed in Windows Task Manager, which I end process on.

GeorgeS
08-24-2011, 10:17 AM
odd, I'll look into it more and see what it may be.
More likely than not, could be a bad field/record in a table/schema issue...

GeorgeS

thepoetwarrior
08-24-2011, 10:12 PM
I updated the source / database just recently, so the table/schema should be up to date.

When the program starts, does it auto load the character table? Or do you type in fields first then do a search/query on characters? I notice when using Navicat that it takes a few seconds just to load up the first 200 character table records per page. The character table is well over 1 GB and I'm using tools remotely.

Thanks for looking into it. I'm excited to see this.

GeorgeS
08-25-2011, 10:08 AM
Now I think the answer is your database size. I would wait till the program finishes loading in the data - could be an hour. Then it should work fine. Had no idea it was 1GB!

Let me know what happens after it loads.

GeorgeS


I updated the source / database just recently, so the table/schema should be up to date.

When the program starts, does it auto load the character table? Or do you type in fields first then do a search/query on characters? I notice when using Navicat that it takes a few seconds just to load up the first 200 character table records per page. The character table is well over 1 GB and I'm using tools remotely.

Thanks for looking into it. I'm excited to see this.

thepoetwarrior
09-11-2011, 07:05 AM
I took down the server to try to character purge tool, and the window still did not show up.

I could see in the process list that mysqld.exe and character_purge.exe tool were very active I/O and let it do that for 10 minutes, but still no window.

Is there any way to load only specific info instead of the whole table which is over 1 GB? Seems to be taking too long. I am assuming soon as the program executes, it connects to the database, and loads the entire table?

Tabasco
09-11-2011, 06:55 PM
I don't understand why you would ever want to load the entire table into memory in the first place, it kind of defeats the purpose of a database in all but the most extreme of cases.

Between SQLDev and joligario all of the required info has already been posted, I just put it into a form here.

http://216.49.224.132/eq/playerprune.tar.gz

This lets you go by player level, total time played, last login time, and total platinum.

If you're hell bent on using some .net tool, maybe GeorgeS can adapt the queries in here to his GUI.
Consider it MIT or BSD licensed.

You'll need to populate db info in db.inc.php and then uncomment the actual query in dodelete.php.

GeorgeS
09-12-2011, 12:44 PM
Actually the tool works, but I forgot a very important issue of handling large character tables. I never would have imaged 1GB for a character table, so the tool would take a long time to load in the query.

Now there is a way to do this (and I will rewrite it this way) - I will only load in results of a query (as suggested before [total time played, last login time etc..]) and not the entire table.

This should expedite the process, although not entirely what I had imagined due to the afformentioned large tables. Let me give this a go, and you can try this tool once it's ready for beta release.


GeorgeS

thepoetwarrior
09-13-2011, 10:41 AM
Can't wait for the new tool. Thanks for trying to work on this =)

Hateborne
03-20-2012, 08:36 PM
I built a minor tool to possibly address this. I tested it as hard as I could with only three characters over two days. If I had some generics to play with, I could produce more. I simply do not have the time/willpower to make new characters every day or so to test this (or manipulate my system time to keep making characters).

It worked fine for me, but I do not have thousands of characters to sort through.

Simply go to the 'BackgroundData.vb' and fill in the username, password, database name, etc. Once you do that, compile it, run it, win.

This was built specifically with EZ Server in mind. As such, the tables hit are: timers, faction_values, aa_timers, player_corpses, player_corpses_backup, character_backup, inventory, and character_.

http://www.filedropper.com/deleteoldcharacters ("Download This File", not the other three "Download" buttons...)

This could probably be done through a PHP script as well, I just have not bothered (as I don't run a server and this is sufficient). If you are running a Linux server, I will create a PHP version. Just blow up the private message box or hateborne@gmail.com.

-Hate

Hateborne
04-11-2012, 08:37 PM
New link:
http://hateborne.dyndns.org/eqemu/tools/Delete%20Old%20Characters.rar

-Hate