3

The below command is not giving any result. I want to grep the error line from the file and insert into table.

Command which is not working:

tail -f logfile.log | grep  ERROR|while read msg; do psql -d testdb -c insert into t values('$msg'); done

But if I remove the grep ERROR code from the command, it is working as expected. I am not sure what's going on?

Command which is working fine:

tail -f logfile.log|while read msg; do psql -d testdb -c insert into t values('$msg'); done

You can assume the below data in file:

ERROR
sql committed
ERROR
ERROR
error
...
6

Two things:

  1. The code, as it is written in the question, would insert the literal string $msg (if it works at all) since you use single quotes around it. Use double quotes instead. Here, I've put the whole statement in double quotes which will expand the $msg within. The shell code is still frail depending on what comes from the grep. The string in $msg should ideally be properly sanitized so that a single ', or other special characters, won't break the statement (or worse, see comment from user cas below).

    tail -f logfile.log |
    grep -F 'ERROR' |
    while read msg; do
        psql -d testdb -c "insert into t values('$msg')"
    done
    

    I've also added -F to the grep invocation as we're searching with a fixed string (this is mostly for documentation purposes).

  2. grep buffers its output, so it won't produce anything until its output buffer is full. This gives the impression of it "not working" when in fact it does, but it won't do anything until grep has flushed its output buffer which it does once enough data is in it. This is a performance optimization.

    GNU grep (as well as some other implementations of the same utility, on e.g. OpenBSD) can be made to be line buffered with its --line-buffered option:

    tail -f logfile.log |
    grep --line-buffered -F 'ERROR' |
    while read msg; do
        psql -d testdb -c "insert into t values('$msg')"
    done
    

Note: I have not tested this since I currently do not have a PostgreSQL (?) instance running.

  • 1
    --line-buffered was added to GNU grep for that very use case, before BSDs rewrote/forked their grep from GNU grep (which they used to use) – Stéphane Chazelas Jan 25 '18 at 9:28
  • 2
    3rd thing: It is possible for the contents of the ERROR message to break the SQL insert command (e.g. if it has a single-quote in it), or even pose an SQL injection threat (e.g. ERROR something something '); drop table t; --). I very strongly recommend using perl or python or any other language that has a DB interface library that supports placeholders for variables in SQL statements. – cas Jan 25 '18 at 11:45
  • 2
    A perl script using the File::Tail and DBI + DBD::Pg modules would be perfect for a job like this. – cas Jan 25 '18 at 11:46

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.