0

I am trying to create an Event in mysql, in the Event I want to execute LOAD DATA query in every 30 min.
My Query:

DELIMITER $$
CREATE
EVENT auto_mytable 
ON SCHEDULE EVERY 30 MINUTE
DO BEGIN

TRUNCATE TABLE mytable;

LOAD DATA local INFILE '/file.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES;

END;
$$

I am getting the error LOAD DATA not allowed in stored procedures Is there any other solution to achieve the same thing? Thanks

ansh
  • 573
  • 3
  • 9
  • 26

1 Answers1

0

You might want to investigate using mysqlimportvia cron or Windows' Task Scheduler.

cron is a Unix daemon (system service) - shipped with all major server Linux/*BSD distributions. On Windows, you get the Windows Task Scheduler service.

(If you want some suggestions on how to configure cron, let me know in the comments below.)

Assuming you're on *nix, I'd wrap your import process in a shell script. Make every effort to sanity check that the data you want to import is available (is it readable, etc) and throw meaningful errors appropriately (e.g. via stderr vs stdout, and non-zero exit codes as appropriate).

Documentation on the mysqlimport is available online.

wally
  • 3,492
  • 25
  • 31