Stored procedure

--=-Afl1SOZRHahuvDaaQznv
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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 --databases
> /backups/DB_backup.dump

mysql -u root -p -D -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

--=-Afl1SOZRHahuvDaaQznv--

Re: Stored procedure

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 --databases
>> /backups/DB_backup.dump
>
> mysql -u root -p -D -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
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Stored procedure

--=-YgZHbLlXPU7r3mbk5LU2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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
To: Machiel Richards , 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 --databases
>> /backups/DB_backup.dump
>
> mysql -u root -p -D -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
>

--=-YgZHbLlXPU7r3mbk5LU2--

Re: Stored procedure

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 >
> *To*: Machiel Richards > >,
> 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 --databases
>>> /backups/DB_backup.dump
>>
>> mysql -u root -p -D -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
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Stored procedure

--=-Y4mXy+HK4nsHHn+7r6Oa
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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
()
- 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 () 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
To: Machiel Richards
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 >
> *To*: Machiel Richards > >,
> 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 --databases
>>> /backups/DB_backup.dump
>>
>> mysql -u root -p -D -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
>>
>


--=-Y4mXy+HK4nsHHn+7r6Oa--