List:General Discussion« Previous MessageNext Message »
From:Machiel Richards Date:January 6 2011 11:31am
Subject:Re: Stored procedure
View as plain text  
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 <petya@stripped>
To: Machiel Richards <machielr@stripped>
Cc: mysql@stripped
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 <petya@stripped
> <mailto:petya%20%3cpetya@stripped%3e>>
> *To*: Machiel Richards <machielr@stripped
> <mailto:Machiel%20Richards%20%3cmachielr@stripped%3e>>,
> mysql@stripped <mailto:mysql@stripped>
> *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
>>
>


Thread
Stored procedureMachiel Richards5 Jan
  • Re: Stored procedurepetya5 Jan
    • Re: Stored procedureMachiel Richards5 Jan
      • Re: Stored procedurepetya5 Jan
        • Re: Stored procedureMachiel Richards6 Jan