Mysql - How to make a Stored Process be activated once a day?

Asked

Viewed 842 times

0

I need a Stored Procedure to be called every day at 11:59 pm but I’ve tried a few ways and when it arrives at that time it doesn’t perform!

    CREATE EVENT `ATUALIZARDADOS` ON SCHEDULE
    EVERY 1 DAY STARTS '2017-11-16 23:59:00' ON COMPLETION PRESERVE
    DO 
    begin
    CALL MULTAOFF();
    CALL RESERVAOFF();
    INSERT INTO configuracao (id,DiasMulta,DiasLivroMaior,DiasLivroMenor) 
    values ('8','2','3','4');
    end &&
    delimiter ;

detail, this excerpt was only so I can know if the event is working or not)

    INSERT INTO configuracao (id,DiasMulta,DiasLivroMaior,DiasLivroMenor) 
    values ('8','2','3','4');

someone can help me with this problem?

mysql sql database events

  • Checked if you have sufficient privileges to schedule an event? If you have done so here SET GLOBAL event_scheduler = ON; before using the event scheduler?

1 answer

1

You will need to create a job that will run this process.

Job

JOB is a way to assist in maintenance with tasks that must be performed several times equal or on a particular date without needing the intervention of the DBA, for example, the defragmentation of a table on Sunday. As on Sunday no one will be in the company can schedule the work to be performed in that period.

Jobs can be created in two ways either through Enterprise manager or by T-SQL, in this article I will show two ways we can use to create a JOB.

Example

Creation will be something along these lines:

CREATE 
    EVENT `archive_blogs` 
    ON SCHEDULE EVERY 1 WEEK STARTS '2011-07-24 03:00:00' 
    DO BEGIN

        -- copy deleted posts
        INSERT INTO blog_archive (id, title, content) 
        SELECT id, title, content
        FROM blog
        WHERE deleted = 1;

        -- copy associated audit records
        INSERT INTO audit_archive (id, blog_id, changetype, changetime) 
        SELECT audit.id, audit.blog_id, audit.changetype, audit.changetime 
        FROM audit
        JOIN blog ON audit.blog_id = blog.id
        WHERE blog.deleted = 1;

        -- remove deleted blogs and audit entries
        DELETE FROM blog WHERE deleted = 1;

    END */$$

DELIMITER ;

You can put into the body absolutely any valid code execution.

Read more

Browser other questions tagged mysql sql database events

You are not signed in. Login or sign up in order to post.