Backup and Cleanup SQL events I use if someone else needs them

[1Man]

Well for backups I use task scheduler to run every 30 mins and this is my .bat file for it

 

@echo off

CLS

SET backuptime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2%-%TIME:~0,2%-%TIME:~3,2%

echo %backuptime%

echo Running dump ...

"C:\Program Files (x86)\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --host="localhost" --port="3306" --user="dayz" --password="blahblah" -Q --result-file="D:\SQL Backups\bk_Chernarus%backuptime%.sql" overpoch

echo Done!

timeout 3

@exit

 

 

Now for restarts/time change I use this, I force stop the program so it doesnt hang and start on a diffrent port and I dont save the RPT or logs. 1st is the restart and 2nd is the hive change. I recommend making 1 for each restart so my restarts are every 3 hours so I have 4 files for each restart and hive. With the hive all you have to do is change the time I.E.

 

[Time]
;Possible values: Local, Custom, Static
;You cannot use Static on OFFICIAL Hive, it will just revert to Local
Type = Static
;If using Custom type, offset from UTC in hours (can be negative as well)
;Offset = -8
;If using Static type (Hour value always the same on every server start), the value (0-24) to set the Hour to
Hour = 9

 

Named my restarts AutoRestart 1-4 and the HiveExt 1-4, with the Hive it will delete the existing hive copy your new hive w/time change and rename it.

 

@echo off

echo.
echo KILL arma2oaserverOverpoch.exe
set serverkill="E:\Overpoch"
cd /d %serverkill%
taskkill /im arma2oaserverOverpoch.exe /f
echo.

timeout 2

echo.
echo Changing time of day
:: change time..
set hiveloc="E:\Overpoch\instance_11_Chernarus"
cd /d %hiveloc%
del "HiveExt.ini"
set arma2oaserverOverpochloc="E:\Overpoch\instance_11_Chernarus"
cd /d %arma2oaserverOverpochloc%
del "arma2oaserverOverpoch.RPT"
set HiveExtloc="E:\Overpoch\instance_11_Chernarus"
cd /d %HiveExtloc%
del "HiveExt.log"
echo Current time file deleted
timeout 2
xcopy "E:\Overpoch\instance_11_Chernarus\TimeHive\HiveExt1.ini" "E:\Overpoch\instance_11_Chernarus" /y
echo New time file copied
timeout 2
cd /d %hiveloc%
ren "HiveExt1.ini" "HiveExt.ini"
echo Time of day changed!!!
echo.

timeout 2

echo.
echo Starting Dayz Server
:: start the server..
set dayzpath=""E:\Overpoch"
cd /d %dayzpath%
start "StartServer" "StartServer.bat"
echo.

timeout 2

echo.
echo KILL cmd.exe
set cmdkill="%windir%\system32\"
cd /d %cmdkill%
taskkill /im cmd.exe
echo.

 

;This is a comment

;Comments above a certain setting will provide it's description

;The format for a setting is
;Variable = Value

;If you see a commented line of that form, it means that the setting is optional, and the Value shows the default
;To change from the default, simply uncomment the line and change the Value

;This configuration file should be placed inside your server instance's configuration directory (like cfgdayz)

[Time]
;Possible values: Local, Custom, Static
;You cannot use Static on OFFICIAL Hive, it will just revert to Local
Type = Static
;If using Custom type, offset from UTC in hours (can be negative as well)
;Offset = -8
;If using Static type (Hour value always the same on every server start), the value (0-24) to set the Hour to
Hour = 9

[Database]
;Hostname or IP of the server to connect to
;If you leave this line commented or blank, HiveExt will connect to the OFFICIAL Hive, which requires registration
;See support.dayzmod.com for more information on what OFFICIAL Hive means, what are the rules, etc.
;If using OFFICIAL hive, the rest of the settings in this section have no effect
Host = localhost

;Currently, only MySQL is supported
Type = MySQL

;Port to connect to. The default is the default listening port of a server of the selected Type
;Instead of specifying Port, you can specify Socket and set Value to the socket name
Port = 3306

;Database name to connect to.
Database = overpoch

;Username to connect with
Username = dayz
;Password to authenticate with (default is blank)
Password = blahblah

;If using OFFICIAL hive, the settings in this section have no effect, appropriate layout will be used
[Characters]
;The field name that Player's IDs are stored in (unique per game license)
;Some table layouts have this as PlayerID, and some as PlayerUID, that's why this is configurable
;IDField = PlayerUID
;The field name that Player's World Position and rotation is stored in
;Enables you to run multiple different maps (different instances) off the same character table
;WSField = Worldspace

