Orbitus

Database Cleanup Scripts

21 posts in this topic

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://www.exilemod.com/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 by Orbitus
11 people like this

Share this post


Link to post
Share on other sites
Advertisement

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

 

 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

root and that's what I logged in as

The Exile Database user is something else. 

Edited by Falcon911

Share this post


Link to post
Share on other sites

try it with the exile database user 

change it to user@localhost 

be sure that this user has the rights for it

Share this post


Link to post
Share on other sites

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 

Share this post


Link to post
Share on other sites
Advertisement

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.