[Howto] Refresh dynamic vehicles on restart

[f3cuk]

What this does

 

Remove all server spawned - or force unlocked - vehicles that have no gear in them and haven't been updated in the last hour on restart.

 

Why you might want this

 

It makes room for fresh vehicles to spawn so there are actually new vehicles on the coast each restart. Plus it helps cleanup vehicles that are never found / used.

 

Why the no gear and update exclusion

You can take them out if you want. But its mainly to ensure people dont get fucked over. You don't want your precious gear to get deleted on restart and neither do you want your vehicle you were just driving before restart to dissapear on you, leaving you all alone in the wilderness :P.

 

---

 

First things first

 

BACKUP YOUR DATABASE BEFORE DOING ANY CHANGES TO THEM

 

1.) Okay you gonna wanna start by running this query to check if it selects the right objects for you.

SELECT
   *
FROM
    `object_data`
WHERE
    `LastUpdated` < DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND
    `CharacterID` = 0
AND
(
    `Inventory` = '[]'
    OR
    `Inventory` = '[[[],[]],[[],[]],[[],[]]]'
 )

This query will not transform anything but just check to see if it finds any matches. Depending on your settings it should find quite a few vehicles, our server is pretty new. We got maxvehicles @ 350 and it finds about ~250 vehicles. After running this query you will have to go through the results and see if it didnt select any vehicles you don't want to get deleted.

 

2.) If all goes well and you are satisfied with the selected vehicles, it's safe to assume the query is working for you and we can go ahead by implementing it. (There's is probably al lot ways to do this, i chose to do it in my restart.bat file, just after backing up the database.)

My restart.bat

@echo off
cd C:\path\to\zombieland
echo Stopping server
start /wait zombieland_stop.bat
echo Backing up database
start /wait zombieland_backup.bat
echo Refresh vehicles
start /wait refresh_vehicles.bat
echo Starting BEC
cd C:\path\to\bec
start Bec.exe -f Config.cfg
echo Starting server
cd C:\path\to\zombieland
start "arma2" /min /affinity 3 "arma2oaserver.exe" -port=2302 "-config=instance_11_Chernarus\config.cfg" "-cfg=instance_11_Chernarus\basic.cfg" "-profiles=instance_11_Chernarus" -name=instance_11_Chernarus "-mod=@DayZ_Epoch;@hive;" -maxMem=2047
ping 127.0.0.1 -n 5 >NUL

Notice the "start /wait zombieland_refresh.bat", that's where the magic happens. The /wait ensures it waits to finish.

 

3.) Create a file called refresh_vehicles.sql and save it in your server folder. Add below code

 

Replace

• {database_name} with the name of your database.

USE {database_name};

DELETE FROM
    `object_data`
WHERE
    `LastUpdated` < DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND
    `CharacterID` = 0
AND
(
    `Inventory` = '[]'
    OR
    `Inventory` = '[[[],[]],[[],[]],[[],[]]]'
 )

4.) Create a file called refresh_vehicles.bat and add below code

 

Replace

• {username} with you database username
• {password} with your database password
• {path_to_mysql_bin_folder} with the path to your mysql bin folder (e.g. C:\xampp\mysql\bin)
• {path_to_refresh_vehicles} with the path to your refresh_vehicles.sql (e.g. C:\servername\

@echo off
cd {path_to_mysql_bin_folder}
mysql.exe -u {username} -p{password} < {path_to_refresh_vehicles}refresh_vehicles.sql
exit

5.) You are now done and set for restart!

 

--

 

6.) Optionally lower the MaxVehicleDamage in your init.sqf to ensure less broken vehicles.

[insertcoins]

would it not be easier to write an sql event that deletes that have not been touched and are not owned, and have an empty inventory, 5 minutes prior to every restart?

[f3cuk]

That would infact be much more handy. But i don't know how to fire a mysql event prior to restart. (Also doesnt this create oppurtunity for vehicles found just before restart to despawn since the event has already got rid of that vehicle in the database?)

[insertcoins]

sure thing, but maybe 1 minute prior to restart?

 

It's a bit of a hassle, but you can set a sql event to start at a certain time and repeat every x hours, so you could set it to run the first time 1 minute prior to a restart, and have it repeat every 3 or 4 hours, depending on RS schedule.

[f3cuk]

Don't like that idea. Simply because my restart are not on set times and sometimes i restart early for whatever reason, which makes it a pain having to go into the database and changing times. Something that actually might work is calling the event just after you called the #shutdown command in your BEC scheduler. Should be a bit more easy to install. But i dont know how to execute a mysql event from within the bec scheduler without the use of a .bat file :P

