MySQL Event Scheduler



If you are running a large web application then you must have lots of data(unwanted data also).For example, spam emails,unused records of the users.All these spamy and junk data will cause problem of database overload and backups.Its performance will also affected.So what could be the solution to overcome this problem.The solution is topic of this post called “MySQL Event Scheduler”.

From the release of MySQL 5.1.6 comes the inclusion of a scheduler. Perfect name "The scheduler" that schedules tasks within the DB.

When you will need MySQL Events?

(1) To delete junk records automatically on regular interval of time. E.g. delete every week.
(2) Used to Call a stored procedure at specific time.
(3) Automatically perform any scheduled SQL operation.

Three basic steps are required to schedule a task.

• Event Name
• Event Interval
• SQL Statement

Create a Table – Cart


CREATE TABLE cart(
cartID INT AUTO_INCREMENT ,
userID INT,
product_id INT,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( cartID )
);


Turn Scheduler On

The scheduler is a backend process. Basically this means a separate thread runs constantly behind the scenes looking for events run. For this to happen, the MySQL GLOBAL variable event_scheduler must be set to ON. This is done with the following SQL statement.

SET GLOBAL event_scheduler = ON;
Or
SET GLOBAL event_scheduler = 1;


Schedule An Event

There are several options available to define when an event is to be executed.The schedule could be a MYSQL TIMESTAMP or may be an INTERVAL. Most of you are familiar with the MYSQL TIMESTAMP format as 2010-05-20 11:30:00. The interval can be any of the following.

•    YEAR
•    MONTH
•    WEEK
•    DAY
•    HOUR
•    MINUTE
•    SECOND

Create an Event

An event is created using the CREATE EVENT syntax. The event name could be a string of up to 64 characters.Here the following event will run everyday and clear/delete 10 days old data from cart table based on time stamp.

CREATE EVENT newEvent
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM cart WHERE created <= DATE_SUB(NOW(), INTERVAL 10 DAY) ;


ALTER An Event

Up to this point, events have been created with various time intervals and frequencies. In order to edit an event, the ALTER EVENT statement is used as follows.

ALTER newEvent
ON SCHEDULE EVERY 5 HOUR
STARTS TIMESTAMP + 3 HOUR

DROP An Event

To remove of DROP an event is similar to all MySQL DROP statements, in that the syntax follows the same.

DROP EVENT newEvent;

Of course, if the event named "newEvent" does not exist, MySQL will return an error. To avoid this, just as with other DROP statements, the use of IF EXISTS is recommended.

DROP EVENT IF EXISTS newEvent;


View Event

Show all the running events.

SHOW EVENTS;


Comments

Post a Comment

Popular posts from this blog

How to show only month and year fields in android Date-picker?

Image
Android default DatePicker show day/month/year fields but you have to customize this DatePicker as your requirements. Suppose that you want to show only month and year fields in android datepicker . To do this, you have to customize the Dialog DatePicker source code but it is not an easy task. In this tutorial I will show you a tricky way to solve this problem. Lets see how to show only month and year field in android DatePicker as a dialog.   Step to show only month and year fields in android date-picker: Some days ago I got this same problem and here I am trying to explain this in a sequential manner. I want to show a dialog datepicker when user click on an EditText. So if user click on EditText, A dialog datepicker will be invocked and it will show only month and year fields. To do this, at first we have to create a xml layout to hold the EditText fields. So create an android project and name it DatePickerExample. In the main.xml file just try to write this simple code....
Read more

How to construct a B+ tree with example

Image
A B+ tree contains n-1 search key values K1K2…….Kn-1 and have n pointers.   In a B+ tree the data are ordered  sequentially.  The leaf node may hold up to n pointers and must hold at least N/2 pointers. The root node must hold at least two pointers, unless the tree consists of only one node.  Now we construct a B+ tree. Construct a B+ tree for the following set of key values: (2,3,5,7,11,17,19,23,29,31) Assume that the tree is initially empty and values are added in ascending order. Construct a B+ tree where the pointer number is Four. Solution: When a node exceeds n-1 search key value then we split it into two nodes. First node contains (ceiling(n-1)/2) values.  2 nd node contains remaining node. Copy the smallest search key value of the second node to the parent node. Let's start, we know each node has N pointer and has N-1 search key values. In our example pointer number is 4, so the search key value is 4-1=3. So each node has 4 pointers and 3 search key val...
Read more

Conflict Serializability in database

Image
To maintains database state consistency we introduce serializability. Using serializability we can understand which schedule will ensure consistency, and which schedule will not. When we are capable of converting a concurrent schedule into a serial schedule then we cal it serializable schedule. A schedule is serializable if it is equivalent to a serial schedule. We divided this concepts into two parts 1. Conflict serializability  & 2. View serializability In this discussion I just clarify you the conflict serializability. Consider two instruction read, write and the data item A and B. When two instruction execute different data item such as read(A) and read(B), then we can swap the instruction without affecting the result of the schedule. But when the data item is equivalent to one another we need to consider following four condition.    a.   read (A ),    read ( A ).   they   don’t conflict.    b.   read ( Q),  ...
Read more