[Lordzy]

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) - https://sampforum.blast.hk/showthread.php?tid=511737

[sammp]

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

Nice work!

[sammp]

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

[Lordzy]

Quote:

Originally Posted by sammp 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.

[Kyle]

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]

NOTE : I've added a small note regarding event_scheduler at the end of this tutorial.

[Jay_]

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]

Removed.

[Mellnik]

This is really nice, can be used for many things.

[Abagail]

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.

[jamjamnewbie]

What the.. This is the first time I've seen this function

pawn Code:

mysql_tquery(connectionHandler,
"CREATE EVENT `unbanall` ON SCHEDULE AT CURRENT_TIMESTAP() + 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.

TIMESTAP or TIMESTAMP?

Anyways, good tutorial, can be used for many things, I know

[Abagail]

TIMESTAMP, - probably just a typo. I could be wrong though.

[Lordzy]

Quote:

Originally Posted by Abagail 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.

Yes it will work because the output query would look like : CREATE EVENT `unbanall` ON SCHEDULE AT CURRENT_TIMESTAMP() + INTERVAL 1 DAY DO UPDATE `mybans` SET `banSTATUS`=0
Though, I believe query's size should be increased.

NOTE : It's CURRENT_TIMESTAMP, not TIMESTAP because I had a little typo on my main post.

Quote:

Originally Posted by jamjamnewbie What the.. This is the first time I've seen this function pawn Code: mysql_tquery(connectionHandler, "CREATE EVENT `unbanall` ON SCHEDULE AT CURRENT_TIMESTAP() + 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. TIMESTAP or TIMESTAMP? Anyways, good tutorial, can be used for many things, I know

It's CURRENT_TIMESTAMP. STAP was little typo there.

[Abagail]

Quote:

Originally Posted by Lordzy Yes it will work because the output query would look like : CREATE EVENT `unbanall` ON SCHEDULE AT CURRENT_TIMESTAMP() + INTERVAL 1 DAY DO UPDATE `mybans` SET `banSTATUS`=0 Though, I believe query's size should be increased. NOTE : It's CURRENT_TIMESTAMP, not TIMESTAP because I had a little typo on my main post. It's CURRENT_TIMESTAMP. STAP was little typo there.

Thank you for clarifying this. Another question though, is this a new MySQL / phpmyadmin feature? I don't seem to see it on my phpMyAdmin, is this just a matter of updating it?

[Lordzy]

Quote:

Originally Posted by Abagail Thank you for clarifying this. Another question though, is this a new MySQL / phpmyadmin feature? I don't seem to see it on my phpMyAdmin, is this just a matter of updating it?

The event scheduler system had been supported since MySQL 5.1. You should also make sure if the user you use has got privileges to use "Events". Or else simply create an event using SQL command and then it might appear on as a tab. Also note that you should click on your database to view Events.

[Abagail]

The last one was the one I needed. Thanks for the fast reply.
If I have any time tommorow I may make a temporary ban system using events(I coded one without using events a few days ago).

Cheers!

[Raefal]

Nice tutorial i need it for delete unused character!, Thanks.

[RajatPawar]

Nice! And thank you for the credits

You could add some more information about auto-backing up (which is also there in my topic) if we are on the topic of periodic functions!

[Lordzy]

Quote:

Originally Posted by RajatPawar Nice! And thank you for the credits You could add some more information about auto-backing up (which is also there in my topic) if we are on the topic of periodic functions!

I thought about adding an example of automatic backups but since it's a tutorial in regarding event scheduler of MySQL, I thought to explain more regarding the scheduler. I might consider creating a tutorial using the same method for automatic backups of tables later. I should actually add some more about this topic because it's a shame that I've forgotten to add the methods of listing events through SQL commands and dropping down events if not needed, through queries.

I will be adding some more information regarding this when I'm getting some free time.

[CoaPsyFactor]

you should create tutorial for TRASACTION and ROLLBACK