MySql Event Scheduler



What is an event? 

Event is a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time.

In Other word,

Events are tasks that run according to a schedule. It also called scheduled events.

It is similar to the idea of the Unix crontab (a.k.a. a cron job) or the Windows Task Scheduler.

  • An event is uniquely identified by its name and the schema to which it is assigned.
  • An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in BEGIN .. END;
  • An event’s timing can be either one-time or recurrent.

Support: ~

     Events have been supported in MySQL since version 5.1.*

Possible Values:~

  1. OFF (default Value).
  2. ON
  3. DISABLE
  • OFF : The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.
  • ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.
  • DISABLED: This value renders the Event Scheduler non-operational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.

Important:~

It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime.

Turn ON-OFF the event_scheduler:~

If the Event Scheduler status has not been set to DISABLED, event_scheduler can be toggled between 1/ON and 0/OFF (using SET).

Any of the following 4 statements can be used to turn on the Event Scheduler: (By default its value is OFF)

  1. SET GLOBAL event_scheduler = ON;
  2. SET @@global.event_scheduler = ON;
  3. SET GLOBAL event_scheduler = 1;
  4. SET @@global.event_scheduler = 1;

Similarly, any of these 4 statements can be used to turn off the Event Scheduler:

  1. SET GLOBAL event_scheduler = OFF;
  2. SET @@global.event_scheduler = OFF;
  3. SET GLOBAL event_scheduler = 0;
  4. SET @@global.event_scheduler = 0;

Also have to add:
event_scheduler=ON To the /etc/my.conf in the [mysqld] section.


Check status of the event_scheduler:~

SELECT @@event_scheduler;
SELECT @@GLOBAL.event_scheduler;

mysql> select @@GLOBAL.event_scheduler ;
+————————–+
| @@GLOBAL.event_scheduler |
+————————–+
| OFF                      |
+————————–+
1 row in set (0.00 sec)

When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process, and its state is represented as shown here:

mysql> SHOW PROCESSLIST;
Row Key
Data for First Row
Data for Second row
Id:
1
2
User:
root           
event_scheduler
Host:
localhost:49168
localhost      
db:
sam 
NULL
Command:
Query  
Daemon 
Time:
0
25
State:   
NULL
Waiting for next activation
Info:          
SHOW PROCESSLIST
NULL
2 rows in set (0.00 sec)
Syntax:~

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}
About Syntax:~
  • The DEFINER clause was added in MySQL 5.1.17.
  • The DEFINER clause specifies the MySQL account to be used when checking access privileges at event execution time. If a user value is given, it should be a MySQL account specified as ‘user_name’@’host_name’ (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE EVENT statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
  • If you specify the DEFINER clause, these rules determine the valid DEFINER user values.
  • If you do not have the SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
  • If you have the SUPER privilege, you can specify any syntactically valid account name. If the account does not actually exist, a warning is generated.
  • Although it is possible to create an event with a nonexistent DEFINER account, an error occurs at event execution time if the account does not exist.
  • IF NOT EXISTS has the same meaning for CREATE EVENT as for CREATE TABLE: If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.)
  • The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms:
  • AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a date time value. You may use a value of either the DATETIME or TIMESTAMP type for this purpose. If the date is in the past, a warning occurs.

NOTE:~
  • The event will not run unless the Event Scheduler is enabled. 

The minimum requirements for a valid CREATE EVENT statement are as follows:
  1. The keywords CREATE EVENT plus an event name, which uniquely identifies the event within a database schema. (Prior to MySQL 5.1.12, the event name needed to be unique only among events created by the same user within a schema.)
  2. An ON SCHEDULE clause, which determines when and how often the event executes.
  3. DO clause, which contains the SQL statement to be executed by an event.


Example1:~

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO

      UPDATE myschema.mytable SET mycol = mycol + 1;


Explanation.

The previous statement creates an event named myevent. This event executes once one hour following its creation by running an SQL statement that increments the value of the myschema.mytable table’s mycol column by 1.


Example2:~

