HI
How do I use the mysql event scheduler?
I have not used this as yet so not sure how to use it.
Regards
Machiel
-----Original Message-----
From: petya <pe...@petya.org.hu>
To: Machiel Richards <machi...@rdc.co.za>, mysql@lists.mysql.com
Subject: Re: Stored procedure
Date: Wed, 05 Jan 2011 12:15:59 +0100
Hi,
Use the mysql event scheduler instead of cron, the bash script is quite
pointless, and call your stored procedure with now() - interval 1 day
parameter.
Peter
On 01/05/2011 11:00 AM, Machiel Richards wrote:
> Good day all
>
> I am hoping that someone can assist me here.
>
> As per a client requirement, I am writing a
> script/stored procedure combination in order to do the following:
>
> - Script to be run within a cron once a day
> according to a set schedule.
> - script to connect to mysql and call a stored
> procedure
> - stored to procedure to do the following:
>
> * retrieve row id of the record
> that indicates the last record of a specified date (i.e 00:00 yesterday)
> [select max(id) into
> max_id from table1 where utc< dt]
>
> * delete records from table2 where
> id< max_id
> * delete records from table1
> where id< max_id
>
> After a struggle to get the script and stored
> procedure working I am now stuck at the following point.
>
> the date that needs to be specified to the
> stored procedure must be in the following format:
>
> 2011-01-04 00:00
> (i.e. yesterday 00:00) meaning that everything before this date and time
> needs to be deleted.
>
> However when trying to run the script with
> the date like this, then I get the following message:
>
>
> ERROR 1064 (42000) at line 1: You
> have an error in your SQL syntax; check the manual that corresponds to
> your MySQL server version for the right syntax to use near '00:00)' at
> line 1
>
>
> I initially had the script create the
> date in a different manner but then the 00:00 was seen as a seperate
> argument which didn't work. After some changes the date is now being
> read correctly from what I can tell but now I get the message above.
>
>
> Herewith my script and stored procedure definitions:
>
>
>
> Script:
>
> #!/bin/bash
>
> DATE="`date --date="1 days ago" +%Y-%m-%d` 00:00"
> echo "$DATE"
>
> mysqldump -u root -p<password> --databases<DB>
>> /backups/DB_backup.dump
>
> mysql -u root -p<password> -D<DB> -e "call select_delete_id_2($DATE)"
>
> exit
>
>
>
>
> Stored Proc:
>
>
> begin declare max_id int(11); select max(id) into max_id from table1
> where utc< dt; delete from table2 where id< max_id; delete from table1
> where id< max_id; end
>
>
> Does anybody perhaps have any suggestions?
>
> Regards
> Machiel
>
