Epoch 1061 Clean up Scripts

[DieTanx]

Hello, i am looking for somebody to possibly share clean up scripts beyond the stock scripts that come with the epoch now.

 

I would like to reduce the size of my database, its getting quite large and is starting to lag server the more it grows.

Please and thank you.

[BigEgg]

Here is my collection of cleanup scripts:

 

-- --------------------------------------------------------------------------------
-- --------------------------------Base Maintenance--------------------------------
-- --------------------------------------------------------------------------------

-- ----------------------------
-- Fix inventory on maintainable objects.
-- ----------------------------
UPDATE `object_data`
SET `inventory` = '[]',`LastUpdated` = `LastUpdated`
WHERE `inventory` = '[[[],[]],[[],[]],[[],[]]]'
AND
`Classname` IN ('BagFenceRound_DZ','CinderWall_DZ','CinderWallDoorLocked_DZ','CinderWallDoorSmall_DZ','CinderWallDoorSmallLocked_DZ','CinderWallHalf_DZ','FireBarrel_DZ','GunRack_DZ','LightPole_DZ','MetalFloor_DZ','MetalPanel_DZ','OutHouse_DZ','TentStorage','TentStorageDomed','TentStorageDomed2','Sandbag1_DZ','Scaffolding_DZ','StickFence_DZ','StorageShed_DZ','WorkBench_DZ','WoodCrate_DZ','WoodFloor_DZ','WoodFloorQuarter_DZ','WoodLargeWall_DZ','WoodLargeWallWin_DZ','WoodShack_DZ','WoodSmallWall_DZ','WoodSmallWallThird_DZ','WoodSmallWallWin_DZ','Land_DZE_GarageWoodDoor','WoodFloorHalf_DZ','Fort_RazorWire','Land_DZE_LargeWoodDoorLocked','WoodStairsRails_DZ','WoodLadder_DZ','WoodStairsSans_DZ','M240Nest_DZ','Land_DZE_WoodDoor');

-- ----------------------------
-- Set 0.1 damage to objects daily.
-- ----------------------------
UPDATE `object_data`
SET `Damage` = IF( `Damage` = 0.0, 0.1, `Damage` + 0.1 )
WHERE `LastUpdated` <= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND `ObjectUID` <> 0
AND `CharacterID` <> 0
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') );

