15

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
  • 522,931
  • 91
  • 1,010
  • 1,501
FaxMax
  • 716
  • 1
  • 7
  • 27
  • You get the same output if you pipe through another command, right? For example `mysql ... | head` or `mysql ... | grep 8`? – terdon Nov 23 '17 at 09:46
  • Thanks for improving my English. `head` and `grep 802` do also double the **\\** `max@host 10:50:48: ~$ mysql -V mysql Ver 14.14 Distrib 5.5.55, for debian-linux-gnu (x86_64) using readline 6.3` – FaxMax Nov 23 '17 at 09:51
  • which tail are you using ? can you share the output of `tail --version` – amisax Nov 23 '17 at 09:53
  • @amisax my tail is `tail (GNU coreutils) 8.23` but i have the same problem with grep or head – FaxMax Nov 23 '17 at 09:54
  • my bash is `GNU bash, version 4.3.30(1)-release (x86_64-pc-linux-gnu)` and `uname -a`results: `Linux host 3.16.0-4-amd64 #1 SMP Debian 3.16.43-2+deb8u2 (2017-06-26) x86_64 GNU/Linux` – FaxMax Nov 23 '17 at 09:57
  • The process is likely using `tcgetattr()`, `isatty()`, etc; to detect where its output is going to do conditional output generation. You could always write an `LD_PRELOAD` rootkit library to overcome this. – Cloud Nov 23 '17 at 21:57

1 Answers1

37

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.
Stéphane Chazelas
  • 522,931
  • 91
  • 1,010
  • 1,501
  • 1
    the options `-r --column-names=0` solved my problem, tanks – FaxMax Nov 23 '17 at 10:07
  • 3
    This is the same reason that `ls` output is put into columns when the output goes to a terminal, but is a single column when writing to a pipe or file. – Barmar Nov 24 '17 at 06:55