SQL Vehicle Cleanup Query?

[prue420 33]

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.

[Chronic 0]

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 *:*:*'

 

[Boose 44]

Is there a way to modify it so it runs as a trigger everyday, and unlocks vehicles instead 

[WD-40 25]

If linux just add work to crontab and forget  Instead of select you can use "update" query and update all old pins to 0000

[Flow 252]

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 September 10, 2015 by Flow

[Dwarfer 50]

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 September 10, 2015 by Dwarfer

[prue420 33]

I love that thanks Dwarfer.

Edited September 10, 2015 by prue420

[WD-40 25]

Coz select just show the query result. 

 

[Boose 44]

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?

 

[Dwarfer 50]

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 September 10, 2015 by Dwarfer