-- ----------------------------
-- Set 0.1 damage doors/plots daily.
-- ----------------------------
UPDATE `object_data`
SET `Damage` = IF( `Damage` = 0.0, 0.1, `Damage` + 0.1 )
WHERE `Classname` IN ('Plastic_Pole_EP1_DZ','CinderWallDoorSmallLocked_DZ','CinderWallDoorLocked_DZ','CinderWallSmallDoorway_DZ','CinderWallDoorway_DZ','Land_DZE_WoodDoorLocked','CinderWallDoor_DZ','CinderWallDoorSmall_DZ','Land_DZE_WoodDoor','Land_DZE_GarageWoodDoor','Land_DZE_GarageWoodDoorLocked','Land_DZE_LargeWoodDoorLocked','Land_DZE_LargeWoodDoor','WoodLargeWallDoor_DZ')
AND `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
AND `Inventory` != '[]';

-- ----------------------------
-- Set safe combinations to 0000 after 10 days of inactivity.
-- ----------------------------
UPDATE `object_data`
SET `CharacterID` = 0
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)
AND `CharacterID` > 0
AND `Classname` = "VaultStorageLocked"
AND `Inventory` <> '[]'
AND `Inventory` IS NOT NULL;

-- ----------------------------
-- Set lockbox combinations to RED00 after 10 days of inactivity.
-- ----------------------------
UPDATE `object_data`
SET `CharacterID` = 10000
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)
AND `CharacterID` > 0
AND `Classname` = "LockboxStorageLocked"
AND `Inventory` <> '[]'
AND `Inventory` IS NOT NULL;

-- ----------------------------
-- 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 inactive 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 objects that have 1 damage.
-- ----------------------------
DELETE FROM `object_data`
WHERE Damage = 1;

-- ----------------------------
-- Delete plot poles and doors not being deleted.
-- ----------------------------
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 11 DAY)
AND `Classname` IN ('Plastic_Pole_EP1_DZ','CinderWallDoorSmallLocked_DZ','CinderWallDoorLocked_DZ','CinderWallSmallDoorway_DZ','CinderWallDoorway_DZ','Land_DZE_WoodDoorLocked','CinderWallDoor_DZ','CinderWallDoorSmall_DZ','Land_DZE_WoodDoor','Land_DZE_GarageWoodDoor','Land_DZE_GarageWoodDoorLocked','Land_DZE_LargeWoodDoorLocked','Land_DZE_LargeWoodDoor','WoodLargeWallDoor_DZ');

-- ----------------------------
-- Delete old stuff.
-- ----------------------------
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 21 DAY);

-- --------------------------------------------------------------------------------
-- ------------------------------Vehicle Maintenance-------------------------------
-- --------------------------------------------------------------------------------

-- ----------------------------
-- Delete Bikes, ATV's, Mozzie's, 350z's, and Busses.
-- ----------------------------
DELETE FROM `object_data`
WHERE `Classname` IN ('MMT_Civ','Old_bike_TK_INS_EP1','TT650_Civ','TT650_Ins','Old_moto_TK_Civ_EP1','M1030_US_DES_EP1','TT650_TK_CIV_EP1','ATV_CZ_EP1','ATV_US_EP1','CSJ_GyroC','350z','350z_red','350z_kiwi','350z_black','350z_silver','350z_green','350z_blue','350z_gold','350z_white','350z_pink','350z_mod','350z_ruben','350z_v','350z_city','350z_yellow','Ikarus','Ikarus_TK_CIV_EP1');

-- ----------------------------
-- Delete useless vehicles with no keys.
-- ----------------------------
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 4 HOUR)
AND `CharacterID` = '0'
AND `Classname` IN ('AN2_DZ','GNT_C185U','GNT_C185','GNT_C185R','GNT_C185C','RHIB','Smallboat_1','Smallboat_2','Zodiac','Fishing_Boat','PBX','JetSkiYanahui_Case_Red','JetSkiYanahui_Case_Yellow','JetSkiYanahui_Case_Green','JetSkiYanahui_Case_Blue','CSJ_GyroP','CSJ_GyroCover','LandRover_CZ_EP1','LandRover_TK_CIV_EP1','HMMWV_M1035_DES_EP1','HMMWV_Ambulance','HMMWV_Ambulance_CZ_DES_EP1','HMMWV_DZ','HMMWV_DES_EP1','Civcar','Civcarbu','civcarbl','Civcarre','Civcarge','Civcarwh','Civcarsl','hilux1_civil_3_open_EP1','datsun1_civil_3_open_DZE','hilux1_civil_1_open_DZE','datsun1_civil_2_covered_DZE','datsun1_civil_1_open_DZE','hilux1_civil_2_covered_DZE','Skoda','SkodaBlue','SkodaGreen','SkodaRed','VolhaLimo_TK_CIV_EP1','Volha_1_TK_CIV_EP1','Volha_2_TK_CIV_EP1','VWGolf','car_hatchback','car_sedan','GLT_M300_LT','GLT_M300_ST','Lada1','Lada1_TK_CIV_EP1','Lada2','Lada2_TK_CIV_EP1','LadaLM','SUV_TK_CIV_EP1','SUV_Blue','SUV_Charcoal','SUV_Green','SUV_Orange','SUV_Pink','SUV_Red','SUV_Silver','SUV_White','SUV_Yellow','SUV_Camo','UAZ_CDF','UAZ_INS','UAZ_RU','UAZ_Unarmed_TK_CIV_EP1','UAZ_Unarmed_TK_EP1','UAZ_Unarmed_UN_EP1');

-- ----------------------------
-- Delete useless vehicles with keys.
-- ----------------------------
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND `CharacterID` > 0
AND `Classname` IN ('AN2_DZ','GNT_C185U','GNT_C185','GNT_C185R','GNT_C185C','RHIB','Smallboat_1','Smallboat_2','Zodiac','Fishing_Boat','PBX','JetSkiYanahui_Case_Red','JetSkiYanahui_Case_Yellow','JetSkiYanahui_Case_Green','JetSkiYanahui_Case_Blue','CSJ_GyroP','CSJ_GyroCover','LandRover_CZ_EP1','LandRover_TK_CIV_EP1','HMMWV_M1035_DES_EP1','HMMWV_Ambulance','HMMWV_Ambulance_CZ_DES_EP1','HMMWV_DZ','HMMWV_DES_EP1','Civcar','Civcarbu','civcarbl','Civcarre','Civcarge','Civcarwh','Civcarsl','hilux1_civil_3_open_EP1','datsun1_civil_3_open_DZE','hilux1_civil_1_open_DZE','datsun1_civil_2_covered_DZE','datsun1_civil_1_open_DZE','hilux1_civil_2_covered_DZE','Skoda','SkodaBlue','SkodaGreen','SkodaRed','VolhaLimo_TK_CIV_EP1','Volha_1_TK_CIV_EP1','Volha_2_TK_CIV_EP1','VWGolf','car_hatchback','car_sedan','GLT_M300_LT','GLT_M300_ST','Lada1','Lada1_TK_CIV_EP1','Lada2','Lada2_TK_CIV_EP1','LadaLM','SUV_TK_CIV_EP1','SUV_Blue','SUV_Charcoal','SUV_Green','SUV_Orange','SUV_Pink','SUV_Red','SUV_Silver','SUV_White','SUV_Yellow','SUV_Camo','UAZ_CDF','UAZ_INS','UAZ_RU','UAZ_Unarmed_TK_CIV_EP1','UAZ_Unarmed_TK_EP1','UAZ_Unarmed_UN_EP1');

-- ----------------------------
-- Delete 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|killhouse|villa|castle|pub|shelter|stodola|garage|MBG|warehouse|sara';

-- ----------------------------
-- Delete all vehicles not touched in 10 days.
-- ----------------------------
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 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|killhouse|villa|castle|pub|shelter|stodola|garage|MBG|warehouse|sara';

-- ----------------------------
-- Unlock purchased, inactive vehicles.
-- ----------------------------
UPDATE `object_data`
SET `CharacterID` = 0,`LastUpdated` = `LastUpdated`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 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|killhouse|villa|castle|pub|shelter|stodola|garage|MBG|warehouse|sara';

-- ----------------------------
-- Unlock vehicle's with no key.
-- ----------------------------
UPDATE `Object_DATA`
SET `Object_DATA`.`CharacterID` = 0,`LastUpdated` = `LastUpdated`
WHERE `Object_DATA`.`CharacterID` <> 0
AND`Object_DATA`.`CharacterID` <= 12500
AND`Object_DATA`.`Classname` NOT LIKE 'Tent%'
AND`Object_DATA`.`Classname` NOT LIKE '%Locked'
AND`Object_DATA`.`Classname` NOT LIKE 'Land%'
AND`Object_DATA`.`Classname` NOT LIKE 'Cinder%'
AND`Object_DATA`.`Classname` NOT LIKE 'Wood%'
AND`Object_DATA`.`Classname` NOT LIKE 'Metal%'
AND`Object_DATA`.`Classname` NOT LIKE '%Storage%'
AND`Object_DATA`.`Classname` NOT IN ('OutHouse_DZ', 'GunRack_DZ', 'WorkBench_DZ', 'Sandbag1_DZ', 'FireBarrel_DZ', 'DesertCamoNet_DZ', 'StickFence_DZ', 'LightPole_DZ', 'DeerStand_DZ', 'ForestLargeCamoNet_DZ', 'Plastic_Pole_EP1_DZ', 'Hedgehog_DZ', 'FuelPump_DZ', 'Fort_RazorWire', 'SandNest_DZ', 'ForestCamoNet_DZ', 'Fence_corrugated_DZ', 'CanvasHut_DZ', 'Generator_DZ')
AND FindVehicleKeysCount(Object_DATA.CharacterID) = 0;

-- --------------------------------------------------------------------------------
-- -------------------------------Player Maintenance-------------------------------
-- --------------------------------------------------------------------------------

-- ----------------------------
-- Delete player login data.
-- ----------------------------
DELETE FROM `player_login`;

-- ----------------------------
-- Delete dead characters.
-- ----------------------------
DELETE from Character_data
USING Character_data, Character_data as tempchartable
WHERE (Character_data.PlayerUID = tempchartable.PlayerUID)
AND (NOT Character_data.alive = tempchartable.alive)
AND (Character_data.alive = 0);

-- ----------------------------
-- Delete characters inactive for 30 days.
-- ----------------------------
DELETE FROM Character_DATA 
WHERE LastLogin < NOW() - INTERVAL 30 DAY;

 

[Relentless]

So I have a cleanup script with several options you could run on each restart. It checks for things like players and objects that are older than a set specific amount of time and deletes them.

[DieTanx]

i would welcome any help

[juandayz]

1 hour ago, DieTanx said:

i would welcome any help

 

cleanPlayersandVeh.sql

BEGIN
DELETE FROM character_data WHERE Alive = 0;
DELETE FROM object_data WHERE Damage = 1;
END

fn_clean.bat

echo Cleaning Server Database
cd\ 
cd C:\xampp\mysql\bin\mysql.exe -u YOURUSER -pYOURPASS dayz-epoch --execute="call cleanPlayersandVeh()"

or just enter as a query the

DELETE FROM character_data WHERE Alive = 0;

DELETE FROM object_data WHERE Damage = 1;

and execute manually.

[Jim90]

You may not want to delete all of the dead players from your character_data, because when a player dies and doesn't rejoin right away his humanity data is stored on that last dead character until he joins again.

[DieTanx]

currently, i am using these   

Spoiler

DELETE FROM `player_login` WHERE `Datestamp` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 7 DAY)

DELETE FROM `object_data` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 8 DAY) AND `CharacterID` > 0 AND `Classname` NOT IN ('VaultStorage','LockboxStorage','VaultStorageLocked','LockboxStorageLocked') AND `Inventory` <> '[]' AND `Inventory` IS NOT NULL

DELETE FROM `Object_DATA` WHERE Damage >= 1

DELETE FROM `Object_DATA` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 DAY) AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )

DELETE FROM `Object_DATA` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 42 DAY)

 

[juandayz]

1 hour ago, Jim90 said:

You may not want to delete all of the dead players from your character_data, because when a player dies and doesn't rejoin right away his humanity data is stored on that last dead character until he joins again.

i just execute it after server restart.. if this player do not respawn before restart.. is hes problem.. but yes,, youer right

[LunatikCH]

2 hours ago, juandayz said:

i just execute it after server restart.. if this player do not respawn before restart.. is hes problem.. but yes,, youer right

CREATE DEFINER=`root`@`localhost` EVENT `deletedead`
	ON SCHEDULE
		EVERY 1 DAY STARTS '2014-04-21 18:32:07'
	ON COMPLETION NOT PRESERVE
	ENABLE
	COMMENT ''
	DO delete FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

That leaves one character there so ppl dont lose theyr humanity incase you're interested :)

[juandayz]

3 hours ago, LunatikCH said:

CREATE DEFINER=`root`@`localhost` EVENT `deletedead`
	ON SCHEDULE
		EVERY 1 DAY STARTS '2014-04-21 18:32:07'
	ON COMPLETION NOT PRESERVE
	ENABLE
	COMMENT ''
	DO delete FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

That leaves one character there so ppl dont lose theyr humanity incase you're interested :)

[DieTanx]

4 hours ago, LunatikCH said:

CREATE DEFINER=`root`@`localhost` EVENT `deletedead`
	ON SCHEDULE
		EVERY 1 DAY STARTS '2014-04-21 18:32:07'
	ON COMPLETION NOT PRESERVE
	ENABLE
	COMMENT ''
	DO delete FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

That leaves one character there so ppl dont lose theyr humanity incase you're interested :)

I love it! :) only a few rows removed

 

delete FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID);
/* Affected rows: 19,753  Found rows: 0  Warnings: 0  Duration for 1 query: 2.750 sec. */

[salival]

56 minutes ago, DieTanx said:

I love it! :) only a few rows removed

 

delete FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID);
/* Affected rows: 19,753  Found rows: 0  Warnings: 0  Duration for 1 query: 2.750 sec. */

Mine had 35k entries, heh

[juandayz]

@BigEgg you call it "collection" ?? its the library of alexandria tnks for share it!

[masta]k9[playa]

Hi,

Could you explain how to add this to the server

[Relentless]

Hey @BigEgg,

what is this exactly?

-- ----------------------------
-- Delete 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|killhouse|villa|castle|pub|shelter|stodola|garage|MBG|warehouse|sara';

All other lines are clear, but I don't get what this code block does. And just another question: Is it good to delete dead characters? I thought the humanity is stored on that until the player respawns?

[salival]

8 minutes ago, DAmNRelentless said:

Hey @BigEgg,

what is this exactly?

-- ----------------------------
-- Delete 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|killhouse|villa|castle|pub|shelter|stodola|garage|MBG|warehouse|sara';

All other lines are clear, but I don't get what this code block does. And just another question: Is it good to delete dead characters? I thought the humanity is stored on that until the player respawns?

It will remove server spawned vehicles (Dynamic Vehicles) after 3 days of inactivity. They are the blue ones on the map if you use infistar.

[Relentless]

So the vehicles that are spawned by dynamic_vehicles.sqf. Okay, thanks for the info!

 

1 hour ago, masta]k9[playa said:

Hi,

Could you explain how to add this to the server

You could execute this by a batch file, for example something like this (I made the variables configurable):

@echo off
@REM *** PARAMETERS/VARIABLES ***
set dbname=your database name
set dbuser=your database user
set dbpassword=your database password
set dbport=your database port
set mysqlpath=your mysql.exe path
set cleanuppath=your cleanup.sql path

@REM *** EXECUTION ***
echo Performing database cleanup...
%mysqlpath%\mysql.exe --port=%dbport% -u %dbuser% -p%dbpassword% %dbname% < %cleanuppath%\db_cleanup.sql
exit

This requires that you save bigeggs cleanup.sql as "db_cleanup.sql" if you don't want to edit this batch-file. But keep in mind to edit the variables right at the top. Here is an example:

set dbname=overpoch_1
set dbuser=user123
set dbpassword=asdf1234
set mysqlport=3306
set mysqlpath="C:\xampp\mysql\bin"
set cleanuppath="D:\Overpoch_1\database"

To automatically execute this batch file, there are multiple ways for example calling it in the scheduler from BEC or add it to the windows scheduler if you're running a dedicated server or a vServer. You can also save the part with the mysql path if you set mysql as an environment variable.

Edited April 30, 2017 by DAmNRelentless
Fixed code for batch

[masta]k9[playa]

So before I make my server useless I wanted to ask if i understand this correctly. I want to add the stuff bigegg posted. I would copy paste that into SQL like in the photo and hit go then change the line in my Hiveext.ini CleanupPlacedAfterDays =  to whatever many days I want it to be. This seems to easy and I am slightly retarted.

 

[Relentless]

@masta]k9[playa
If you are not sure what you are doing there, use the batch file I posted.

[Vladick]

Can't you just run these SQL statements as Events in phpMyAdmin? 

[Relentless]

42 minutes ago, Vladick said:

Can't you just run these SQL statements as Events in phpMyAdmin? 

You can, but that needs knowledge of phpmyadmin. That's why I suggested the batch file.

[BigEgg]

16 hours ago, Vladick said:

Can't you just run these SQL statements as Events in phpMyAdmin? 

I find the batch file to be a much easier way (I have used a batch file for a long time) because it doesn't need timed restarts to work. My server can restart at any time because the batch file is called in the restart batch, so instead of needing restarts at the times your events are set to run, you can just have it restart any time. 

[Relentless]

19 minutes ago, BigEgg said:

I find the batch file to be a much easier way (I have used a batch file for a long time) because it doesn't need timed restarts to work. My server can restart at any time because the batch file is called in the restart batch, so instead of needing restarts at the times your events are set to run, you can just have it restart any time. 

That's how I do it aswell.

[theduke]

ok, so I have my sql cleanup scripts very similar to @BigEgg 's list...

For some odd reason, my safes are disappearing.

this is my add damage to safes, so they can be maintained... I also added the safes and lockboxes to the DZE_maintainClasses in the variables.sqf

/* Set Damage On lockable storage objects via Age */
UPDATE `object_data`
SET `Damage` = IF( `Damage` = 0.0, 0.16, `Damage` + 0.04 )
WHERE `Classname` IN ('LockboxStorageLocked','VaultStorageLocked')
AND `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
AND `Inventory` != '[]';

