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. A 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.

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.

Singleton Class in Objective-C/iOS

Background :~

Have you ever wanted to share data between views, but couldn't figure it out? Now, here's a solution called - Singleton Class.

Singletons is an easy way to share data and common methods within your entire application.

Singleton class is used when you need to ensure that only one instance of the class can be instantiated and you need a global access for that.

Singleton classes are an important concept to understand because they exhibit an extremely useful design pattern.

Examples in UIKit:

1. [UIApplication sharedApplication] - return the single instance of the app.
2. [NSFileManager defaultManager] - returns the single instance of the file manager.
   
   

Implementation :~

Utility.h

#import <Foundation/Foundation.h>

@interface Utility : NSObject

+ (Utility *)sharedHandler ;

Utility.m

#import "Utility.h"

@implementation Utility

+ (Utility *)sharedHandler {

static dispatch_once_t onceToken;

static Utility *sharedHandler = nil;

dispatch_once(&onceToken, ^{

sharedHandler = [[self alloc] init];

});

return sharedHandler;

}

How to Use :~

Utility *singletonObject = [Utility sharedHandler];

Java SE 7 Feature: A Numeric Underscores with Binary Literals

Hello folks,

     In this tutorial, we will discuss about underscore ( _ ) in Literals feature added in Java SE7. Prior to Java SE 7, declaring numeric literals with underscores interspersed within the digits would causes a compile time error. But in JDK7, numeric literals with underscore characters are not only legal, but they're highly encouraged to increase readability of code. This is a nice and small feature.

Underscore can be added anywhere in the value part with few constraints listed below,

Rules for using Numeric number with Underscores in Java 7

1. Underscores ( _ ) can't go at the beginning or the end of a number.
2. You can't use an underscore on either side of a decimal. 
3. The underscore cannot go before an identifying suffix such as F, D or L
4. You can't put an underscore before or after the binary or hexadecimal identifiers b and x.

Example of valid literals:

long creditCardNumber	= 1234_5678_9012_3456L;
long socialSecurityNumber	= 999_99_9999L;
float pi	= 3.14_15F;
long hexBytes	= 0xFF_EC_DE_5E;
long hexWords	= 0xCAFE_BABE;
long maxLong	= 0x7fff_ffff_ffff_ffffL;
byte nybbles	= 0b0010_0101;
long bytes	= 0b11010010_01101001_10010100_10010010;

Example of invalid literals with reason:

int a1     = _52;                             // This is an identifier, not a numeric literal

int a2     = 52_;                             // cannot put underscores at the end of a literal

int a3     = 0_x52;                         // cannot put underscores in the 0x radix prefix

int a4     = 0x_52;                         // cannot put underscores at the beginning of a number

float pi   = 3._1415F;                   // cannot put underscores adjacent to a decimal point

long ssn = 999_99_9999_L;        // cannot put underscores prior to an L suffix

In Java SE 7, the integral types (byte, short, int, and long) can also be expressed using the binary number system. To specify a binary literal, add the prefix 0b or 0B to the number. This makes it easier to read code which uses bitwise operations. The following examples show binary literals:

int x = Integer.parseInt("1000", 2); //previously, you would have had to do it like this:

int eight = 0b1000; //in jdk7, you can create a binary literal like this:

int four = 0b1000>>1;      //easier to read bitwise operations

There is not limit in underscore. You can use as many underscore as you want. Only reason I see to use any number of underscores, is to be able to do fancy stuff like in the following piece of code (created by Joshua Bloch, if I'm not mistaken):

private static final int BOND =

     0000_____________0000________0000000000000000__000000000000000000+

   00000000_________00000000______000000000000000__0000000000000000000+

  000____000_______000____000_____000_______0000__00______0+

 000______000_____000______000_____________0000___00______0+

0000______0000___0000______0000___________0000_____0_____0+

0000______0000___0000______0000__________0000___________0+

0000______0000___0000______0000_________0000__0000000000+

0000______0000___0000______0000________0000+

 000______000_____000______000________0000+

  000____000_______000____000_______00000+

   00000000_________00000000_______0000000+

     0000_____________0000________000000007;

FEATURE SUMMARY:

Java numeric literals will allow underscores to be placed in (nearly) arbitrary positions within the number, at the programmer's discretion, for readability purposes. These underscores shall be ignored by the compiler for the purposes of code generation.

MAJOR BENEFIT:

1. Increased readability of code.

REFERENCE:

1. http://docs.oracle.com/javase/7/docs/technotes/guides/language/underscores-literals.html
2. http://docs.oracle.com/javase/7/docs/technotes/guides/language/binary-literals.html

Java Versions, Features and History

On this "Java Version History" describes about the history of different java version.

From it's inception, java language is undergone several changes.

From version 1.4, Java language development is undergone according to the rules of Java Community Process (JCP), and they uses Java Specification Requests(JSR) to propose and specify improvements and changes to the Java Language.

Java Versions :=

• JDK 1.0 (1996, January 23), Codename Oak. [8 packages with 212 classes]
• JDK 1.1 (1997, February 19) [23 packages with 504 classes]
• J2SE 1.2 (1998, December 8), Codename Playground. [59 packages with 1520 classes]
• J2SE 1.3 (2000, May 8 ), Codename Kestrel. [76 packages with 1842 classes]
• J2SE 1.4 (2002, February 6), Codename Merlin. [135 packages with 2991 classes]
• J2SE 5.0 (2004, September 30), Codename Tiger. [166 packages, over 3279 classes]
• Java SE 6 (2006, December 11), Codename Mustang. [203 packages with 3793 classes]
• Java SE 7 (2011, July 28), Codename Dolphin. [209 packages with 4024 classes]

Java Features :=

1. Java SE 7

• 
  

  [ Code named Dolphin ]

• New features in Java SE 7

• Strings in switch Statement
• Type Inference for Generic Instance Creation
• Multiple Exception Handling
• Support for Dynamic Languages
• Try with Resources
• Java nio Package
• Binary Literals, underscore in literals
• Diamond Syntax
• Automatic null Handling

2. Java SE 6

[Code named Mustang]

• New features in Java SE 6

• Scripting Language Support
• JDBC 4.0 API
• Java Compiler API
• Pluggable Annotations
• Native PKI, Java GSS, Kerberos and LDAP support.
• Integrated Web Services.
• Lot more enhancements.

3. J2SE Version 5.0

[Code named Tiger]

• New features in Java SE 5.0

• Generics
• Enhanced for Loop
• Autoboxing/Unboxing
• Typesafe Enums
• Varargs
• Static Import
• Metadata (Annotations)
• Instrumentation

4. J2SE Version 1.4

[Code named Merlin]

• New features in Java SE 1.4

• XML Processing
• Java Print Service
• Logging API
• Java Web Start
• JDBC 3.0 API
• Assertions
• Preferences API
• Chained Exception
• IPv6 Support
• Regular Expressions
• Image I/O API

5. J2SE Version 1.3

[Code named Kestrel]

• New features in Java SE 1.3

• Java Sound
• Jar Indexing
• A huge list of enhancements in almost all the java area.

6. J2SE Version 1.2

Code named Playground and released on December 8, 1998.

• New features in Java SE 1.2

• Collections framework.
• Java String memory map for constants.
• Just In Time (JIT) compiler.
• Jar Signer for signing Java ARchive (JAR) files.
• Policy Tool for granting access to system resources.
• Java Foundation Classes (JFC) which consists of Swing 1.0, Drag and Drop, and Java 2D class libraries.
• Java Plug-in
• Scrollable result sets, BLOB, CLOB, batch update, user-defined types in JDBC.
• Audio support in Applets.

7. JDK Version 1.1

New features in Java SE 1.1

• JDBC (Java Database Connectivity)
• Inner Classes
• Java Beans
• RMI (Remote Method Invocation)
• Reflection (introspection only)

8. J2SE Version 1.0

Codenamed Oak and released on January 23, 1996.

Reference Link:=

1. http://javapapers.com/core-java/java-features-and-history/