I've turned mysql general log on and I've been logging every transaction that occurs for the last week. I also have a cronjob running every 5 minutes doing some mysql transactions which is polluting my log. I would like to know how I can remove those entries from my log so it would look more clean.

The repetitive entries from cron look like this:

160614 16:45:01    36 Connect   user@localhost on 
                   36 Init DB   db1
                   36 Query     SELECT FROM status_history as sh INNER JOIN (SELECT work_order_i
d, MAX(status_changed) AS MaxDateTime FROM status_history GROUP BY work_order_id) as groupedsh ON sh.work_order_id = groupedsh.work_o

                            JOIN rchetype_work_order_views AS wov ON wo.id = wov.work_order_id
                                WHERE wov.viewed = 0 AND sh.status_id IN (SELECT status_id FROM rchetype_status_completed)
                   36 Quit

I was thinking in using sed but the problem is that he first line will obviouslly have a different date and different transaction number (in this case 36) and I would like to delete all lines until it finds the first Quit at the end. So I was wondering if there's a way to do this. I'm just looking for ideas.

  • Any specific string which is constant on first line I.e date line ?
    – Rahul
    Jun 24 '16 at 19:33
  • @Rahul yes, Connect user@localhost on
    – VaTo
    Jun 24 '16 at 20:28

Assuming that they are always the same amount of lines, you could do something like this:
sed '/Connect\s*user@localhost on/,+7d' log.file This will remove the line containing Connect user@localhost on and the following 7 lines from the file "log.file" in your current directory.

Edit: final solution (well, at least good enough for the OP to alter to his liking) can be found in the comments.

  • Thank you for your answer Kaffe, I don't know if there are always the same amount of lines, but in any case, isn't possible to tell sed to remove until it finds the next Quit word?
    – VaTo
    Jun 24 '16 at 22:13
  • Something like this? sed '/Connect *user@localhost on/,/36 Quit/{/(user|Quit)/!d}' tmp.file Jun 24 '16 at 22:21
  • Of course, assuming it's always "36 Quit". You can replace that with maybe just "Quit" or this: sed '/Connect\s*user@localhost on/,/[0-9][0-9] Quit/{/(user|Quit)/!d}' tmp.file Jun 24 '16 at 22:23
  • Thank you, that's very close to what I'm looking for! however it doesn't work in some of the logs I have so I'm trying to modify your regex, could you explain what /{/(user|Quit)/!d} does? So I can modify it? thanks. And by the way it's not always '36 Quit' I did [0-9]* Quit since I don't know how many digits the transaction number will have.
    – VaTo
    Jun 24 '16 at 23:06
  • The first part tells sed the range and the second in the curly brackets are applied to the range. If nothing is in there, like {//!d}, you'll get the lines where the range starts and ends. Now, I have to admit, I didn't do that right, even if it yielded the right result for me. Replace that with this sed '/Connect\s*user@localhost on/,/[0-9]* Quit/{/^.*$/d}' tmp.file, which tells it do delete everything that starts and ends with anything within the range, in other words -- delete's the unwanted data. Jun 24 '16 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.