Anyways, this works for us and anyone can change it to suit their needs.

[cen]

Use firedaemon and run a .bat file as a preservice. Then every time the server starts it runs the bat which contains the .sql :)
 
Also your query will catch things like vault and lockboxes.
 
Try this one:

SELECT * FROM `object_data`
SELECT * FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(NOW(), INTERVAL 2 HOUR)
AND `CharacterID` = 0
AND `Classname` 
NOT REGEXP 'land|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold'
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') );

[f3cuk]

 

Use firedaemon and run a .bat file as a preservice. Then every time the server starts it runs the bat which contains the .sql :)

 

Also your query will catch things like vault and lockboxes.

 

Try this one:

SELECT * FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND `CharacterID` = 0
AND `Classname` 
NOT REGEXP 'land|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold';
AND (`Inventory` = '[]' OR `Inventory` = '[[[],[]],[[],[]],[[],[]]]');

 

Nope buildables should have a CharacterID (they do on our server, didnt really change anything in that department).

--

Whats the advantage of firedeamon? To me it sounds like something you don't really need cause it's doing the exact same thing now..

[cen]

Safes reset to 0000 after 14 days on mine so it will catch those, but that's server-specific.

 

FireDaemon runs anything you want as a service. Lots of benefits, one of which is being able to run pre-service operations.

 

An example is on my servers every time a service starts (a dayz server for example) it runs through a few pre-service operations before the server starts.

 

One of which is a set of .SQL queries:

/* =================== DELETES =================== */

/* Delete destroyed objects */
DELETE FROM `object_data`
WHERE Damage = 1;

/* Delete old login data */
DELETE FROM `player_login`;

/* Delete untouched server spawned vehicles */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND `CharacterID` = '0'
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';

/* Delete all vehicles not touched in 7 days */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';

/* Delete empty storage objects */
DELETE FROM `object_data` 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY) 
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )
AND `Classname` IN ('Wooden_shed_DZ','WoodShack_DZ', 'TentStorage', 'TentStorageDomed', 'TentStorageDomed2', 'WoodCrate_DZ', 'GunRack_DZ', 'OutHouse_DZ', 'StorageShed_DZ');

/* Delete unused storage objects */
DELETE FROM `object_data` 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY) 
AND `Classname` IN ('Wooden_shed_DZ','WoodShack_DZ', 'TentStorage', 'TentStorageDomed', 'TentStorageDomed2', 'WoodCrate_DZ', 'GunRack_DZ', 'OutHouse_DZ', 'StorageShed_DZ');

/* Delete damaged bikes/motorcycles */
DELETE FROM object_data WHERE (classname = "Old_bike_TK_INS_EP1" OR classname = "MMT_CIV")
AND (hitpoints LIKE "%[\"wheel_1_damper\",1]%"
AND hitpoints LIKE "%[\"wheel_2_damper\",1]%");

/* =================== UPDATES =================== */

/* Unlock Purchased Untouched Vehicles */
UPDATE `object_data`
SET `CharacterID` = 0
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 DAY)
AND `CharacterID` > 0
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';
/* Set Damage On Objects via Age */
UPDATE `Object_DATA` SET `Damage`= 0.2
WHERE `ObjectUID` <> 0
AND `CharacterID` <> 0
AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') );

/* Set Safe Combo to 0000 */
UPDATE `object_data` 
SET `CharacterID` = 0 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) 
AND `CharacterID` > 0 
AND `Classname` = "VaultStorageLocked"  
AND `Inventory` <> '[]'
AND `Inventory` IS NOT NULL;

/* Set Lock Box to RED 00 */
UPDATE `object_data` 
SET `CharacterID` = 10000 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) 
AND `CharacterID` > 0 
AND `Classname` = "LockboxStorageLocked"  
AND `Inventory` <> '[]' 
AND `Inventory` IS NOT NULL;

[f3cuk]

Obviously if you have custom scripts that resets stuff to CharacterID = 0, you are going to want to filter those (although what is the harm in deleting unused && unlocked && empty safe?).

 

Nice setup! Most of those i've got running as mysql events, is their any real advantage running them as separate queries?

Could you eleborate on the "Lots of benefits" part? The thing about pre-service isn't that just the same as using the /wait command in a bat file to schedule multipe events pre restart?

[cen]

Well the point of resetting safe combinations that aren't used are so players can find them and be rewarded, so deleting them would be a bad thing :D

 

I can't say there's any advantage to my way vs. scheduled events unless you don't have access to the mysql server and scheduler is disabled.

 

As far as the benefits go, one huge benefit is if the server every crashes FD will automatically restart the service and you'll server will be back up and running in no time.

 

Another FD specific feature is that you can run Fusion (a free add-on) that gives you an web gui that lets you create accounts that have access to specific services/features so that you can have admins start/stop/restart your servers remotely on the web.

 

There are tons of others!

[f3cuk]

Okay the web interface is a definite awesome add-on then. I run a Windows Event Scheduler to check if my server is up and running and if not calls server_error.bat which logs (i'm trying to add a mailfunction to to that so i get notified) some running processes stuff and restart the server. But yeah the web interface is definately something that is a cool feature and i just might want it for that :)

[Stranger]

Im getting a "Database changed" in Navicat...if i try your sql stuff

[f3cuk]

Never worked with navicat so couldn't tell you expected results. Running a SELECT query should never change your database, its purely read.

[Externized]

Thanks for this

[ATRealMaster]

Does this affect Bases?

[Tanita-Corp]

 

Safes reset to 0000 after 14 days on mine so it will catch those, but that's server-specific.

 

FireDaemon runs anything you want as a service. Lots of benefits, one of which is being able to run pre-service operations.

 

An example is on my servers every time a service starts (a dayz server for example) it runs through a few pre-service operations before the server starts.

 

One of which is a set of .SQL queries:

/* =================== DELETES =================== */

/* Delete destroyed objects */
DELETE FROM `object_data`
WHERE Damage = 1;

/* Delete old login data */
DELETE FROM `player_login`;

/* Delete untouched server spawned vehicles */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND `CharacterID` = '0'
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';

/* Delete all vehicles not touched in 7 days */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';

/* Delete empty storage objects */
DELETE FROM `object_data` 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY) 
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )
AND `Classname` IN ('Wooden_shed_DZ','WoodShack_DZ', 'TentStorage', 'TentStorageDomed', 'TentStorageDomed2', 'WoodCrate_DZ', 'GunRack_DZ', 'OutHouse_DZ', 'StorageShed_DZ');

/* Delete unused storage objects */
DELETE FROM `object_data` 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY) 
AND `Classname` IN ('Wooden_shed_DZ','WoodShack_DZ', 'TentStorage', 'TentStorageDomed', 'TentStorageDomed2', 'WoodCrate_DZ', 'GunRack_DZ', 'OutHouse_DZ', 'StorageShed_DZ');

/* Delete damaged bikes/motorcycles */
DELETE FROM object_data WHERE (classname = "Old_bike_TK_INS_EP1" OR classname = "MMT_CIV")
AND (hitpoints LIKE "%[\"wheel_1_damper\",1]%"
AND hitpoints LIKE "%[\"wheel_2_damper\",1]%");

/* =================== UPDATES =================== */

/* Unlock Purchased Untouched Vehicles */
UPDATE `object_data`
SET `CharacterID` = 0
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 DAY)
AND `CharacterID` > 0
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';
/* Set Damage On Objects via Age */
UPDATE `Object_DATA` SET `Damage`= 0.2
WHERE `ObjectUID` <> 0
AND `CharacterID` <> 0
AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') );

/* Set Safe Combo to 0000 */
UPDATE `object_data` 
SET `CharacterID` = 0 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) 
AND `CharacterID` > 0 
AND `Classname` = "VaultStorageLocked"  
AND `Inventory` <> '[]'
AND `Inventory` IS NOT NULL;

/* Set Lock Box to RED 00 */
UPDATE `object_data` 
SET `CharacterID` = 10000 
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) 
AND `CharacterID` > 0 
AND `Classname` = "LockboxStorageLocked"  
AND `Inventory` <> '[]' 
AND `Inventory` IS NOT NULL;

y used is our servers?

[Zupa]

y used is our servers?

 

Sorry, can you repeat that. I think nobody undestands the sentece. 

 

 

I build my bambi server:

Gold Coins

Plot4Life

SnapPro Vector

Plot Manager

Door Manager

ZCraft

 

Dit i ever release ZCraft ?

 

Or did you take it out of my mission files? :p

[Buck0]

What the hell iz zcraft?

[Th3-Hunter333]

Is it possible to have this run on a vilayer hosted server, im very new to messing with the database