1

I have a working command:

UPDATE ads SET ad_points = ad_points - 20

And I would like to perform this action every day.

After creating the event in phpMyAdmin (MYSQL) nothing happens (on the next day, etc.):

CREATE EVENT daily_pointsmin
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Subtracts twenty points from all the ads on every day.'
    DO
      UPDATE ads SET ad_points = ad_points - 20;

What can be the reason? The DB is in MYISAM, the tables are in InnoDB.

I've tried this already (including db in code):

UPDATE indikatr_main.ads SET ad_points = ad_points - 20
  • Is the event scheduler thread running on the server? – Dave Aug 22 '18 at 15:00
  • For this command: SET GLOBAL event_scheduler = ON; I have this result: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation But other events are working. Like this: BEGIN DECLARE x INT; DECLARE cname VARCHAR(255); SET x = DATEDIFF(NOW(),'2018-01-01') - 1; SET cname = CONCAT('d_',x); SET @s=CONCAT('ALTER TABLE calendar DROP `', cname); PREPARE stmt FROM @s; EXECUTE stmt; END – pnz Aug 22 '18 at 15:10
  • "The DB is in MYISAM, the tables are in InnoDB." -- Huh?? – Rick James Aug 30 '18 at 3:36
0

To enable or disable the execution of scheduled events, it is necessary to set the value of the global event_scheduler system variable. This requires the SYSTEM_VARIABLES_ADMIN or SUPER privilege. link

GRANT EVENT ON myschema.* TO jon@ghidora;

Or you may need to add super privilege using phpmyadmin link

Go to PHPMYADMIN > privileges > Edit User > Under Administrator tab Click SUPER > Go

1

(I'm not answering the Question as Asked, but rather suggesting how to make things better in the long run.)

Updating entire tables, especially on a regular basis, is a serious performance hit if the table is large. Also, it is a sign of a dubious schema design.

One thought... Don't blindly increment all the rows. Instead, compute the offset in every SELECT -- do some simple arithmetic based on the current date and the original date for the row. (Subtract, then multiply by 20.)

0

The problem was that, the server set OFF the "Event scheduler status", so I had to write cron jobs instead SQL events.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.