Getting Rid of old players

S

Stuart Halliday

Member
Whilst the perl cleanup script will remove dead players from the database, there seems to be no provision to remove the hundreds of old players that are no longer using the server?

As each player has a date field. Has anyone the expertise to write a perl script that works out when a player is alive, but not been on the server for a month (say for example) and then delete them?

Can't stand having hackers names still listed in the db! :)
 
I didn't write a perl script for you but here is the SQL (for a Bliss server).

Code: 
-- clean up any deployed items
DELETE FROM id USING instance_deployable id JOIN survivor s ON id.owner_id = s.id WHERE s.last_updated < now() - INTERVAL 1 MONTH;
-- delete profile
DELETE FROM p USING profile p JOIN survivor s ON s.unique_id = p.unique_id WHERE s.last_updated < now() - INTERVAL 1 MONTH;
-- delete characters
DELETE FROM survivor WHERE last_updated < now() - INTERVAL 1 MONTH;
 
wriley said:
I didn't write a perl script for you but here is the SQL (for a Bliss server).

Code: 
-- clean up any deployed items
DELETE FROM id USING instance_deployable id JOIN survivor s ON id.owner_id = s.id WHERE s.last_updated < now() - INTERVAL 1 MONTH;
-- delete profile
DELETE FROM p USING profile p JOIN survivor s ON s.unique_id = p.unique_id WHERE s.last_updated < now() - INTERVAL 1 MONTH;
-- delete characters
DELETE FROM survivor WHERE last_updated < now() - INTERVAL 1 MONTH;
Click to expand...


This is wonderful, but when i run it i get this. My server has been up for more than a month at this point and has tons of useless old player info.

-- clean up any deployed items
DELETE FROM id USING instance_deployable id JOIN survivor s ON id.owner_id = s.id WHERE s.last_updated < now( ) - INTERVAL 1 MONTH ;# MySQL returned an empty result set (i.e. zero rows).
-- delete profile
DELETE FROM p USING profile p JOIN survivor s ON s.unique_id = p.unique_id WHERE s.last_updated < now( ) - INTERVAL 1 MONTH ;# MySQL returned an empty result set (i.e. zero rows).
-- delete characters
DELETE FROM survivor WHERE last_updated < now( ) - INTERVAL 1 MONTH ;# MySQL returned an empty result set (i.e. zero rows).
 
What do you get running these 2 queries?

Code: 
mysql> SELECT COUNT(*) FROM survivor;
+----------+
| COUNT(*) |
+----------+
|    1087 |
+----------+
1 row in set (0.00 sec)
 
mysql> SELECT COUNT(*) FROM survivor WHERE last_updated < NOW() - INTERVAL 1 MONTH;
+----------+
| COUNT(*) |
+----------+
|      836 |
+----------+
1 row in set (0.00 sec)

This is on my Chernarus server and you can see that 836 out of 1087 haven't played in the last month.
 
Sure thing, here are the 2 queries. You can copy and paste one at a time or both at once, shouldn't matter.

Code: 
SELECT COUNT(*) FROM survivor;
SELECT COUNT(*) FROM survivor WHERE last_updated < NOW() - INTERVAL 1 MONTH;
 
returned 0, maybe there are none older than a month.. come to think of it i did wipe the server at the end of december. how would you write the original script to clean up things older than lets say 21 days
 
Actually, come to think of it. What i really want to do is remove all of the profiles of players who have not been on in 3 weeks. but more importantly remove all of the
IS DEAD = 1 players in survivor tab. i have figured out how to clean up all the other tables such as deployable and vehicle. but survivor and profiles are getting to be quite large.
 
final result, thank you for your help. its good to be able to bounce ideas off of people. thanks for your original code most importantly.

-- clean up any deployed items
DELETE FROM id USING instance_deployable id JOIN survivor s ON id.owner_id = s.id WHERE s.last_updated < now() - INTERVAL 21 DAY;# 7 rows affected.

-- delete profile
DELETE FROM p USING profile p JOIN survivor s ON s.unique_id = p.unique_id WHERE s.last_updated < now() - INTERVAL 21 DAY;# 142 rows affected.

-- delete characters
DELETE FROM survivor WHERE last_updated < now() - INTERVAL 21 DAY;# 220 rows affected.
 
-- delete characters
DELETE FROM survivor WHERE is_dead = 1

I've tried this, same error as when i try to do it manually. Some delete, others say this....

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`user-234`.`instance_deployable`, CONSTRAINT `instance_deployable_ibfk_2` FOREIGN KEY (`owner_id`) REFERENCES `survivor` (`id`))

Must be some sort of dependency that wont allow certain ones to delete
 
Cool, glad I could help.

If you're using Bliss you can use "db_utility.pl cleandead X", where X is the number of days, to clean up dead survivors. I have a call to db_utility.pl as part of my server start .bat file and clean up dead survivors older than 7 days.
 
Clavicus said:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`user-234`.`instance_deployable`, CONSTRAINT `instance_deployable_ibfk_2` FOREIGN KEY (`owner_id`) REFERENCES `survivor` (`id`))

Must be some sort of dependency that wont allow certain ones to delete
Click to expand...

Yes, that is telling you it can't remove survivor entries that are linked to items in the instance_deployable table (like tents). db_utility.pl uses the proper SQL to remove deployables when using the cleandead option. Note in the original queries I showed you I'm deleting from instance_deployable and profile before deleting the survivor entry. This can be done as one query but I broke it up to show the dependencies.
 
not sure what happens when i restart my server, it just has a restart button on my panel from HFB.com
i would like to find the bat though and see whats going. do you have an qualms with start.bat?
i'm going to go dig mine up and see what it does.
 
modified the script so it won't delete users who own a deployable (FK restraint)

Code: 
DELETE FROM
  survivor  
WHERE
  last_updated < now() - INTERVAL 1 MONTH
  AND survivor.id NOT IN 
  (
    SELECT 
      IDP.owner_id
      FROM instance_deployable IDP
 
  )
 
Or if you just want to delete all players older than X (14 in the example below) days including those that own deployables use:

Code: 
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM `survivor` WHERE `is_dead`='1' AND `last_updated` < now() - interval 14 day;
SET FOREIGN_KEY_CHECKS = 1;
 
Could you please explain where I put this and how to run it etc..

I'm running bliss and very new to this.
 
You must log in or register to reply here.
Back
Top