MySQL Event Scheduler & Base Maintenance

[kingpapawawa]

I noticed that my events were not running even tho i had manually turned on Event scheduler with 

SET GLOBAL event_scheduler="ON";

also turned it on within phpMyAdmin only to later find it turned off

apparently my.ini needs to be edited to include the following in the [mysqld] section

event_scheduler=on

[juandayz]

@kingpapawawa maybe this old post can help you.

  Reveal hidden contents

by @mgm

  Quote

..."We're doing auto-aging of buildables via a batch script which is executed in maintenance time window in between restarts, below is the calling script.

This is better in my opinion as database events stop being a thing to set up / monitor.

If you ever need to migrate machines [due to game server hosting provider change etc.] you simply copy the folder and you're done.

(You just monitor your batch scripts for a few restarts and then if they work that's it)."...

Expand  

 

  Reveal hidden contents

@echo off

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: CONFIG - begin
set mysql_exe_path="C:\my_data\program_files\MySQL\MySQL Server 5.6\bin"
:: CONFIG - end
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Run MySQL database maintenance scripts
cd /d %mysql_exe_path%
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-Untouched_Level_1__24h.sql
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-Threshold_After_Spawn_Level_1__3h.sql
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-Untouched_Level_2__3d.sql
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-Untouched_Level_3__7d.sql
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-Untouched_Level_4__14d.sql
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-Untouched_Level_5__5w.sql
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Player_24h.sql

:: Apply damage  to buildable objects
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\setDamageOnAge.sql

:: Cleanup completely damaged buildables
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-Delete_buildable_with_damage_more_than_1.00.sql

:: Delete "No Key Left" Vehicles
mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\DB_Cleanup-Object-DeleteNoKeyLeftVehicles.sql

:: with file based traders, no need for this any more
::mysql.exe --user=dbadminCHANGETHIS --password=passwordCHANGETHIS --host=127.0.0.1 --port=3306 --database=overpochcher < C:\dayz_mod_server\__SQL_scripts\Modify_Trader_Data.Replenish_Stocks.sql
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


:: We're done here.
@exit

 

And the corresponding SQL script is:

  Reveal hidden contents

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
-- EXECUTION FREQUENCY: 		To be executed before each server restart (every 3 hours).
-- SUMMARY:							Apply damage to all buildables that have not been updated for longer than 24 hours (i.e.: not maintained by the owner). 
--												If existing damage is none, apply 0.10 damage.
--												If existing damage is more than 0.10, apply 0.07 damage.
-- 
-- 
-- This is based on a 14 day maintenance server setup. 
-- 
-- 1st day = 10% damage
-- Remaining days = 7% damage 
-- 
-- (90% / 13 days =%6.92 damage per day, rounded up to %7.0 for additional days)
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
USE overpochcher;
UPDATE `object_data`
	SET `Damage` = IF( `Damage` = 0.0, 0.1, `Damage` + 0.07 )
	WHERE `LastUpdated` <= DATE_SUB(NOW(), INTERVAL 1 DAY)
		AND `Classname` REGEXP 'wall|floor|door|ladder|stair'
		AND `ObjectUID` <> 0
		AND `CharacterID` <> 0
		AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') );

 

 

Source:

  Reveal hidden contents

 

 

[kingpapawawa]

  On 4/12/2017 at 5:02 PM, juandayz said:

@kingpapawawa maybe this old post can help you.

Expand  

This is incomplete, the batch file calls 11 different scripts and only one is included.  But the issue is resolved with the information i provided in the first post.
SET GLOBAL event_scheduler="ON"; ///doing this turns the event scheduler on but restarting the server turns it back off.

Editing my.ini to include "event_scheduler=on" turns it on every time mysqld starts.

 

 

[juandayz]

  On 4/13/2017 at 12:11 AM, kingpapawawa said:

This is incomplete, the batch file calls 11 different scripts and only one is included.  But the issue is resolved with the information i provided in the first post.
SET GLOBAL event_scheduler="ON"; ///doing this turns the event scheduler on but restarting the server turns it back off.

Editing my.ini to include "event_scheduler=on" turns it on every time mysqld starts.

 

 

Expand  

yup i have it added in my xamp my.ini

[mysqld]
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql"
tmpdir = "C:/xampp/tmp"
datadir = "C:/xampp/mysql/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
event_scheduler=ON

[juandayz]

anyway using the .bat file you can include the recommended_mysql_events.sql provided by default in epoch files.

  Reveal hidden contents

@echo off

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: CONFIG - begin
set mysql_exe_path="C:\xampp\mysql\bin"
:: CONFIG - end
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Run MySQL database maintenance scripts
cd /d %mysql_exe_path%


:: 
::mysql.exe --user=YOUR_MYSQL_USER --password=YOUR_MYSQL_PASS --host=127.0.0.1 --port=3306 --database=YOUR_DB_NAME < C:\yourserverRoot\SQL\recommended_mysql_events.sql
::

:: We're done here.
@exit