+———————+
| NOW()               |
+———————+
| 2012-10-11 22:26:39 |
+———————+
1 row in set (0.03 sec)
mysql> CREATE EVENT past_event
    ->     ON SCHEDULE AT ‘2012-10-11 22:26:00’
    ->     DO INSERT INTO sam.user VALUES (NOW());
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
Level
Code
Message                                                                                                                     
Note 
1588
Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
1 row in set (0.00 sec)
To create an event, that occurs at some point in the future relative to the current date-time, use the optional clause [+ INTERVAL interval].

The interval portion consists of two parts, a quantity and a unit of V. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK
You can also combine intervals. For example,
 AT CURRENT_TIMESTAMP
+ INTERVAL 3 WEEK
+ INTERVAL 2 DAY
Each portion of such a clause must begin with + INTERVAL.
To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.)

For example, EVERY 6 WEEK

An EVERY clause may contain an optional STARTS clause. STARTS are followed by a timestamp value that indicates when the action should begin repeating, and may also use + INTERVAL interval to specify an amount of time. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means every three months, beginning one week from now. Similarly, you can express every two weeks, beginning six hours and fifteen minutes from now as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL ‘6:15’ HOUR_MINUTE.
Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP that is, the action specified for the event begins repeating immediately upon creation of the event.


Example3:~
mysql> CREATE EVENT insert_event ON SCHEDULE
EVERY 1 MINUTE
DO INSERT INTO User SELECT NOW();

Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE EVENT INSERT_EVENT\G

    Event : insert_event
 sql_mode:STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,
           NO_ENGINE_SUBSTITUTION

time_zone:SYSTEM

Create Event:CREATE DEFINER=`root`@`localhost` EVENT `
             insert_event` ON SCHEDULE EVERY 1 MINUTE 
             STARTS ‘2012-10-12 22:06:21’ ON COMPLETION
             NOT PRESERVE ENABLE DO INSERT INTO User SELECT NOW()
character_set_client:cp850
collation_connection:cp850_general_ci
Database Collation:utf8_general_ci

1 row in set (0.00 sec)

Notes :~
  • The event_name must be a valid MySQL identifier with a maximum length of 64 characters.
  • Event names are not case sensitive
  • An event is associated with a schema. If no schema is indicated as part of event_name, the default (current) schema is assumed.
  • An event is immediately dropped once its schedule has expired (ON COMPLETION NOT PRESERVE). Set ON COMPLETION PRESERVE to prevent that behavior. Using ON COMPLETION NOT PRESERVE merely makes the default non persistent behavior explicit.
  • You may use STARTS, ENDS, both, or neither in an EVERY clause.
  • If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.
  • You may supply a comment for an event using a COMMENT clause. Comment may be any string of up to 64 characters that you wish to use for describing the event. The comment text, being a string literal, must be surrounded by quotation marks.
  • A CREATE EVENT statement that contains an ALTER EVENT statement in its DO clause appears to succeed; however, when the server attempts to execute the resulting scheduled event, the execution fails with an error.

To disable my_event, use this ALTER EVENT statement:
  • ALTER EVENT my_event DISABLE; 

The schedule can be assigned various settings, e.g.

Syntax
Explanation
Example
AT ‘YYYY-MM-DD HH:MM.SS’
Run once on a specific date/time
AT ’2011-06-01 02:00.00’
AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]
Run once after a specific period has elapsed
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]
Run at specific intervals forever
EVERY 1 DAY
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date
ENDS date
Run at specific intervals during a specific period
EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK
ENDS
’2012-01-01 00:00.00′
References:~
  1. http://dev.mysql.com/doc/refman/5.1/en/events-overview.html
  2. http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html

How to Retrieve/Find Nth highest Value from a table?



I have a table in my mysql database and I want to find out the highest value, 2nd highest value, 3rd highest value and so on. Then how should I deal with this scenario?

Distance Table :~

mysql> desc Distance;
+—————–+—————+——+
| Field           | Type          | Null |
+—————–+—————+——+
| DistanceId      | bigint(20)    | NO   |
| ZipCode1        | varchar(45)   | NO   |
| ZipCode2        | varchar(45)   | NO   |
| DistanceInMiles | decimal(10,2) | NO   |
+—————–+—————+——+

9 rows in set (0.11 sec)


Records in Distance Table :~

mysql> select * from Distance order by DistanceInMiles ;

+————+———-+———-+—————–+
| DistanceId | ZipCode1 | ZipCode2 | DistanceInMiles |
+————+———-+———-+—————–+
|          1 | 95101    | 95101    |            0.00 |
|          2 | 95101    | 95108    |            0.00 |
|          3 | 44444    | 44444    |            0.00 |
|          8 | 44444    | 44451    |            8.66 |
|          5 | 44444    | 44446    |           11.27 |
|          6 | 44444    | 44449    |           13.85 |
|          7 | 44444    | 44450    |           19.59 |
|         13 | 44444    | 44460    |           20.84 |
|          9 | 44444    | 44452    |           23.17 |
|         10 | 44444    | 44453    |           24.52 |
|         11 | 44444    | 44454    |           29.88 |
|          4 | 44444    | 44445    |           30.08 |
|         12 | 44444    | 44455    |           33.48 |
+————+———-+———-+—————–+
13 rows in set (0.00 sec)

Find the Highest Record from the Table


mysql> select * from Distance order by DistanceInMiles desc limit 1;
+————+———-+———-+—————–+
| DistanceId | ZipCode1 | ZipCode2 | DistanceInMiles |
+————+———-+———-+—————–+
|         12 | 44444    | 44455    |           33.48 |
+————+———-+———-+—————–+
1 row in set (0.02 sec)


mysql> select Max(DistanceInMiles) from Distance;
+———————-+
| Max(DistanceInMiles) |
+———————-+
|                33.48 |
+———————-+
1 row in set (0.00 sec)

Find the 2nd Highest Record from the Table

mysql> select Max(DistanceInMiles) from Distance where DistanceInMiles < (select Max(DistanceInMiles) from Distance);

+———————-+

| Max(DistanceInMiles) |
+———————-+
|                30.08 |
+———————-+
1 row in set (0.00 sec)

Now, Find the Nth Highest Record from the Table.

mysql> SELECT DistanceInMiles FROM ( 
         SELECT DISTINCT DistanceInMiles 
           FROM Distance
             ORDER BY DistanceInMiles DESC limit N) a 
                ORDER BY DistanceInMiles limit 1;

where N = Nth Highest record you want to find from the table.


Cases :~ 

    N = 0 – Return an Empty Set
    N >= Total Number of records in Table – Will return MIN value from that table.

For example, if i want to find out the 5th Highest record from the Distance Table then, my Query will be,

mysql> SELECT DistanceInMiles FROM ( 
         SELECT DISTINCT DistanceInMiles 
           FROM Distance 
             ORDER BY DistanceInMiles DESC limit 5) a 
                ORDER BY DistanceInMiles limit 1;
+—————–+
| DistanceInMiles |
+—————–+
|           23.17 |
+—————–+
1 row in set (0.00 sec)


Exporting and Importing Databases


EXPORTING DATABASE


You will not need your administrator password to issue the mysqldump command. Nor will you have to use sudo to issue this command. So, to export your database open up a terminal window and issue the following command:
mysql > mysqldump -u USER -p DATABASE > FILENAME.sql

Where,
USER – MySQL administrator user.
DATABASE – Database you want to export.
FILENAME – Exported file name (Best, use database name for file name to avoiding confusion)

When you issue this command you will be prompted for the MySQL admin password. Enter that password and hit the Enter key. In the directory you issued the command you will now have a file with the .sql extension which is the file you then need to copy to your CD, DVD, or USB flash drive.

IMPORTING DATABASE


Now, you have that file on a removable media, transport that file to the new machine, insert the media, mount the media (if necessary), and copy the file to your users’ home directory. Next, open up a terminal window and issue the command:

mysql > mysql -u USER -p DATABASE < FILENAME.sql

Where,
USER – MySQL admin username, 
DATABASE – Name of the database to be imported.
FILENAME.sql – Dump that was exported from the initial machine.

You will be prompted for the MySQL administrator password and then, most likely, you will be returned to your prompt, sans errors.

That’s it. You have officially exported and imported a database from one machine to another.