;If using OFFICIAL hive, the settings in this section have no effect, as it will clean up by itself
[Objects]
;Which table should the objects be stored and fetched from ?
;Table = Object_DATA

;Negative values will disable this feature
;0 means that ALL empty placed items will be deleted every server restart
;A positive number is how old (in days) a placed empty item must be, in order for it to be deleted
CleanupPlacedAfterDays = 28

;Flag indicating whether hiveext should detect vehicles out of map boundaries (X < 0, or Y > 15360) and reset their position to []
;Note: YOU MUST have a proper dayz_server.pbo that supports this feature, otherwise you will get script errors
;You can find that file under the SQF directory for your server version
;ResetOOBVehicles = false

;If using OFFICIAL hive, the settings in this section have no effect, it will manage objects on its own
[ObjectDB]
;Setting this to true separates the Object fetches from the Character fetches
;That means that the Object Table must be on this other database
;Use = false

;The settings here have the same meaning as in [Database], and are only used if the setting above is set to true
;Type = MySQL
;Host = localhost
;Port = 3306
;Database = dayz
;Username = root
;Password =

 

If you have your event scheduler running on your database then you can take a look at the spoilers, here is what I use and I like a maintenance free database.

 

A2Epoch:Server Installation Instructions Extended

 

(Redirected from Server Installation Instructions Extended)

In your mysql config my.ini add the line:

event_scheduler=ON

Next run the SQL query

SET GLOBAL event_scheduler = 1;

to enable the event scheduler.

Execute this add_recommended_mysql_events.sql SQL against every dayz epoch database:

 

 

Empty Character Inventory [run once a day]

CREATE EVENT `Empty Character Inventory`

ON SCHEDULE
EVERY 1 DAY STARTS '2014-09-15' ENDS '2015-09-15'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Removed Characters with empty inventory'
DO DELETE FROM `character_data` WHERE ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[],[]]') )

 

Remove Old Logins [run once a day]

CREATE EVENT `Remove Old Logins`

