SA-MP Forums

SA-MP Forums (http://forum.sa-mp.com/index.php)
-   Tutorials (http://forum.sa-mp.com/forumdisplay.php?f=70)
-   -   [Tutorial] MySQL - Event scheduler (http://forum.sa-mp.com/showthread.php?t=546630)

Lordzy 16/11/2014 04:05 PM

MySQL - Event scheduler
 
MySQL - Event Scheduler
Last Updated - 25th of December, 2016

Introduction

This tutorial is in regarding creating schedules on MySQL which can assist you with running queries on a specified schedule. It's a database only CRON job. In short, using scheduling, you can easily do the following:

Temporary bans, VIP systems and more.
Resets over inactive player's owned stuffs (vehicles/properties or even their account).
Creating archives.
Running SQL queries even when the server is shut down.
Delaying queries or using the scheduler as a timer, whether repeating or non-repeating.
Organize in-game events easily.
Create automatic backups.
and more...
NOTE: This tutorial is meant for users who has got at least the basic knowledge in MySQL. If you're unsure about using MySQL, please consider learning the basics at first.


Events

MySQL's event scheduler manages the tasks that run according to the schedules set. The event scheduler has got various syntax which can be set that determines what it should do, when it should begin, when it should stop and whether it has to repeat at every specified intervals or not.

You can create events and manage them easily on MySQL, name can be set for each events and it can also be dropped if needed.

Before creating an event, you must make sure if the event_scheduler variable on MySQL is ON or not. You can turn it on by editing my.cnf (For Linux) or my.ini(For Windows) or by executing this query:
Code:

"SET GLOBAL `event_scheduler`=ON"
//Sets `event_scheduler` variable to be ON.


If event_scheduler variable isn't on then your schedules will not be executed on the given intervals.

Types of events:

Events can be generally used like timers, they're of two types;

Recurring event type.
One time event type.

The first one is meant to be a schedule which runs at every specified time and the latter one will be running only one time just like it's name specifies. As soon as the ONE TIME schedule has ran, it gets removed from the event list since there's no more use of it.
Creating Events

You can create events either through SQL command or through phpMyAdmin. I'll be showing both the methods, starting over with creating events through phpMyAdmin.

Step #1:

Select your database from the database list, as soon as you click, it should display the tables that exists.
Step #2:

You can see a tab named "Events", click it then. Here's a picture to assist you:
http://i.imgur.com/Qjq4RoI.jpg
Step #3:

You are now redirected to your "Events" section. Here on my picture it shows that there are no events created on my database, to add an event, click "Add event". The event_scheduler variable is ON here because I've turned it on earlier, you can turn it on if it's off for you. Without event_scheduler on, schedules will not be executed.
http://i.imgur.com/8aFVJHM.jpg
Step #4:
You can see a dialog box showing the form of creating an event.
http://i.imgur.com/x3OeK6b.jpg

Event Name - Stands for the name of the event.
Status - States if it's enabled or disabled, disabled events won't be executed.
Event type - I've mentioned different types of events earlier, by default it's set to ONE TIME. If you set it to RECURRING you'll see additional forms asking for the intervals it should be repeating. For example, every 1 MINUTE, 1 DAY or even every 1 YEAR.
Execute at - Stands for the date-time the schedule has to begin, it's format is DATETIME so you can even insert "NOW()" or "CURRENT_TIMESTAMP" function while creating events through SQL command.
Defining - The SQL query that has to be sent when the schedule is called.
on completion preserve - If this is disabled, the event will be automatically dropped once if it's expired or else it will be preserved.

Creating Events - Through SQL Commands

I mostly create schedules using SQL queries, please note that you better check at first if the definition query is successful or not. I've noticed that at sometimes if you give wrong syntax on definition query while executing the create schedule query, it might not log any error. But the schedule will be failed or might not work the way you want to.

To create a schedule or an event, we use the function CREATE EVENT. The basic syntax of CREATE EVENT as per MySQL documentation is:
Code:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Here's a small example of creating a schedule easily which runs a query only for a time, basically a delayed query.
Code:

"CREATE EVENT `myeventname` ON SCHEDULE AT CURRENT_TIMESTAMP() + INTERVAL 1 MINUTE \
DO \
UPDATE `mytable` SET `money` = 0 WHERE `user`='Lordzy'


The above query will create an event named "myeventname" and will start after 1 minute since the event creation has been done. If you specify "INTERVAL 1 DAY" then it will be executed only after a day since the event creation. By "DO", you're defining the query to be executed once if the event has been triggered. The query I did here is an update over my table named "mytable" which sets the money field's value to 0 on row where the user is equal to "Lordzy".

Some other example can be:
Code:

mysql_tquery(connectionHandler,
"CREATE EVENT `unbanall` ON SCHEDULE AT CURRENT_TIMESTAMP() + INTERVAL 1 DAY \
DO \
UPDATE `mybans` SET `banSTATUS`=0", "", "");
//After 1 day, every bans will be expired from my table.
//Even if the server is shut down, this schedule will be running as long as MySQL server is up and if the event is enabled.


Once if a ONE TIME event has been over, it's automatically removed from the events section. (Only if 'on completion preserve' is disabled, otherwise it will be existing even if it's expired.)

That's it with ONE TIME event scheduler, now the RECURRING event schedule - It can be mostly used for auto unban over expired bans and can be running like a repeating timer. To go on through the tutorial I'm assuming that I've got a ban database with the following fields:

BanID - INT(10), UNSIGNED, NOT NULL, AUTO_INCREMENT, PRIMARY KEY
BanUser - VARCHAR(24) NOT NULL
BanBy - INT(10) UNSIGNED NOT NULL (Storing the userID of the admin who banned this user)
BanReason - VARCHAR(60) NOT NULL
BanOn - INT(32) UNSIGNED NOT NULL
UnbanOn - INT(32) UNSIGNED NOT NULL
BanSTATUS - TINYINT(1) UNSIGNED NOT NULL (0 = Ban expired, 1 = Banned)
Here, BanOn and UnbanOn saves the timestamp values which can be used on schedule definition. Now I'm creating a schedule to process auto unban over bans which are not expired and if their ban expire time has reached.
Code:

"CREATE EVENT `autounban` \
ON SCHEDULE EVERY 1 MINUTE \
STARTS NOW() \
DO \
UPDATE `bandata` SET `BanSTATUS`=0 WHERE `BanSTATUS`=1 AND (UNIX_TIMESTAMP() >= `UnbanOn` AND `UnbanOn` != 0)"


On the above query, I'm creating an event called "autounban" which will be running on every 1 minute like a repeated timer that runs on every 1 minute. It will be beginning at the time it gets created because it uses "NOW()" which returns the current datetime according to the timezone of the database. Then it does the query which sets the banSTATUS to 0 if the unban time has been reached or over. The reason why I check "`UnbanOn` != 0" is because if it's 0, it's meant to be a permanent ban. Or else while banning, it's meant to have value of UNIX_TIMESTAMP() + (ban intervals in seconds).

Some more examples can be:
Code:

"CREATE EVENT `autoaccountremoval` \
ON SCHEDULE EVERY 1 MONTH \
STARTS NOW() \
DO \
DELETE FROM `usertable` WHERE UNIX_TIMESTAMP() >= `lastonline`+ 2678400"


The above query will delete accounts if they're inactive for more than a month. 2678400 = 1 month, in seconds.

Listing Events

Listing events can be easily done by clicking on the "Events" tab. To list it through SQL query, you can perform the following command:
Code:

SHOW EVENTS
This SQL command will directly list all the details of every events than "Events" tab basically lists out.

Retrieving events information on PAWN using MySQL plugin


It's possible to retrieve event information to the server just like account information are retrieved. The method I'm using here is meant for the latest MySQL plugin or for the ones which uses cache_ functions.

For versions below R40:
Code:

mysql_tquery(connectionHandle, "SHOW EVENTS", "OnEventsList", ""); //Running a query to list my events.
forward OnEventsList();

public OnEventsList() //The thread which is called when the query responds successfully
{
        new rows = cache_get_row_count();
        if(rows) //If there are any rows,
        {
                for(new i = 0; i< rows; i++) //Looping through the retrieved number of rows
                {
                        /*
                        Here, I'll be retrieving the following data:
                        Db - The database where the event is located.
                        Name - The name of the event.
                        Type - The event type, whether RECURRING or ONE-TIME    */
                       
                        cache_get_field_content(i, "Db", MySQLEvent[i][Db], connectionHandle, 60);
                        cache_get_field_content(i, "Name", MySQLEvent[i][Name], connectionHandle, 32);
                        cache_get_field_content(i, "Type", MySQLEvent[i][Type], connectionHandle, 10);
                }
        }
        return 1;
}

As per R40th version of MySQL plugin:
Code:

mysql_tquery(connectionHandle, "SHOW EVENTS", "OnEventsList", ""); //Running a query to list my events.
forward OnEventsList();

public OnEventsList() //The thread which is called when the query responds successfully
{
        new rows;
        cache_get_row_count(rows);
        if(rows) //If there are any rows,
        {
                for(new i = 0; i< rows; i++) //Looping through the retrieved number of rows
                {
                        /*
                        Here, I'll be retrieving the following data:
                        Db - The database where the event is located.
                        Name - The name of the event.
                        Type - The event type, whether RECURRING or ONE-TIME    */
                       
                        cache_get_value_name(i, "Db", MySQLEvent[i][Db], 60);
                        cache_get_value_name(i, "Name", MySQLEvent[i][Name], 32);
                        cache_get_value_name(i, "Type", MySQLEvent[i][Type], 10);
                }
        }
        return 1;
}

You can also list out particular events using "WHERE" along with SHOW EVENTS, here's an example:
Code:

"SHOW EVENTS WHERE `Name`='myspecialevent'"
//Will list the event with name 'myspecialevent'

"SHOW EVENTS WHERE `Db`='myDB'
//Will list the event located at the database 'myDB'


Altering Events

Like tables, event structure can also be altered. You can either use the event configurations GUI as shown in Step #4 or simply run a query to change it's configurations. Changes can occur to it's schedule, it's description, it's state (whether enabled or disabled) and the event's name.

Enabling/Disabling an event using SQL query
To enable/disable an event using SQL query, you can simply execute the following command.
Code:

ALTER EVENT `event_name` DISABLE;

To enable the query using SQL command, simply change "DISABLE" to to "ENABLE".
Renaming an event using SQL query
To rename, we use the "RENAME TO" clause along with "ALTER EVENT".
Code:

ALTER EVENT `event_name` RENAME TO `new_event_name`;

It's also possible to move your event from one database to other. You'll have to specify the event name separated with it's database name.
Code:

ALTER EVENT `db_name`.`event_name` RENAME TO `new_db_name`.`event_name`;
Changing an event's schedule using SQL query
Event schedules can also be changed easily using a simple SQL query. You can also change a one-time occurring event to recurring (if the event exists) or conversely change recurring to one-time. To set it as a recurring event to run every 1 hour, I can execute the following.
Code:

ALTER EVENT `event_name` ON SCHEDULE EVERY 1 HOUR;

ALTER EVENT `event_name` ON SCHEDULE EVERY 2 HOUR; //Runs every 2 hours



Code:

ALTER EVENT `my_event` ON SCHEDULE AT CURRENT_TIMESTAMP() + INTERVAL 1 MINUTE; //This query will be executed after 1 minute from now.

ALTER EVENT `my_event` ON SCHEDULE AT CURRENT_TIMESTAMP() + INTERVAL 1 HOUR; //Executes after 1 hour from now.

Changing an event's task using SQL query

Changing an event's task is also easy using SQL query.
Code:

ALTER EVENT `my_event` DO <stuffs>

ALTER EVENT `my_event` DO UPDATE `my_table` SET `money`=`money`+100; //Update every rows and add 100$ to their money value whenever the event is called.

Dropping Events

Dropping events on MySQL is also simple; to drop them using phpMyAdmin, head over to "Events" section, choose an event and click on "Drop" button. The chosen event(s) will be dropped out then.

It's also possible to drop events using SQL query and the command is:
Code:

DROP EVENT `event_name`
Where `event_name` should be replaced with the event's name you want to remove.

Conclusion

Before concluding, I want to note that this is the second time I'm writing the conclusion part. I screwed up my thread somehow while editing and now everything since listing events are rewritten in a hurry, so if there's any mistakes, feel free to post it here.

This tutorial is to show how MySQL's event scheduler feature can be useful. It can be used for creating some useful things like logging or even set it to do automatic backups. Event scheduler can work just like timers, it's a database only CRON job. Even when the server is offline, event scheduler will handle the things to be done on the database.

event_scheduler has to be ON if event scheduling has to be worked out. I'm also linking some topics which can help you more with event scheduling:

Creating Scheduled Events - http://www.sitepoint.com/how-to-create-mysql-events/
CREATE EVENT official MySQL documentation - http://dev.mysql.com/doc/refman/5.1/...ate-event.html
DROP EVENT official MySQL documentation - http://dev.mysql.com/doc/refman/5.1/en/drop-event.html

Storing player chat log using MySQL event scheduler (It's a tutorial which uses the same method, by RajatPawar) - http://forum.sa-mp.com/showthread.php?t=511737

sammp 16/11/2014 04:13 PM

Re: MySQL - Event scheduler
 
Very nice, i'll put it into practice VIP systems and such so some code doesn;t need to be written!

Nice work!

sammp 16/11/2014 04:16 PM

Re: MySQL - Event scheduler
 
Also, look at this:

Code:

EVERY 1 MINUTE

If it's over 1 minute, would i do this

Code:

EVERY 2 MINUTES

or

Code:

EVERY 2 MINUTE

Lordzy 16/11/2014 04:26 PM

Re: MySQL - Event scheduler
 
Quote:

Originally Posted by sammp (Post 3258858)
Also, look at this:

Code:

EVERY 1 MINUTE

If it's over 1 minute, would i do this

Code:

EVERY 2 MINUTES

or

Code:

EVERY 2 MINUTE

"EVERY 2 MINUTE" is enough and will be working properly.

Kyle 16/11/2014 04:26 PM

Re: MySQL - Event scheduler
 
Damn, this is pretty awesome. I didn't know about this before... If I ever go back into SA-MP, I'll remember this.

I do complete some events in PAWN and now I know about this, can do it in MySQL instead.

Thanks

Lordzy 16/11/2014 04:45 PM

Re: MySQL - Event scheduler
 
NOTE : I've added a small note regarding event_scheduler at the end of this tutorial.

Jay_ 16/11/2014 06:17 PM

Re: MySQL - Event scheduler
 
You learn something new every day - this is a feature I really did not know existed :)

I'm not really a fan of your example though - I haven't done any background reading around this yet so don't know how it works or performs, but surely it would be more efficient to check whether the user should be unbanned when they next attempt to join the server and login to their account.

Abagail 16/11/2014 07:46 PM

Re: MySQL - Event scheduler
 
Removed.

Mellnik 16/11/2014 08:35 PM

AW: MySQL - Event scheduler
 
This is really nice, can be used for many things.

Abagail 16/11/2014 09:36 PM

Re: MySQL - Event scheduler
 
Will something like this work?
Code:

new query[100];
new interval[30];
format(interval, sizeof(interval), "1 DAY");
mysql_format(connectionHandler, query, sizeof(query),
"CREATE EVENT `unbanall` ON SCHEDULE AT CURRENT_TIMESTAP() + INTERVAL '%s' \
DO \
UPDATE `mybans` SET `banSTATUS`=0", interval);


You seem to be an expert on this function, and I can't find anything relevant on the documentation page.


All times are GMT. The time now is 10:40 AM.

Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.