14

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

  • 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 9: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 9:51
  • which tail are you using ? can you share the output of tail --version – amisax Nov 23 '17 at 9: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 9:54
  • my bash is GNU bash, version 4.3.30(1)-release (x86_64-pc-linux-gnu) and uname -aresults: 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 9:57
32

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 you 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.
  • 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 6:55

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.