ON SCHEDULE
EVERY 1 DAY STARTS '2014-09-15' ENDS '2015-09-15'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Removes logins 7 days and older'
DO DELETE FROM `player_login` WHERE `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)

 

Remove damaged vehicles [i run this every 3 hours right before restart]

CREATE EVENT `removeDamageVehicles`

ON SCHEDULE
EVERY 3 HOUR STARTS '2014-09-03 20:50:00' ENDS '2015-09-03 20:50:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT '\'Removes damaged vehicles'
DO DELETE FROM `object_data` WHERE Damage = 1

 

Remove old character data

CREATE EVENT `Removes old characters`

ON SCHEDULE
EVERY 1 DAY STARTS '2014-09-15' ENDS '2015-09-15'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Old Characters14 Days for testing'
DO DELETE FROM `character_data` WHERE `LastLogin` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY)

 

This one you will need to create a trigger in the DB and an event to follow, 1st is the trigger 2nd is the event. This will look for keys to vehicles and unlock them if no key is found. I run this every 3 hours right before a restart

 

Trigger/Function for FindVehicleKeysCount

BEGIN

DECLARE totalKeys INT DEFAULT 0;
DECLARE keyName VARCHAR(32) DEFAULT "";
DECLARE keysInChar INT DEFAULT 0;
DECLARE keysInObj INT DEFAULT 0;

SET keyName = (CASE
WHEN `keyId` < 2501 THEN CONCAT('ItemKeyGreen', `keyId`)
WHEN `keyId` < 5001 THEN CONCAT('ItemKeyRed', `keyId` - 2500)
WHEN `keyId` < 7501 THEN CONCAT('ItemKeyBlue', `keyId` - 5000)
WHEN `keyId` < 10001 THEN CONCAT('ItemKeyYellow', `keyId` - 7500)
WHEN `keyId` < 12501 THEN CONCAT('ItemKeyBlack', `keyId` - 10000)
ELSE 'ERROR'
END);

SET keysInChar = (SELECT COUNT(*) FROM `Character_DATA` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', keyName, '%') OR `Backpack` LIKE CONCAT('%', keyName, '%')));
SET keysInObj = (SELECT COUNT(*) FROM `Object_DATA` WHERE `Inventory` LIKE CONCAT('%', keyName, '%'));

RETURN (keysInChar + keysInObj);
END

 

Unlock vehicle

UPDATE

`Object_DATA`
SET
`Object_DATA`.`CharacterID` = 0
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

 

Hope this helps some of you

[Raoul]

This sure as hell helps me out :D

 

Thanks dude for posting these

[1Man]

This sure as hell helps me out :D

 

Thanks dude for posting these

 

Thanks Im glad someone else could use it

[mimic]

Is it just me, or do the "Show" buttons for the spoilers not work? Tried on 2 different machines, using 3 different browsers.

[cen]

Is it just me, or do the "Show" buttons for the spoilers not work? Tried on 2 different machines, using 3 different browsers.

 

Working fine for me with chrome.

[iSoJu]

how do you actually set up a function?

[1Man]

how do you actually set up a function?

 

Create a Function/Stored Event

If using HeidiSQL login and go to your database, then right click on it.

Scroll to Create New and select Stored routine

Under the options tab

Name: FindVehicleKeysCount

Define: Your DB Login [by default it should be filled in]

Comment: FindVehicleKeysCount [could be anything keep it simple]

Type: Function

Data access: Contains SQL

Returns: int(11) [you can paste this in there]

SQL Security: Definer

Routine body: [Paste this in there]

BEGIN

DECLARE totalKeys INT DEFAULT 0;

DECLARE keyName VARCHAR(32) DEFAULT "";

DECLARE keysInChar INT DEFAULT 0;

DECLARE keysInObj INT DEFAULT 0;

SET keyName = (CASE

WHEN `keyId` < 2501 THEN CONCAT('ItemKeyGreen', `keyId`)

WHEN `keyId` < 5001 THEN CONCAT('ItemKeyRed', `keyId` - 2500)

WHEN `keyId` < 7501 THEN CONCAT('ItemKeyBlue', `keyId` - 5000)

WHEN `keyId` < 10001 THEN CONCAT('ItemKeyYellow', `keyId` - 7500)

WHEN `keyId` < 12501 THEN CONCAT('ItemKeyBlack', `keyId` - 10000)

ELSE 'ERROR'

END);

SET keysInChar = (SELECT COUNT(*) FROM `Character_DATA` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', keyName, '%') OR `Backpack` LIKE CONCAT('%', keyName, '%')));

SET keysInObj = (SELECT COUNT(*) FROM `Object_DATA` WHERE `Inventory` LIKE CONCAT('%', keyName, '%'));

RETURN (keysInChar + keysInObj);

END

DONT COPY THIS Just the above info [bEGIN to END]

Click over to the Parameters option

Click add and under Name paste keyId

Click Save and your done

[1Man]

In case you have installed a version of MYSQL that does not have an editable myini file you can go to programs and use MySQL 5.7 Command Line Client and login.

 

Next you will just paste this

event_scheduler=ON

Hit enter and its on

[gooses89]

I use some similar events to keep my database clean.

 

The only thing I'd recommend is changing

 

DELETE FROM `character_data` WHERE `LastLogin` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY)

to 

DELETE FROM `character_data` WHERE `LastLogin` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) and 'Alive' = 0

 

This will stop the event from deleting anyone who is still Alive in the database. I've seen people leave for more than 14 days and come back.

 

 

[Proximus]

I just changed the 14 to 28.

If a player hasn't bin online for 4 weeks, somethings wrong :lol:

[gooses89]

I just changed the 14 to 28.

If a player hasn't bin online for 4 weeks, somethings wrong :lol:

 

That's another option for it. 14 days was a bit short.

[1Man]

if a player hasn't been on in 14 days his stuff is probably looted and isnt coming back. 2 weeks is plenty of time to login which is all that is.

[Nazgul]

anyone know how to see all player stuff in database? i mean what he have in vault, the vehicles he buy , all things attatched in the player id. anyone know how to consult this in database?

[1Man]

anyone know how to see all player stuff in database? i mean what he have in vault, the vehicles he buy , all things attatched in the player id. anyone know how to consult this in database?

 

Only build-able objects are based on character ID's, Vehicles and Vaults are based off Keys and Codes. You can see where they built in the database and look for vaults in that location.

[Caveman1]

Thank you

[vRazgriz]

Will this help clear locked vehicles in the server, had a hacker spawn over 1000 of them. 

[1Man]

Yes it would as long as there are no keys to these vehicles, only issue I see is the event unlocks them so you would have that many available