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
