HI All
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
times,etc... specified.
- 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.
Regards
Machiel
-----Original Message-----
From: petya <pe...@petya.org.hu>
To: Machiel Richards <machi...@rdc.co.za>
Cc: mysql@lists.mysql.com
Subject: Re: Stored procedure
Date: Wed, 05 Jan 2011 12:44:07 +0100
http://dev.mysql.com/doc/refman/5.1/en/events.html
On 01/05/2011 12:21 PM, Machiel Richards wrote:
> 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 <mailto:petya%20%3cpe...@petya.org.hu%3e>>
> *To*: Machiel Richards <machi...@rdc.co.za
> <mailto:machiel%20richards%20%3cmachi...@rdc.co.za%3e>>,
> mysql@lists.mysql.com <mailto: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
>>
>
