prue420

SQL Vehicle Cleanup Query?

19 posts in this topic

Could someone make a sql query to search for peoples last time they logged in and if they have not logged in in the past 5 days it will delete there vehicle that is tied to there uid? my server is getting over run with vehicles and admins are spending to much time going back and forth from the data base and check when the last time someone logged in was.

Share this post


Link to post
Share on other sites

hey, 

 

here you go just change the date at the end to fit your needs.

SELECT
vehicle.id,
vehicle.class,
vehicle.spawned_at,
vehicle.account_uid,
vehicle.is_locked,
vehicle.fuel,
vehicle.damage,
vehicle.hitpoints,
vehicle.position_x,
vehicle.position_y,
vehicle.position_z,
vehicle.direction_x,
vehicle.direction_y,
vehicle.direction_z,
vehicle.up_x,
vehicle.up_y,
vehicle.up_z,
vehicle.cargo_items,
vehicle.cargo_magazines,
vehicle.cargo_weapons,
vehicle.cargo_container,
vehicle.pin_code
FROM
vehicle
INNER JOIN account ON account.uid = vehicle.account_uid
WHERE last_connect_at < '2015-09-05 *:*:*'

 

Share this post


Link to post
Share on other sites
Advertisement
CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles` 
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-10 14:25:02' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Delete vehicles from inactive users' 
DO DELETE FROM `vehicle` WHERE `account_uid` IN (SELECT `uid` FROM `account` WHERE `last_connect_at` < NOW() - INTERVAL 500 DAY)

For your security i use 500 days in the example. Change this  to your liking

Edited by Flow

Share this post


Link to post
Share on other sites

The problem with doing this is you could end up deleting vehicles that have moved to a CLAN member as that clan member has now stopped playing. 

The way I have done this is to mod the vehicle table to add a new column called updated

ALTER TABLE `vehicle` ADD `updated` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `spawned_at`;

This then makes a column you can then use to see when something was last used as this will auto update the column updated when it is locked, moved or gear is moved in or out of. once you have this done you can do what flow has suggested

CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles` 
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-10 14:25:02' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Delete vehicles from inactive users' 
DO DELETE FROM `vehicle` WHERE `updated` < now() - INTERVAL 14 DAY)

14 days being anything over that time frame gets nuked.

Edited by Dwarfer
  • Like 1

Share this post


Link to post
Share on other sites

The problem with doing this is you could end up deleting vehicles that have moved to a CLAN member as that clan member has now stopped playing. 

The way I have done this is to mod the vehicle table to add a new column called updated

ALTER TABLE `vehicle` ADD `updated` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `spawned_at`;

This then makes a column you can then use to see when something was last used as this will auto update the column updated when it is locked, moved or gear is moved in or out of. once you have this done you can do what flow has suggested

CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles` 
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-10 14:25:02' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Delete vehicles from inactive users' 
DO DELETE FROM `vehicle` WHERE `updated` < now() - INTERVAL 14 DAY)

14 days being anything over that time frame gets nuked.

How can i change this to unlock instead?

 

Share this post


Link to post
Share on other sites

Well you would just switch the delete from to something like.

update `vehicle` set is_locked=0 WHERE `updated` < now() - INTERVAL 14 DAY)

I have note tested so make a backup !!

Naturally when you run this the updated coloum will have the new time stamp on it..

However I must stress doing this while the server is running is BAD VERY BAD.. Best bet is to add some script todo this when the server restart.

Edited by Dwarfer

Share this post


Link to post
Share on other sites
Advertisement

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.