prue420 33 Report post Posted September 10, 2015 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
Chronic 0 Report post Posted September 10, 2015 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
Boose 44 Report post Posted September 10, 2015 Is there a way to modify it so it runs as a trigger everyday, and unlocks vehicles instead Share this post Link to post Share on other sites
WD-40 25 Report post Posted September 10, 2015 If linux just add work to crontab and forget Instead of select you can use "update" query and update all old pins to 0000 Share this post Link to post Share on other sites
Flow 252 Report post Posted September 10, 2015 (edited) 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 Share this post Link to post Share on other sites
Dwarfer 50 Report post Posted September 10, 2015 (edited) 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 updatedALTER 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 suggestedCREATE 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 1 Share this post Link to post Share on other sites
prue420 33 Report post Posted September 10, 2015 (edited) I love that thanks Dwarfer. Edited September 10, 2015 by prue420 Share this post Link to post Share on other sites
WD-40 25 Report post Posted September 10, 2015 Coz select just show the query result. Share this post Link to post Share on other sites
Boose 44 Report post Posted September 10, 2015 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 updatedALTER 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 suggestedCREATE 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
Dwarfer 50 Report post Posted September 10, 2015 (edited) 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 Share this post Link to post Share on other sites