Scheduled Events

Start Omhoog Stored Routines Triggers Scheduled Events Performance Import and Export Data Storage Engines Transactions Server Administration Users and Access Control Oefeningen Database Schema's

Een andere benadering van database-automatisering zijn de scheduled events, welke op bepaalde tijden geïnitieerd worden. Zij voorzien een kader waarin een of meer SQL bewerkingen op een tijd gebaseerd schema uitgevoerd zullen worden.

  • altijd gekoppeld aan een bepaalde tafel
  • kan ingesteld worden om eenmalig uitgevoerd te worden of herhaaldelijk op ingestelde intervallen
  • Dit kan van pas komen voor taken die moeten periodiek plaatsvinden, zoals een log rotatie, het genereren van statistieken, of om updates te verwerken.

    Een eenvoudig voorbeeld: het archiveren van oude passagiersgegevens

    Alle passagiers automatisch verplaatst naar vluchten die 30 dagen oud uit de pax tafel en in een ander archief tabel:

    MSSQL MySQL
     
    Command(s) completed successfully.
    DELIMITER //
    CREATE EVENT pax_day
    ON SCHEDULE EVERY 1 DAY
    STARTS '2009-01-14 22:45:00'
    ENABLE
    DO
    BEGIN
        INSERT INTO paxarchive
        SELECT * FROM pax
        WHERE 
            FlightDate <= DATE_SUB(CURRENT_DATE(), 
            INTERVAL 30 DAY);
        DELETE FROM pax 
        WHERE
            FlightDate <= DATE_SUB(CURRENT_DATE(),
            INTERVAL 30 DAY);
    END //
    (0 row(s) affected)

    Om een scheduled event te definiëren, biedt MySQL het commando CREATE EVENT. Deze opdracht moet worden gevolgd door:

  • de naam van de event,
  • de planning van de event, (bijvoorbeeld ON SCHEDULE EVERY 1 DAY & STARTS date and time)
  • een actieve / inactieve vlag, en (inschakelen / uitschakelen)
  • het hoofdgedeelte, welke de SQL-instructies bevatten die uitgevoerd moeten worden wanneer de event optreedt (de DO-clausule, bevat ofwel één SQL-instructie of meerdere SQL-instructies die omsloten worden door een BEGIN ... END blok).
  • Standaard is de event scheduling engine van MySQL uitgeschakeld. Met de volgende deze instructie start u de globale event scheduler, welke periodiek controleert of er scheduled events zijn die uitgevoerd moeten worden.

    SET GLOBAL event_scheduler = ON;
    (0 row(s) affected)

    Opmerking: Om een scheduled event te maken, moet een gebruiker het EVENT privilege hebben. Om de globale event scheduler in- of uit te schakelen, moet een gebruiker het SUPER privilege hebben.

    Om een schedule event te wijzigen, gebruikt u de opdracht ALTER EVENT en voorziet u nieuwe parameters voor het event. Hier is een voorbeeld, die de vorige event aanpast zodat het om de twee uur uitgevoerd wordt:

    MSSQL MySQL
     
    Command(s) completed successfully.
    DELIMITER //
    ALTER EVENT pax_day
    ON SCHEDULE EVERY 2 HOUR
    STARTS '2009-01-14 22:45:00'
    ENABLE
    DO
    BEGIN
        INSERT INTO paxarchive
        SELECT * FROM pax
        WHERE 
            FlightDate <= DATE_SUB(CURRENT_DATE(), 
            INTERVAL 30 DAY);
        DELETE FROM pax 
        WHERE
            FlightDate <= DATE_SUB(CURRENT_DATE(),
            INTERVAL 30 DAY);
    END //
    (0 row(s) affected)

    Drop scheduled events, disable scheduled events

    Standaard wordt, zodra een gebeurtenis is voltooid, deze automatisch door de event scheduler verwijderd van de event wachtrij. U kunt deze ook op elk gewenst moment handmatig verwijderen door gebruik te maken van de DROP EVENT instructie gevolgd door de naam van de event:

    MSSQL MySQL
     
    Command(s) completed successfully.
    DROP EVENT pax_day;
    (0 row(s) affected)

    Tip: Om te voorkomen dat een event automatisch wordt verwijderd uit de event wachtrij als het eenmaal is voltooid (voor redenen zoald audit enz.), voegt u een ON COMPLETION PRESERVE clausule toe aan de CREATE EVENT opdracht.

    Als u alle scheduled events uit te schakelen, stopt u de globale event scheduler met de volgende instructie.

    SET GLOBAL event_scheduler = OFF;
    (0 row(s) affected)

    U kan een specifieke scheduled event uitschakelen (uitgeschakeld events zullen in het geheel niet optreden) door gebruik te maken van volgende instructie.

    MSSQL MySQL
     
    Command(s) completed successfully.
    ALTER EVENT pax_day DISABLE;
    (0 row(s) affected)

    Om het hoofdegedeelte van een bepaalde event te bekijken, gebruikt u de SHOW CREATE EVENT commando gevolgd door de de naam van de gebeurtenis.

    MSSQL MySQL
     
    Command(s) completed successfully.
    SHOW CREATE EVENT pax_day;
    (1 row(s) returned)

    Om een lijst van alle event die op de server staan te bekijken, gebruikt u de opdracht SHOW EVENTS.

    MSSQL MySQL
     
    Command(s) completed successfully.
    SHOW EVENTS;
    (1 row(s) returned)
    RecordID ByUser Note EventTime
    1 1 DOMAIN\
    domainuser
    Record added: flight 2016-04-22 21:51:45.0500000
    RecordID ByUser Note EventTime
    1 1 root@% Record added: flight 2016-04-22 21:04:06

    Event Security: DEFINER

    De CREATE EVENT instructie ondersteunt een DEFINER clausule, waarmee aangegeven wordt welke privileges van het gebruikers-account moeten worden gebruikt bij het uitvoeren van de event code. Zodat een event met success uitgevoerd kan worden dient de gebruiker over de nodige rechten te bezitten die nodig zijn om de instructies te kunnen uitvoeren die opgenomen zijn in het hoofdgedeelte van de event. Standaard stelt MySQL de waarde van de DEFINER in op gebruiker die het evenement gecreëerd heeft.

    MSSQL MySQL
     
    Command(s) completed successfully.
    DELIMITER //
    CREATE
    DEFINER = 'someone@example.com'
    EVENT pax_day
    ON SCHEDULE EVERY 1 DAY
    STARTS '2009-01-14 22:45:00'
    ENABLE
    DO
    BEGIN
        INSERT INTO paxarchive
        SELECT * FROM pax
        WHERE 
            FlightDate <= DATE_SUB(CURRENT_DATE(), 
            INTERVAL 30 DAY);
        DELETE FROM pax 
        WHERE
            FlightDate <= DATE_SUB(CURRENT_DATE(),
            INTERVAL 30 DAY);
    END //
    (0 row(s) affected)

    Recurring Events

    Laten we de terugkerende events nader bekijken. Zoals in de vorige paragraaf geïllustreerd is, bevat een terugkerend event de EVERY clausule in de event definitie; deze clausule geeft aan dat MySQL het event "elke XX tijdeenheden" moet herhalen. De EVERY clausule bevat ook het herhalings interval, deze bestaat uit een nummer en een sleutelwoord die de tijdseenheid aangeeft. Geldige tijdseenheden zijn onder meer YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, WEEK en SECOND.

    Hier is een voorbeeld, waarbij elk uur voor elke vlucht het percentage van de zetels wordt gecontroleerd die gereserveerd zijn en vluchten die meer dan 80 procent vol zijn registereert.

    MSSQL MySQL
     
    Command(s) completed successfully.
    DELIMITER //
    CREATE EVENT util_hour
    ON SCHEDULE EVERY 1 HOUR
    ENABLE
    DO
    BEGIN
        DECLARE fid INT;
        DECLARE fdate DATE;
        DECLARE str TEXT DEFAULT '';
        DECLARE util FLOAT;
        DECLARE c CURSOR FOR
        SELECT s.FlightID, s.FlightDate, 1-
            (SUM(s.CurrSeats) /
            (SELECT SUM(fc.MaxSeats)
            FROM flightclass AS fc
            WHERE fc.FlightID = s.FlightID
            GROUP BY FlightID))
            AS u
        FROM stats AS s
        GROUP BY s.FlightID, s.FlightDate
        HAVING u > 0.80;
        OPEN c;
        l: LOOP
            FETCH c INTO fid,fdate,util;
            SET str = CONCAT
                ('Flight # ', fid, ' on ', fdate, ": ", 
                ROUND(util*100), '%');
            INSERT INTO log (ByUser, Note, EventTime)
            VALUES (CURRENT_USER(), str, NOW());
        END LOOP l;
        CLOSE c;
    END //
    
    (0 row(s) affected)

    Let op: Terugkerende events die nieuwe gegevens naar een tabel schrijven die geen vastomlijnde eindtijd bevatten kunnen een mogelijk risico vormen, omdat ze de doeltabel aanzienlijk in grootte kunnen laten groeien zonder dat dit op een bepaald moment zal stoppen.

    Vermijd het gebruik van deze zoveel als mogelijk (het vorige voorbeeld is slechts illustratief en moet niet worden gebruikt in een productieomgeving), en als je het zou moeten doen, dient u zich ervan te vergewissen dat u altijd een eindtijd en eventueel nog extra beperkingen meegeeft die de acties van de event beperkt.

    U kunt het event ook zo configureren dat het zal optreden binnen een bepaalde tijdspanne door het opgeven van optionele STARTS en ENDS clausules, waarbij de begin- en eindtijden voor het event worden opgegeven. Hier is een herziening van het vorige voorbeeld, waarin de event zo is geconfigureerd dat het zal optreden tijdens een bepaalde maand:

    MSSQL MySQL
     
    Command(s) completed successfully.
    DELIMITER //
    CREATE EVENT util_hour
    ON SCHEDULE EVERY 1 HOUR
    STARTS '2009-04-01 00:00:01'
    ENDS '2009-04-30 23:59:01'
    ENABLE
    DO
    BEGIN
    DECLARE fid INT;
    DECLARE fdate DATE;
    DECLARE str TEXT DEFAULT '';
    DECLARE util FLOAT;
    DECLARE c CURSOR FOR
    SELECT s.FlightID, s.FlightDate, 1-(SUM(s.CurrSeats) /
        (SELECT SUM(fc.MaxSeats)
        FROM flightclass AS fc
        WHERE fc.FlightID = s.FlightID
        GROUP BY FlightID))
        AS u
    FROM stats AS s
    GROUP BY s.FlightID, s.FlightDate
    HAVING u > 0.80;
    OPEN c;
    l: LOOP
        FETCH c INTO fid,fdate,util;
        SET str = CONCAT
        ('Flight # ', fid, ' on ', fdate, ": ", 
        ROUND(util*100), '%');
        INSERT INTO log (ByUser, Note, EventTime)
        VALUES (CURRENT_USER(), str, NOW());
    END LOOP l;
    CLOSE c;
    END//
    
    (0 row(s) affected)

    One-Off Events

    Hoewel de event scheduler van MySQL zeer geschikt is voor het opzetten van terugkerende events, ondersteunt het ook events die slechts eenmaal, op een vooraf ingestelde tijd en datum moet optreden. Voor het instellen van een dergelijke event, dient men de EVERY clausule in de CREATE EVENT instructie te vervangen door een AT clausule die de datum en het tijdstip moet bevatten waarop het evenement moet optreden. Hier is een voorbeeld, waarin een evenement optreedt op 1 april 2009 om 1:25.

    MSSQL MySQL
     
    Command(s) completed successfully.
    CREATE EVENT log_onetime
    ON SCHEDULE AT '2009-04-01 01:25'
    ENABLE
    DO
    INSERT INTO log (ByUser, Note, EventTime)
    VALUES (CURRENT_USER(), 'Updating all accounts', NOW());
    (0 row(s) affected)

    Tip: Als u een event onmiddellijk wil laten optreden op het moment dat het is gemaakt, gebruikt u de functie NOW() in de AT clausule in plaats van een tijdstempel.