Delete untouched vehicles after X number of days

[BetterDeadThanZed]

Is there an SQL query I can run that will delete all vehicles that haven't been "touched" in X number of days? 

[Shizweak]

I've been using this:

DELETE FROM object_data 
WHERE 
  object_data.Datestamp < NOW() - INTERVAL 5 DAY
  AND Classname NOT LIKE '%Tent%'
  AND Classname NOT LIKE '%Vault%'
  AND Classname NOT LIKE '%Cinder%'
  AND Classname NOT LIKE '%Wood%'
  AND Classname NOT LIKE '%Sand%'
  AND Classname NOT LIKE '%Tank%'
  AND Classname NOT LIKE '%Wire%'

However, I'm not too sure if the Datestamp field is being actively updated as we've noticed some vehicles being removed when they've been used recently. You could set this up as an event, or run it manually. This may not cover all the "building"  class types either - so I suggest using it with precaution.

 

If you don't want to remove vehicles that players have bought, simply add:

AND CharacterID=0

[BetterDeadThanZed]

Ok, thanks for that information. I will try it out!

[MadHatter05]

Thank you! Time to add this to the 14 day maintenance period as well.

[Achmed]

What sql code would be needed to remove vehicles that are not owned and also have all the wheels and glass taken?

[Shizweak]

What sql code would be needed to remove vehicles that are not owned and also have all the wheels and glass taken?

 

This would require some extra work, as you need to parse the Hitpoints column, I'll see if I can work something out for you. As it's certainly something I wish to do also.

 

OK, just had a quick muck around - this seems to work fine, probably not the most efficient way - but considering the size of any DayZ database, performance should be acceptable for maintence tasks:

DELETE FROM dayz_epoch.object_data
WHERE Hitpoints LIKE '%wheel_1_1_steering",1%'
AND Hitpoints LIKE '%wheel_1_2_steering",1%'
AND Hitpoints LIKE '%wheel_2_1_steering",1%'
AND Hitpoints LIKE '%wheel_2_2_steering",1%';

Once again, you will need to do the CharacterID check above to ensure it doesn't remove owned vehicles.

[BetterDeadThanZed]

What if I wanted to only delete vehicles with no inventory?

[Shizweak]

What if I wanted to only delete vehicles with no inventory?

 

That should be an easy one, use my original query and add

AND (Inventory = '[[[],[]],[[],[]],[[],[]]]' OR Inventory = '[]')

Once again, don't forget to add the character ID line to exclude character owned vehicles if you wish.

[Bags2247]

So I'm guessing here btw, that you could marry them all together, that removes untouched/semi damaged/ non owned vehicles all at the same time then..

 

I'm just following a semi logical way to shortcut it all...

Using it would be my next question... where...

 

 

I use heidisql, to watch DB stuff, but pretty much unsure of any other useful functions/uses of it are..

 

Old aged Noob New Server Renter learning how to do this stuff here

[BetterDeadThanZed]

That should be an easy one, use my original query and add

AND (Inventory = '[[[],[]],[[],[]],[[],[]]]' OR Inventory = '[]')

Once again, don't forget to add the character ID line to exclude character owned vehicles if you wish.

 

Thanks for all the information on this. One more question. If I want to only delete vehicles that have no owner and no inventory, without any of the other previous code, would this be correct?

 

DELETE FROM object_data 
WHERE 
  object_data.CharacterID = 0
  AND (Inventory = '[[[],[]],[[],[]],[[],[]]]' OR Inventory = '[]')

[Shizweak]

So I'm guessing here btw, that you could marry them all together, that removes untouched/semi damaged/ non owned vehicles all at the same time then..

 

Best approach would be to use the event scheduler in MySQL, as per the Epoch docs:

http://dayzepoch.com/wiki/index.php?title=Server_Installation_Instructions_Extended

 

 

If I want to only delete vehicles that have no owner and no inventory, without any of the other previous code, would this be correct?

DELETE FROM object_data 
WHERE 
  object_data.CharacterID = 0
  AND (Inventory = '[[[],[]],[[],[]],[[],[]]]' OR Inventory = '[]')

 

That should be fine - CharacterID check should also ensure building parts are not included - let me know if you have any troubles.

[Armifer]

Just an FYI for everyone reading this thread:

 

Date field in object_data is not updated, it only displays when the object was created.

[BetterDeadThanZed]

Just an FYI for everyone reading this thread:

 

Date field in object_data is not updated, it only displays when the object was created.

 

Well, that stinks. I guess I'll only set up a schedule to delete vehicles without tires, older than 7 days, and unowned.

 

Maybe we should have a stickied thread, or a separate section, for MySQL queries?

[Lanmanfm]

here is the post that shows how to add the last active to the database

 

http://dayzepoch.com/forum/index.php?/topic/992-getting-a-lot-of-vehicles-sitting-around/#entry6947

[Shizweak]

Another solution is to add a trigger:

DELIMITER $$
CREATE TRIGGER update_object_datestamp 
    BEFORE UPDATE ON object_data
    FOR EACH ROW BEGIN
    SET NEW.Datestamp = NOW(); 
