Welcome to the most active Linux Forum on the web.
Go Back > Forums > Linux Forums > Linux - Newbie
User Name
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!


  Search this Thread
Old 05-19-2009, 09:10 AM   #1
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:

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.


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


Last edited by Carlo1973; 05-19-2009 at 10:54 AM.
Old 05-20-2009, 02:56 AM   #2
LQ Guru
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.10, Centos 7.5
Posts: 17,697

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

DELETE FROM table ....


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 > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 05:27 AM.

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