Event Scheduler — avtomatik vazifalar

~15 daq · MySQL · View, Stored Procedure, Trigger

Muammodan boshlaylik

Muntazam bajariladigan vazifalar:

• Har kecha — kunlik statistikani hisoblash (34-dars)
• Har soat — 3 kundan beri to'lanmagan buyurtmalarni bekor qilish
• Har kuni — 90 kundan eski sessiya va loglarni o'chirish
• Har oy — yangi partition qo'shish (35-dars)

Odatda bu Linux cron orqali: skript yoziladi, cron uni ishga tushiradi, skript bazaga ulanadi. Lekin agar vazifa faqat SQL bo'lsa — buni MySQL ning o'zida ham qilish mumkin. Event Scheduler — bazaning ichki "cron" i.

Yoqish

SHOW VARIABLES LIKE 'event_scheduler';   -- ON (MySQL 8 da standart)

SET GLOBAL event_scheduler = ON;

Doimiy qilish — my.cnf da:

[mysqld]
event_scheduler = ON

Bir martalik event

CREATE EVENT aksiya_tugashi
ON SCHEDULE AT '2026-12-31 23:59:59'
DO
    UPDATE mahsulotlar SET narx = asl_narx WHERE aksiyada = 1;

Belgilangan vaqtda bir marta bajariladi va (standart holatda) o'zini o'chiradi.

CREATE EVENT eslatma
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    INSERT INTO xabarlar (matn) VALUES ('1 soat o''tdi');

Takrorlanuvchi event

CREATE EVENT tolanmagan_buyurtmalarni_bekor_qilish
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
    UPDATE buyurtmalar
    SET holat = 'bekor'
    WHERE holat = 'yangi' AND yaratilgan < NOW() - INTERVAL 3 DAY;

[!TIP] Og'ir vazifalarni tunda, kam yuklamali vaqtga qo'ying: STARTS '2026-11-05 03:00:00'. Vaqt — server vaqt mintaqasida (@@time_zone).

Ko'p qadamli event

DELIMITER //

CREATE EVENT kunlik_tozalash
ON SCHEDULE EVERY 1 DAY STARTS '2026-11-05 03:00:00'
DO
BEGIN
    -- 1. Kechagi statistika
    INSERT INTO kunlik_statistika (kun, buyurtmalar, tushum)
    SELECT DATE(b.yaratilgan), COUNT(DISTINCT b.id), COALESCE(SUM(q.soni * q.narx), 0)
    FROM buyurtmalar b JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.yaratilgan >= CURDATE() - INTERVAL 1 DAY AND b.yaratilgan < CURDATE()
      AND b.holat <> 'bekor'
    GROUP BY DATE(b.yaratilgan);

    -- 2. Eski loglarni bo'laklab o'chirish
    DELETE FROM kirish_loglari WHERE vaqt < NOW() - INTERVAL 90 DAY LIMIT 50000;

    -- 3. Bajarilganini qayd etish
    INSERT INTO event_jurnali (nom, vaqt) VALUES ('kunlik_tozalash', NOW());
END //

DELIMITER ;

Murakkab mantiq bo'lsa — event shunchaki procedure ni chaqiradi:

CREATE EVENT oylik_partition
ON SCHEDULE EVERY 1 MONTH STARTS '2026-12-01 00:10:00'
DO CALL partition_qosh_va_eskisini_ochir();

Boshqarish

SHOW EVENTS FROM dokon;
SHOW CREATE EVENT kunlik_tozalash\G

-- Vaqtincha to'xtatish va qayta yoqish
ALTER EVENT kunlik_tozalash DISABLE;
ALTER EVENT kunlik_tozalash ENABLE;

-- Jadvalni o'zgartirish
ALTER EVENT kunlik_tozalash ON SCHEDULE EVERY 1 DAY STARTS '2026-11-05 04:00:00';

DROP EVENT IF EXISTS kunlik_tozalash;

Oxirgi va keyingi bajarilish:

SELECT EVENT_NAME, STATUS, LAST_EXECUTED, INTERVAL_VALUE, INTERVAL_FIELD
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'dokon';

Bir martalik event ni bajarilgandan keyin saqlab qolish:

CREATE EVENT ... ON COMPLETION PRESERVE DO ...;

Xatolar va monitoring

Event xato bersa — u jimgina muvaffaqiyatsiz bo'ladi. Hech kim xabar olmaydi. Xato faqat MySQL error log da:

[ERROR] Event Scheduler: [root@localhost][dokon.kunlik_tozalash] Table 'dokon.kunlik_statistika' doesn't exist

Shuning uchun:

• Har bir event oxirida jurnalga yozing (yuqoridagi event_jurnali)
• Monitoring: "oxirgi 25 soatda kunlik_tozalash yozuvi yo'q" → ogohlantirish
• Xatoni ushlab jurnalga yozish uchun — DECLARE EXIT HANDLER (41-dars)

Event Scheduler yoki cron?

Amalda: ko'p loyihalar ilova scheduler idan foydalanadi (Laravel schedule:run, NestJS @Cron) — kod bir joyda, test va loglar odatiy. Event Scheduler — sof baza texnik xizmati uchun qulay: tozalash, partitionlar, statistika.

[!INFO] Replication da (53-dars) event lar replica larda avtomatik DISABLE holatida yaratiladi — vazifa ikki marta bajarilmasligi uchun. Replica source ga aylantirilsa, event larni qo'lda yoqish kerak.

Nima uchun shunday?

Nima uchun bazaning ichida rejalashtiruvchi bo'lishi kerak — operatsion tizimda cron bor-ku?

Cron bilan ishlash uchun: server ga kirish huquqi, skript yozish, unga baza parolini berish, skriptning o'zini kuzatish kerak. Bulutdagi boshqariladigan bazalarda (AWS RDS, Google Cloud SQL) esa operatsion tizimga umuman kirish yo'q. Event Scheduler — bazaning o'z ichida, o'z huquqlari bilan ishlaydigan, alohida infratuzilma talab qilmaydigan vosita. Ma'lumot bazada, vazifa ham bazada.

Xulosa

Eslab qoling:

• event_scheduler = ON — yoqilgan bo'lishi kerak
• CREATE EVENT nom ON SCHEDULE AT vaqt DO ... — bir martalik
• ON SCHEDULE EVERY 1 DAY STARTS '... 03:00:00' DO ... — takrorlanuvchi
• Ko'p qadam — BEGIN ... END; murakkab — CALL procedure()
• ALTER EVENT ... DISABLE / ENABLE, SHOW EVENTS
• Xatolar jim — jurnalga yozing va kuzating
• Faqat SQL vazifalar uchun; email, API — ilova scheduler i

Keyingi dars

45-dars: Cursor — satr-satr ishlash