Working with Events in MySQL

Question

I have a stored procedure which basically selects data from one table and insert into another table. Basically I am doing data archiving manually. Now, I want to write an event just like discussed here

However, after reading that post and researching online, I came to know that it's not possible to create an event inside a stored procedure. Is there a way to accomplish my goal in MySQLWorkbench?

I think there are two possible approaches: A trigger (that is executed when an insert, update or delete happens on a table) or a scheduled event... which one are you trying to do? — Barranka, Jul 02 '14 at 23:16
@Barranka I am referring to Scheduled event. I want to run my stored procedure once a day everyday. — John, Jul 02 '14 at 23:20
Reference: [Event Scheduler overview](http://dev.mysql.com/doc/refman/5.1/en/events-overview.html) — Barranka, Jul 02 '14 at 23:24
Quoting the Reference Manual (specifically [`CREATE EVENT` syntax](http://dev.mysql.com/doc/refman/5.1/en/create-event.html)): *"There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters within an event*" — Barranka, Jul 02 '14 at 23:29
The problem is that in the Object Browser window,MySQL Workbench has only three options listed below a database 1) Tables 2) Views 3)Routines. Where precisely I should write Create Event Syntax? If I edit a routine with Create Event syntax, it doesn't work. — John, Jul 02 '14 at 23:34
Quoting a dumb line from a dumb movie: "Just because you don't see it doesn't mean it isn't there" :P Write it yourself (and keep a copy of the reference manual at hand) — Barranka, Jul 02 '14 at 23:37

Barranka · Accepted Answer · 2014-07-03T00:01:34.003

I believe you are thinking this in the oposite direction: You can't create an event in a stored procedure, but you can create a stored procedure and call it from an event.

Example:

delimiter $$
create procedure myProc()
-- Dummy procedure to move the data from mainTable to backupTable, 
-- and then clear (truncate) mainTable
begin
    insert into backupTable select * from mainTable;
    truncate mainTable;
end $$
delimiter ;

-- Now, suposing that you want to execute this procedure every hour:
delimiter $$
create event myEvent
    on schedule every 1 hour
    do
        begin
            call myProc();
        end $$
delimiter ;

You can write this as any other query in the workbench, or directly in the command line client.

About your concern

After reading your comment, I believe you are a bit confused about what MySQL Workbench is.

MySQL Workbench is only a graphical application that allows you to connect to a MySQL server and perform queries and administration tasks. But Workbench is not the core of MySQL... it is only a viewer (with steroids, maybe, but a viewer after all).

Now, the event scheduler does not reside in Workbench, but in the MySQL server instance you are connecting to. Just as the tables, views, procedures and functions are not stored in the Workbench interface but in the server, the events are also stored in the server.

(Yes, I believe it is a relevant SNAFU that the scheduled events don't show anywhere in the graphical interface, but... after a while, one learns to live with that kind of frustrations and to move on with life)

Maybe your only concern is: "Hey, and what if I want to know what events are set to run in the event scheduler?" You can execute a "show events" query to show a list of the events in the current database, and you can execute "show create event yourEvent" to show the create event syntax for that event.

I insist: Read the manual, and keep a copy at hand (download the manual for your MySQL version here).

When I do exactly what you have mentioned inside a stored procedure and click on "Apply" for saving the changes on MySQLWorkbench, I am getting a message in the Output window :` "No Changes Detected" `and the changes are not getting saved. — John, Jul 02 '14 at 23:44
Are you trying to write this in a "new procedure" window? why don't you write it as a regular query and execute it? — Barranka, Jul 02 '14 at 23:46
I have a procedure `myProc()` and I am editing this procedure by `ALTER PROCEDURE` command and then adding the CREATE EVENT SYNTAX. So, you mean I should just execute CREATE EVENT syntax in the `Query` window? I am wondering how would mysql remember that the stored procedure needs to be executed every hour because the contents in the Query window gets erased when I close the MySQLWorkbench. Please let me know if I have misunderstood something. — John, Jul 02 '14 at 23:52
Yes, write it in the query window... and allow me one minute to edit my answer to address the other part of your comment — Barranka, Jul 02 '14 at 23:54
Thanks @Barranks for very nice explanation. I have some other issues with the event I created which I will mention in my new question soon. — John, Jul 11 '14 at 18:41

Working with Events in MySQL

1 Answers1

Linked