END$$
DELIMITER ;

[BetterDeadThanZed]

Another solution is to add a trigger:

DELIMITER $$
CREATE TRIGGER update_object_datestamp 
    BEFORE UPDATE ON object_data
    FOR EACH ROW BEGIN
    SET NEW.Datestamp = NOW(); 
END$$
DELIMITER ;

 

Does this need to be set on a schedule, or is it just run once? How does this affect the other queries posted here? For example, if you use the above code, how does it change this:

DELETE FROM object_data 
WHERE 
  object_data.Datestamp < NOW() - INTERVAL 5 DAY
  AND Classname NOT LIKE '%Tent%'
  AND Classname NOT LIKE '%Vault%'
  AND Classname NOT LIKE '%Cinder%'
  AND Classname NOT LIKE '%Wood%'
  AND Classname NOT LIKE '%Sand%'
  AND Classname NOT LIKE '%Tank%'
  AND Classname NOT LIKE '%Wire%'
  AND CharacterID=0

Also, in this thread, a different approach was suggested: http://dayzepoch.com/forum/index.php?/topic/992-getting-a-lot-of-vehicles-sitting-around

ALTER TABLE `character_data`
    ADD `lastactive` timestamp on update current_timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `object_data`
    ADD `lastactive` timestamp on update current_timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

Is either method better than the other?

[Shizweak]

I wouldn't say one is better than the other, my solution uses the existing schema and updates the Datestamp column each time the record is updated, where as the other solution uses a new field "lastactive" which gets updated with the time it was updated. I don't see any reason we need to keep the initial creation date, unless you want to track how long a vehicle has been 'alive' for.

 

In regards to the trigger, it only needs to be added once - and it will fire before each row is updated. Tested this all last night, and seems to work fine.

[BetterDeadThanZed]

I wouldn't say one is better than the other, my solution uses the existing schema and updates the Datestamp column each time the record is updated, where as the other solution uses a new field "lastactive" which gets updated with the time it was updated. I don't see any reason we need to keep the initial creation date, unless you want to track how long a vehicle has been 'alive' for.

 

In regards to the trigger, it only needs to be added once - and it will fire before each row is updated. Tested this all last night, and seems to work fine.

 

If I try your method, I get this message when submitting the SQL query: #1419 - You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

 

If I use the other method, I get this: #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

[MGT]

Add the lastactive column to db as per earlier in this thread, then create a new function, call them what you like

 

Deletes any vehicle not accessed in last 7 days with the damage specified, if people are using a vehicle as storage, when they access it it will reset the date anyway on lastactive, so no need leave out owned vehicles

BEGIN
DELETE FROM dayz_epoch.object_data
WHERE Hitpoints LIKE '%wheel_1_1_steering",1%'
AND object_data.lastactive < NOW() - INTERVAL 7 DAY
AND Hitpoints LIKE '%wheel_1_2_steering",1%'
AND Hitpoints LIKE '%wheel_2_1_steering",1%'
AND Hitpoints LIKE '%wheel_2_2_steering",1%';

END

[Shizweak]

Not sure about the second one, but the first one seems to indicate you don't have super privileges on the database - this is required for creating triggers and such.

[Gimmic]

Personally, I would use a new field and keep a trigger updating it. This way you have both the age of the vehicle and the last time it was modified. This would give you more flexibility in cleanup.

[LimpNoodle]

 

Add the lastactive column to db as per earlier in this thread, then create a new function, call them what you like

 

Deletes any vehicle not accessed in last 7 days with the damage specified, if people are using a vehicle as storage, when they access it it will reset the date anyway on lastactive, so no need leave out owned vehicles

BEGIN
DELETE FROM dayz_epoch.object_data
WHERE Hitpoints LIKE '%wheel_1_1_steering",1%'
AND object_data.lastactive < NOW() - INTERVAL 7 DAY
AND Hitpoints LIKE '%wheel_1_2_steering",1%'
AND Hitpoints LIKE '%wheel_2_1_steering",1%'
AND Hitpoints LIKE '%wheel_2_2_steering",1%';

END

 

That does not work. lastactive column of my vehicles gets updated every single time my server restarts... So how do I get the 7 day thing working then...

[MGT]

That does not work. lastactive column of my vehicles gets updated every single time my server restarts... So how do I get the 7 day thing working then...

 

Working ok on mine? What db tool are you using?

[LimpNoodle]

Working ok on mine? What db tool are you using?

 

Navicat 

 

I've added those, restarted the server and it updated all my vehicles with the current date/time

 

I then accessed a vehicle and looked at the database and it did the update perfectly fine so I smiled!

 

But everytime the server restarts it updates the lastactive column of all the vehicles with the current date/date again :(

 

Not sure wtf

[Shizweak]

But everytime the server restarts it updates the lastactive column of all the vehicles with the current date/date again :(

 

Not happening here, how are you restarting? BEC? other addons?

[LimpNoodle]

Not happening here, how are you restarting? BEC? other addons?

 

Scheduled restarts with the control panel scheduler, but does not matter even if I do a manual restart, it just updated them all on restart for some reason :(