Conditional bash/mysql query help

Shell Programming and Scripting
1

I think(hope) I've got a simple one - I just need to send an email if a mysql query returns any results (ideally - it will never match).

Currently I just pipe the mysql query output to the mail program, but of course that emails regardless of the output( and I check this every 10 minutes from cron, so that's a lot of mail when it's empty).

I only want to send mail (the actual mysql output) if there is anything returned from the query.

Here is my query - ideally it will never find any messages that meet those conditions, but if it does, I need an email sent with the results of the query.

mysql -e 'select new_messages,FROM_UNIXTIME(last_purge_at)
  AS 'last_purge_at',comment
  FROM zimbra.mailbox
  WHERE new_messages > 0
  AND last_purge_at < DATE_SUB(NOW(),INTERVAL 10 MINUTE)' \
  | mail -s "Zimbra email overdue" user@email.com

Anyone got any suggestions? Thanks in advance

2

Show an example of output when it does and when it doesn't find results.

3

You might be better to catch the output into a file and then decide if you need to send it. Your code may well return a No rows found type message and email it every run whether there is anything interesting or not. It might even cause you problems if you try to send nothing.

  • What output do you get from the mysql command?
  • What is the condition to send or not?

Regards,
Robin

4

Here is an example when it finds something:

+--------------+---------------------+-----------------------+
| new_messages | last_purge_at | comment |
+--------------+---------------------+-----------------------+
| 3 | 2015-03-02 11:41:55 | email@something.com |
+--------------+---------------------+-----------------------+

When it returns nothing, I get no kind of results - just a new prompt.

5

You might be having a problem with the quoting too. Your command has a single quoted input to the mysql but then you single quote within that. You might need to use double quotes for the mysql command or escape all the single quotes.

If you are getting output from your command as it is, then catch it to a file instead of trying to pipe it to the mail command. You can then use the shell test to see if the file contains anything:-

mysql -e ............ whatever you already have > /tmp/new_msgs
if [ -s /tmp/new_msgs ]
then
   mail -s "An e-mail subject" user@email.com < /tmp/new_msgs
fi

Does this help?

Robin

1 Like
6

Thanks Robin, this looks like it would work perfectly (and thanks for mentioning the dual single-quotes).

I will test this out and let you know.

---------- Post updated at 03:28 PM ---------- Previous update was at 10:16 AM ----------

FYI, this solution works perfectly. Thanks again for all your help.