TransWikia.com

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

Unix & Linux Asked by FaxMax on November 23, 2021

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 ).

One 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.

Answered by Stéphane Chazelas on November 23, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP