3

I am running a MySQL script file from a shell script in Linux (CentOS 7). While I can capture the result in a file, I am not able to capture result metadata.

Example:

My test.sql file looks like this:

USE dbname;
SELECT * FROM `test`;
INSERT INTO test values (3,'Test');

My test.sh script looks like this:

#!/bin/bash
    
mysql --password=<pwd> --user=<username> --host=<host domain> < test.sql > out.txt

When I execute test.sh from the command line, I can capture the output in out.txt. But MySQL also generates metadata like no of rows affected for commands like INSERT. I am unable to capture that for the last SQL command (please refer to my SQL file example above).

2
  • 1
    Just a guess: If you use your command interactively (from the command line) like this: mysql --password=<pwd> --user=<username> --host=<host domain> < test.sql > /dev/null does the metadata info still appear on the terminal? It might be going to e.g. stderr . – FelixJN Jun 23 at 7:05
  • Thanks @FelixJN . I just tried as you specified (directly from command line). I am unable to see metadata. – Kannan Jun 23 at 7:11
2

You can increase verbosity. This should be enough:

-vv

Reason:

It check for isatty and if it find it is not printing to a terminal enters batch mode. The verbosity from man and --help:

--verbose 
 -v       Verbose mode. Produce more output about what the program
          does. This option can be given multiple times to produce
          more and more output. (For example, -v -v -v produces 
          table output format even in batch mode.)

--batch
-B        Print results using tab as the column separator, with each
          row on a new line. With this option, mysql does not use 
          the history file.

          Batch mode results in nontabular output format and escaping
          of special characters. Escaping may be disabled by using 
          raw mode; see the description for the --raw option.

Depending on what you want you might also want --raw.


Chasing the rabbit

Else one would have to fake tty, e.g. by using script:

0<&- script -qefc "mysql -u user --password='xxx' --host=host"< test.sql >out.txt

that would capture everything - but then again someone might want that.


Excluding input

For programs using library isatty(), and do not have a override flag for this, one can fake tty this way as well; (Compile a minimal C snippet):

echo 'int isatty(int fd) { return 1; }' | \
gcc -O2 -fpic -shared -ldl -o faketty.so -xc -

strip faketty.so # not needed, but ...

chmod 400 faketty.so # not needed, but ...

Then run by:

LD_PRELOAD=./faketty.so mysql -u user --password='xxx' --host=host< test.sql >out.txt

or add a shell wrapper for for example faketty:

#! /bin/sh -
LD_PRELOAD=/path/to/faketty.so "$@"

Then

$ faketty mysql ... < foo >bar
1
  • I tried the verbosity solution. Works perfectly. Thanks. – Kannan Jun 23 at 7:34

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.