How to echo errors to an out file using vsql?

Moderator: NorbertKrupa

Post Reply
User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

How to echo errors to an out file using vsql?

Post by usli06 » Wed Sep 18, 2013 7:48 pm

Hello,

I need to echo errors to an out file when running a vsql script. I cannot get errors to echo out.

Example:

Here is my script:

Code: Select all

[dbadmin@Vertica1 ~]$ cat test.sql
\o /home/dbadmin/test.log
create user test;
\o
The first time I run it, out out file contains the create user text as expected:

Code: Select all

[dbadmin@Vertica1 ~]$ vsql -f test.sql
[dbadmin@Vertica1 ~]$ cat /home/dbadmin/test.log
CREATE USER
But when I run it the second time, the error is not in the log file:

Code: Select all

[dbadmin@Vertica1 ~]$ vsql -f test.sql
vsql:test.sql:2: ROLLBACK 5403:  User/role "test" already exists
[dbadmin@Vertica1 ~]$ cat /home/dbadmin/test.log
I don't want the error to go to the screen, I want it in the log file.

Anyone know how to do this?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to echo errors to an out file using vsql?

Post by JimKnicely » Wed Sep 18, 2013 7:54 pm

Try:

nohup vsql -f test.sql > /home/dbadmin/test.log
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: How to echo errors to an out file using vsql?

Post by id10t » Wed Sep 18, 2013 8:21 pm

Hi!

Try:

Code: Select all

vsql -f script.sql 2> errorl.logs
PS
IMO: nohup a wrong tool.
nohup - run a command immune to hangups, with output to a non-tty

User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Re: How to echo errors to an out file using vsql?

Post by usli06 » Thu Sep 19, 2013 6:41 am

Thanks, guys!

But if I some additional items to the vsql script neither method gives the expected results.

Code: Select all

[dbadmin@Vertica1 ~]$ cat test.sql
\o /home/dbadmin/test.log
select current_database(), now();
\timing
create user test;
\o
knicely87's way:

Code: Select all

[dbadmin@Vertica1 ~]$ cat test.log
Timing is on.
vsql:test.sql:4: ROLLBACK 5403:  User/role "test" already exists
----------------------
 VMart            | 2013-09-18 22:38:27.911799-07
(1 row)
sKwa's way:

Code: Select all

[dbadmin@Vertica1 ~]$ vsql -f test.sql 2> test.log
Timing is on.
[dbadmin@Vertica1 ~]$ cat test.log
vsql:test.sql:4: ROLLBACK 5403:  User/role "test" already exists
----+-------------------------------
 VMart            | 2013-09-18 22:35:33.621687-07
(1 row)
Hmm. Shouldn't the result of the SELECT statement appear before the CREATE TABLE error?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to echo errors to an out file using vsql?

Post by JimKnicely » Thu Sep 19, 2013 7:09 am

Good point, sKwa! How about this one?

vsql -f test.sql &>> test.log
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: How to echo errors to an out file using vsql?

Post by id10t » Thu Sep 19, 2013 11:28 am

Hi!

>> How about this one?
Also may be a solution, depends on needs.
1. Your variant isn't equal to my: your solution appends output while my always rewrite a file or creates a new one.
2. Also its a new syntax of Bash 4 for redirecting all streams (STDERR and STDOUT) to a single output, while usli asked for STDERR only.

NOTE: "&>outfile" is a Bash4 code and not portable. The way to go portable better is ">outfile 2>&1"

>> Shouldn't the result of the SELECT statement appear before the CREATE TABLE error?
No. STDERR and STDOUT are independent and parallel streams, while monitor/file/output device is a single device.
It's OS decides when to flush STDERR/STDOUT buffers and have similarity to locks in DB (because you have only single device for writing or only one table to write in it).

User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Re: How to echo errors to an out file using vsql?

Post by usli06 » Thu Sep 19, 2013 3:02 pm

Thanks, guys. Great explanation sKwa. I guess I wasn't specific enough. I just wanted a log file that shows every command in the script as well as the output (including both errors and normal success out put from those commands). But you guys gave me what I needed. Thanks!!!

Post Reply

Return to “vSQL”