my safe had inventory in it. maintain was done 3 days ago (set to 14 days, rest of the base is still there)

im not toooo worried, as it was my own safe lol, havnt heard players say anything yet....but id like to prevent it.

So im wondering if my sql above, is correct?

Thanks

[Relentless]

@theduke

This should be working

UPDATE `object_data`
SET `Damage` = IF( `Damage` = 0.0, 0.1, `Damage` + 0.1 )
WHERE `LastUpdated` <= DATE_SUB(NOW(), INTERVAL 2 DAY)
AND `Classname` IN ('VaultStorageLocked','LockboxStorageLocked')
AND `Inventory` != '[]'
AND `Inventory` IS NOT NULL;

You should put that date_sub line into the WHERE

[DieTanx]

Question, Trying to run clean up script from bec-  no errors, Scheduler working, restart messages, but i dont see this running/cleanup of database happening

Any thoughts?

Or would this be better as a Fire Dameon or windows service?  Run this for multiple servers- So assumed bec would be best option. but i may be wrong.

      <job id="0">
        <time>001500</time>
        <delay>000000</delay> 
        <day>1,2,3,4,5,6,7</day>
        <start>013000</start>
        <runtime>020000</runtime>
        <loop>0</loop> 
        <cmd>D:\Games\overpoch106\BEC\Config\db_cleanup.bat</cmd>
        <cmdtype>1</cmdtype>
    </job>