Database Cleanup Scripts

[Orbitus 25]

Hey,

its me Orbitus from RAPAX Exile Server.

I saw the last days that some guys asked about Database Cleanup etc... Then i saw this Thread http://exile.majormittens.co.uk/topic/107-sql-vehicle-cleanup-query/
I had today enough time to do some investigation on it and will share my Scripts that i use now for cleaning the Database. So thanks to the guys in the other Thread who made the foundation for it.

Before you Change anything on your Database always make a complete backup of it!

We use for this the event_scheduler from mysql. To turn them on you have to use one of the following statements:

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

You also can add "event_scheduler=on" into your "my.cnf" or "my.ini" (You have to restart mysql after if you place it into the config files)

You can check with the following command if the Scheduler is on

SHOW PROCESSLIST;

If it is on you should have something like this:

mysql> SHOW PROCESSLIST;
+------+-----------------+-----------------+-------+---------+------+-----------------------------+------------------+
| Id   | User            | Host            | db    | Command | Time | State                       | Info             |
+------+-----------------+-----------------+-------+---------+------+-----------------------------+------------------+
| 1516 | event_scheduler | localhost       | NULL  | Daemon  | 5802 | Waiting for next activation | NULL             |
| 1664 | root            | localhost:58997 | exile | Sleep   |    7 |                             | NULL             |
| 1667 | root            | localhost:59017 | exile | Sleep   |    4 |                             | NULL             |
| 1669 | root            | localhost:59019 | exile | Sleep   |    4 |                             | NULL             |
| 1670 | root            | localhost:59020 | exile | Sleep   |    4 |                             | NULL             |
| 1671 | root            | localhost:59022 | exile | Sleep   |    4 |                             | NULL             |
| 1672 | root            | localhost:59023 | NULL  | Sleep   |   12 |                             | NULL             |
| 1673 | root            | localhost:59024 | exile | Sleep   |   11 |                             | NULL             |
| 1674 | root            | localhost:59025 | exile | Query   |    0 | init                        | SHOW PROCESSLIST |
+------+-----------------+-----------------+-------+---------+------+-----------------------------+------------------+
9 rows in set

Next step are the Cleanup Scripts. I want to cleanup following Tables

player, container, vehicles

Table "player"

For the "player"-table i use the following Script. This will check once per day the table "player" for entries where the column "is_alive" equals 0 and deletes the row.

CREATE DEFINER=`exile`@`localhost` EVENT `Delete dead bodies` 
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-15 06:00:00' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Delete dead bodies' 
DO DELETE FROM `player` WHERE `is_alive` = 0;

Table "vehicles"

I want that every vehicle that was not accessed, moved, or geared for over 10 days gets deleted (10 days because our decay-time of bases are 10)

First we have to add a new column (last_accessed_updated) where everytime when some of the columns is changed the date gets updated. For this i use following statement:

ALTER TABLE `vehicle` ADD `last_accessed_updated` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `spawned_at`;

Now we have a column that we can work with. Now we add an event as we have done for the table "player"

CREATE DEFINER=`exile`@`localhost` EVENT `Delete vehicles` 
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-15 06:30:00' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Delete vehicles from inactive users' 
DO DELETE FROM `vehicle` WHERE `last_accessed_updated` < (now() - INTERVAL 10 DAY);

Table "container"

Even if you have a decay time for Territories, the Crates/Saves will not get deleted. For me or my server we want to cleanup this because containers takes a lot of loading time on server start.

First i do the same as i have done for table "vehicles". I add a new column "last_accessed_updated" 

ALTER TABLE `container` ADD `last_accessed_updated` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `last_accessed`;

Why i dont use the "last_accessed" column?
First i thought "Hey let's use this column", then i saw that if someone places a new crate and doesnt access it in the database the "last_accessed"-Time is '0000-00-00 00:00:00' When the script is running the same Moment it would delete this Crate and after Restart the Crate would be gone. You could also do an exception in the delete Script i will do it this way

Now we add again an event to the scheduler:

CREATE DEFINER=`exile`@`localhost` EVENT `Delete container` 
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-15 05:00:00' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Delete container from inactive users' 
DO DELETE FROM `container` WHERE `last_accessed_updated` < (now() - INTERVAL 10 DAY);

Now you have for everything that you can cleanup a cleanup-script.

Baseparts/Constructions

Also you should watch how fast your Baseparts are growing. We had already 30k Baseparts until now and set the decay time now to 10 Days.

Before you change this you should look at your "territory"-table for the "last_paiyed_at"-column and "construction"-table for the "maintained_at"-column and update it to a date that is not 10 days behind. If you do not you will wipe the buildings of your Server

Here are the statements for this:

UPDATE `construction` SET `maintained_at`='2015-09-14 20:00:00';
UPDATE `territory` SET `last_payed_at`='2015-09-14 20:00:00';

Be sure to update the date!

I hope that i helped you a little bit with this. And i have to say thank you to Flow for the foundation of this.

If you have and ideas to do it better tell us  

From time to time i will update this Thread with usefull things for the Database or better cleanup.

Edited September 14, 2015 by Orbitus

[Flow 252]

Field

maintained_at

is currently not used by the game btw

[Falcon911 33]

Getting error when running the "player' 

0 row(s) affected, 1 warning(s): 1449 The user specified as a definer ('exile'@'localhost') does not exist

 

 

 

[Flow 252]

https://github.com/flow0815/exilemod

[Orbitus 25]

Getting error when running the "player' 

0 row(s) affected, 1 warning(s): 1449 The user specified as a definer ('exile'@'localhost') does not exist

 

 

 

You have to change exile@localhost to your databasename

[Falcon911 33]

You have to change exile@localhost to your databasename

it is named  exile sir.

 

[Orbitus 25]

whats your database user ?

[Falcon911 33]

root and that's what I logged in as

The Exile Database user is something else. 

Edited October 1, 2015 by Falcon911

[Orbitus 25]

try it with the exile database user 

change it to user@localhost 

be sure that this user has the rights for it

[Falcon911 33]

Got it apparently the Show event stated that the Definer was exile@localhost (Which is not even in the DB as a user)

So I ran the ALTER on the SQL and forced root to run the event.  I think I got it. Just got to wait to see if it works or not now unlessI can force the event to run