Events

An Event is a scheduled job executed by the MySQL server itself. Events are supporoted from MySQL 5.1. If your MySQL version does not support events you should note that SQLyog has the 'Notifications Service' facility which in most situations will make it possible for you to achieve the same.

The differences between Events and Notifications Service can be summarized as follows:

  • Events require specific server versions - Notifications Service works with every MySQL version.
  • Events are executed by the server without the need of any external client to connect (but the server does actually start a client instance internally) - Notifications Service creates an ordinary client connection.
  • Events may only execute SQL statements. It cannot perform file system operations, invoke external binaries etc. - Notifications Service can only execute SQL. As it is managed by the Operating System and not the MySQL server, it can be executed by a batch job or an application which copies files.
  • Events have no notifications feature - Notifications Service has (that is why it is named as it is!).

You can see that each has advantages and drawbacks. They may also be combined. For instance you can have an Event scheduled at close interval (say 1 minute) that updates a table used by a Notifications Service Job executed at larger intervals (like once per hours).

Create Event

To create a new event, use the menu item Others -> Event-> Create Event or use the database context menu.

Enter the event name in the appeared dialog, and SQLyog will present a template for your Event in a separate tab of the SQL Window. The template contains a 'skeleton' for defining three common types of Event:

  • Events that runs only once
  • Events that run forever after creating
  • Events defined to start and/or stop at a specified time.

Uncomment the one that is closest to what you want and also uncomment any non-default options you want to use for the Event.

Use the toolbar buttons or hotkeys to execute the queries. As editor tabs of a special kind are used for 'stored programs' creation/alteration, pressing both F5 and Shift+F5 keys leads to execution of all queries present in the SQL window.

To create a useful Event there must of course be some code in between the BEGIN .. END sequence. That can be any valid SQL. If there are more SQL statements here they must be separated by ";" (semicolon). To conclude the Event code you must use a user-defined DELIMITER as the default SQL delimiter (;) is used between BEGIN... END internally in the Event code . More details about this in the paragraph on delimiters.

Alter Event

To alter an existing event, right-click it in the Object browser or use the menu item Others -> Event -> Alter Event or press F6.

The current Event definiton will appear in a separate tab of the SQL Window. Change what you want and next use the toolbar buttons or hotkeys to execute the queries. As editor tabs of a special kind are used for 'stored programs' creation/alteration, pressing both F5 and Shift+F5 keys leads to execution of all queries present in the SQL window.

If you want to use the template for Create Event you may also open the Create Event template and simply replace the word CREATE with ALTER.

The first option is easiest if you only want to change a few details of the Event code. If you want to completely redesign the schedule you may prefer the second option.

Drop Event

To drop an existing event, right click it in the Object browser and use the menu item Others -> Event -> Drop Event. Then confirm the operation in the appeared dialog.

Rename Event

To rename an event, right-click it in the Object browser and use the menu item Others -> Event -> Rename Event or simply click its name in the Object browser or press F2. 'Rename Event' is special variation of 'Alter Event'.