LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices



Reply
 
Search this Thread
Old 05-19-2009, 09:10 AM   #1
Carlo1973
LQ Newbie
 
Registered: Mar 2007
Posts: 14

Rep: Reputation: 1
Smile BASH script for a MySQL cron job - Need help


Hi there. I'm trying to make a bash script to automatically purge old records from a MySQL database, to be run every day from the CRON directory cron.daily.

The script I have is as follows:

#!/bin/sh
SHELL=/bin/bash
PATH=usr/bin
MyHOST="localhost"
mysql -h$MyHOST -e "use pstsize; SELECT * FROM sizehist WHERE msgtime < CURRENT_DATE - INTERVAL 30 DAY && modtime < CURRENT_DATE - INTERVAL 30 DAY;" >> /var/log/sizehist_cleanup.log
exit 0


It seems to work okay for the SELECT * command. Viewing the log, exactly what I want is there. However since I'm a newbie to MySQL I figured I'd have it search and display the information prior to doing something relatively destructive.

The need is to have it find entries that where 2 date fields (msgtime and modtime) are greater than 30 days. If either of the date fields are less than 30 days it leaves the entries alone.

EG:

if msgtime & modtime > 30 days - delete entries
if msgtime < 30 days but modtime > 30 days leave alone (dont delete)
if msgtime > 30 days but modtime < 30 days leave alone (dont delete)
if msgtime < 30 days and modtime < 30 days leave alone (dont delete)


I tried changeing the SELECT * to DELETE * but got the following error:

ERROR 1064 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 '* FROM sizehist WHERE msgtime < CURRENT_DATE - INTERVAL 30 DAY


Is it possible that it's because I didnt' include a username/password? As far as I know a username/password isn't required for this specific database.

Anyhelp would be appreciated

Thanks in advance

Carlo

Last edited by Carlo1973; 05-19-2009 at 10:54 AM.
 
Old 05-20-2009, 02:56 AM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
There's no '*' in DELETE, as the msg is hinting

DELETE FROM table ....
 
  


Reply

Tags
bash, mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
cron job running PHP script to update MySQL - access denied error Beauford2008 Linux - Newbie 9 09-24-2008 08:17 PM
adding a perl script to cron.daily / cron.d to setup a cron job CrontabNewBIE Linux - Software 6 01-14-2008 09:16 AM
HELP - bash script - cron job - not out putting in 132 boyd98 Programming 12 05-01-2007 07:14 PM
How to set up cron job to execute bash script lgmqy2000 Linux - General 4 11-22-2006 05:29 AM
Bash script and cron job rust8y Linux - General 2 07-08-2006 08:45 AM


All times are GMT -5. The time now is 10:11 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration