Why does piping `mysql` to 'tail' change the output format?

Question

When I view the result of a SELECT with MySQL Workbench, it's correct with one single

max@host 10:13:58: ~$ mysql -h db-master.domain.local -uuser -ppw db -e '
>                 SELECT
>                 DISTINCT i.filesourceregexp
>                 FROM db.ImportLogFiles i'

+------------------------------------------------+
| filesourceregexp                               |
+------------------------------------------------+
| ^[0-9]{8}_1062355673_merge_google_pbn.csv$    |
| ^[0-9]{8}_8026062435_merge_google_pbn.csv$    |
| ^[0-9]{8}_1062355673_store_visits_report.csv$ |
+------------------------------------------------+

max@host 10:14:10: ~$ mysql -h db-master.domain.local -uuser -ppw db -e '
                SELECT
                DISTINCT i.filesourceregexp
                FROM db.ImportLogFiles i' | tail -n +2
^[0-9]{8}_1062355673_merge_google_pbn\.csv$
^[0-9]{8}_8026062435_merge_google_pbn\.csv$
^[0-9]{8}_1062355673_store_visits_report\.csv$
max@host 10:14:19: ~$

I have these options in my.cnf:

[client] 
host = db-master 
user = user 
password = pass 
default-character-set=utf8

Why does piping the result through tail change the output/string? (note the double ).

Stéphane Chazelas · Answer

It's not tail, it's the piping.
mysql uses a tabular with ASCII boxing output format when its stdout is a terminal device, when it's intended for a user, and reverts to a scripting format when it's not, like when it's a pipe or a regular file.
You'd see the same different format with
mysql... | cat

or
mysql > file; cat file

See also the -r/--raw, -s/--silent, -B/--batch, -N/--skip-column-names/--column-names=0, -H/--html, -t/--table... that affect the output format.
If you want the tabular output even when the output doesn't go to a terminal device, add the -t option:
mysql -t ... | tail -n +2

But if the point is to remove the header line, just use -N, with or without -t.
Here to get the values from the database as raw as possible and without header, I'd use:
mysql --defaults-extra-file=/some/protected/file/with/credentials 
      --batch --raw --skip-column-names -e 'select...' database

That is:

not expose the password in the output of ps by passing the credentials in a file instead (like your my.cnf) with --defaults-extra-file.
use the batch mode to avoid the tabular output (and acknowledge the fact that we are actually batching it which may have other implications).
--raw to avoid the escaping. Assuming the values don't contain newlines as otherwise the output could not be post-processed reliably.
--skip-column-names to remove the header line.

Why does piping `mysql` to 'tail' change the output format?

One Answer

Add your own answers!

Ask a Question