Thank you for the responses.
I have been going through the documentation the whole of today
thus far and it seems to be easy enough.
I am still however confused on how to achieve the following
though , and this might be due to a lack of experience or I might just
not be thinking straight...
- From what I can tell the scheduled event is
created and contains the "body" of what needs to be run at the
- The command I need to run though will be
somthing like this:
--> call <procedure>
(<yesterday's date at 00:00:00>)
- The purpose of the procedure is to delete all
records from specific tables older than (<) the specified date.
The procedure is already working and if I run it manually
entering the date it works 100%.
However, I need to schedule an event to run each day @ 02h00 for
instance which will then call the procedure as per above.
My problem (which I had with the bash script as well) is to
get the full correct date (<yesterday's date at 00:00:00>) passed to the
"call procedure()" statement.
Can anybody give me some ideas as I have tried so many options
and yet none of them has worked as yet.
From: petya <petya@stripped>
To: Machiel Richards <machielr@stripped>
Subject: Re: Stored procedure
Date: Wed, 05 Jan 2011 12:44:07 +0100
On 01/05/2011 12:21 PM, Machiel Richards wrote:
> How do I use the mysql event scheduler?
> I have not used this as yet so not sure how to use it.
> -----Original Message-----
> *From*: petya <petya@stripped
> *To*: Machiel Richards <machielr@stripped
> mysql@stripped <mailto:mysql@stripped>
> *Subject*: Re: Stored procedure
> *Date*: Wed, 05 Jan 2011 12:15:59 +0100
> Use the mysql event scheduler instead of cron, the bash script is quite
> pointless, and call your stored procedure with now() - interval 1 day
> 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
>> - 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:
>> DATE="`date --date="1 days ago" +%Y-%m-%d` 00:00"
>> mysqldump -u root -p<password> --databases<DB>
>> mysql -u root -p<password> -D<DB> -e"call
>> 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
>> where id< max_id; end
>> Does anybody perhaps have any suggestions?