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.dumpmysql -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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
