SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 16/11/2014, 04:05 PM   #1
Lordzy
High-roller
 
Join Date: Mar 2012
Location: NetherRealm
Posts: 2,763
Reputation: 1175
Default 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:
pawn 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:
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.
Step #4:
You can see a dialog box showing the form of creating an event.


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.
pawn 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:
pawn 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.
pawn 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:
pawn 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:
pawn 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:
pawn 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:
pawn 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.
pawn 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".
pawn 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.
pawn 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.
pawn Code:
ALTER EVENT `event_name` ON SCHEDULE EVERY 1 HOUR;

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


pawn 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.
pawn 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
__________________
Currently inactive - I don't play at any SA-MP servers nor work on anything in PAWN for now. The projects that I've done so far in PAWN, which requires updates will be taking some time.

Last edited by Lordzy; 25/01/2017 at 03:48 PM. Reason: Added R40+ syntax (25th of December, 2016)
Lordzy is offline   Reply With Quote
Old 16/11/2014, 04:13 PM   #2
sammp
Gangsta
 
sammp's Avatar
 
Join Date: Jan 2014
Posts: 615
Reputation: 49
Default 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!
__________________
SA-MP must become open-source to maximise longetivity of the project.
sammp is offline   Reply With Quote
Old 16/11/2014, 04:16 PM   #3
sammp
Gangsta
 
sammp's Avatar
 
Join Date: Jan 2014
Posts: 615
Reputation: 49
Default Re: MySQL - Event scheduler

Also, look at this:

pawn Code:
EVERY 1 MINUTE

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

pawn Code:
EVERY 2 MINUTES

or

pawn Code:
EVERY 2 MINUTE
__________________
SA-MP must become open-source to maximise longetivity of the project.
sammp is offline   Reply With Quote
Old 16/11/2014, 04:26 PM   #4
Lordzy
High-roller
 
Join Date: Mar 2012
Location: NetherRealm
Posts: 2,763
Reputation: 1175
Default Re: MySQL - Event scheduler

Quote:
Originally Posted by sammp View Post
Also, look at this:

pawn Code:
EVERY 1 MINUTE

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

pawn Code:
EVERY 2 MINUTES

or

pawn Code:
EVERY 2 MINUTE
"EVERY 2 MINUTE" is enough and will be working properly.
__________________
Currently inactive - I don't play at any SA-MP servers nor work on anything in PAWN for now. The projects that I've done so far in PAWN, which requires updates will be taking some time.
Lordzy is offline   Reply With Quote
Old 16/11/2014, 04:26 PM   #5
Kyle
High-roller
 
Kyle's Avatar
 
Join Date: Mar 2009
Location: United Kingdom
Posts: 2,389
Reputation: 314
Default 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
Kyle is offline   Reply With Quote
Old 16/11/2014, 04:45 PM   #6
Lordzy
High-roller
 
Join Date: Mar 2012
Location: NetherRealm
Posts: 2,763
Reputation: 1175
Default Re: MySQL - Event scheduler

NOTE : I've added a small note regarding event_scheduler at the end of this tutorial.
__________________
Currently inactive - I don't play at any SA-MP servers nor work on anything in PAWN for now. The projects that I've done so far in PAWN, which requires updates will be taking some time.
Lordzy is offline   Reply With Quote
Old 16/11/2014, 06:17 PM   #7
Jay_
Beta Tester
 
Jay_'s Avatar
 
Join Date: Jul 2009
Posts: 659
Reputation: 188
Default 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.
Jay_ is offline   Reply With Quote
Old 16/11/2014, 07:46 PM   #8
Abagail
High-roller
 
Join Date: Dec 2013
Location: GetPlayerPos(playerid, pos); Scripter/Mapper.
Posts: 3,499
Reputation: 439
Default Re: MySQL - Event scheduler

Removed.
Abagail is offline   Reply With Quote
Old 16/11/2014, 08:35 PM   #9
Mellnik
Gangsta
 
Mellnik's Avatar
 
Join Date: Dec 2011
Location: Paradise Falls, CAℕADA
Posts: 794
Reputation: 369
Default AW: MySQL - Event scheduler

This is really nice, can be used for many things.
Mellnik is offline   Reply With Quote
Old 16/11/2014, 09:36 PM   #10
Abagail
High-roller
 
Join Date: Dec 2013
Location: GetPlayerPos(playerid, pos); Scripter/Mapper.
Posts: 3,499
Reputation: 439
Default Re: MySQL - Event scheduler

Will something like this work?
pawn 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.
Abagail is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[FilterScript] Rajat's Event system! (Lockable, Event wins, guns) RajatPawar Filterscripts 14 07/03/2015 06:30 PM
[FilterScript] S-Event (Advanced Event System) marcelodell Lançamentos/Releases 54 12/04/2014 03:54 AM
Task scheduler Dragony92 Scripting Help 4 17/02/2012 12:35 AM
How to make /event start an actual event Snowman12 Help Archive 7 03/04/2011 11:39 PM


All times are GMT. The time now is 07:17 PM.


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