scheduled tasks - How to schedule a MySQL query?

Question

Can I schedule a task to run at a specified interval in MySQL?

I have an inventory MySQL database. The structure is as follows:

table_1            fields: itmcode, avgcost
table_2            fields: itmcode(FK to table_1(itmcode)), quantity

The basis of the report is when I want the inventory valuation details item wise for a past date.

The avgcost and quantity fields is changed when a new purchase is posted into system. I can run a query to see the current stock valuation, but I want to be able to see the stock valuation at a previous date as well. How do I do that? For quantity, I can add the sales and deduct the purchases backwards from the current date until whatever date the report requires, but the avgcost is current since this gets updated each time a purchase is posted.

I was wondering if an automatic daily dump could be executed, similar to this:

SELECT itmcode, quantity, avgcost, (avgcost * quantity) as ttlval
FROM table_1 
JOIN table_2 ON table_1.itmcode = table_2.itmcode

Is this task possible to schedule directly in MySQL or is there some other solution?

Answer 1

you have 2 basic options (at least):

1, Take a look at Event Scheduler

First create table eg. stock_dumps with fields

itemcode, quantity, avgcost, ttlval,dump_date (DATETIME)

CREATE EVENT `Dumping_event` ON SCHEDULE
        EVERY 1 DAY
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
INSERT INTO stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date)
SELECT itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, NOW()
  FROM table_1 JOIN table_2 ON table_1.itmcode = table_2.itmcode;
END

Please follow instructions how to enable scheduler on link posted above. Note : Old versions of mysql don't have event scheduler

2, Create cron job/windows scheduled job:

create sql file:

INSERT INTO stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date)
SELECT itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, NOW()
FROM table_1 JOIN table_2 ON table_1.itmcode = table_2.itmcode;

schedule this command:

mysql -uusername -ppassword < /path/to/sql_file.sql