I am using vsql.exe on an external Vertica database for which I don't have any administrative access. I use some views with simple SELECT+FROM+WHERE
queries.
These queries 90% of the time work just fine, but some times, randomly, I get this error:
ERROR 3326: Execution time exceeded run time cap of 00:00:45
The strange thing is that this error can happen way after those 45 seconds, even after 3 minutes. I've been told this is related to having different resource pools, but anyway I don't want to dig into that.
The problem is that when this occurs, vsql.exe
returns errorlevel 0
and there is (apparently almost) no way to know this failed.
The output of the query is stored in a csv file. When it succeeds, it ends with (#### rows)
. But when it fails with this error, it just stops at any point of the csv, and its resulting size is around half of what's expected. This is of course not what you would expect when an error occurs, like no output or an empty one.
If there is a connection error or if the query has syntax errors, the errorlevel is not 0, so in those cases it behaves as expected.
I've tried many things, like increasing the timeout or adding -v ON_ERROR_STOP=ON
to the vsql.exe parameters, but none of that helped.
I've googled a lot and found many people having this error, but the solutions are mostly related to increasing the timeouts, not related to the errorlevel returned.
Any help will be greatly appreciated.
TL;DR: how can I detect an error 3326 in a batch file like this?
@echo off
vsql.exe -h <hostname> -U <user> -w <pwd> -o output.cs -Ac "SELECT ....;"
echo %errorlevel% is always 0
if errorlevel 1 echo Error!! But this is never displayed.
Now that's really unexpected to me. I don't have Windows available just now, but trying on my Mac - at first just triggering a deliberate error:
With
ON_ERROR_STOP
set toON
, this should be the behaviour everywhere.Could you try what I did above through Windows, just with
echo %ERRORLEVEL%
instead ofecho $?
, just from the Windows command prompt and not in a batch file?Next test: I run on resource pool
general
in my little test database, so I temporarily modify it to a runtime cap of 30 sec, run a silly query that will take over 30 seconds withON_ERROR_STOP
set toON
, collect the value returned byvsql
and set the runtime cap ofgeneral
back toNONE
. I also have the%VSQL_* %
env variables set so I don't have to repeat them all the time:Now for the test (backslashes, in Linux/MacOs escape a new line, which enables you to "word wrap" a shell command. Use the caret (
^
) in Windows for that):So it works in my case. Your line:
if errorlevel 1 echo Error!! But this is never displayed.
... never echoes anything because the previous line, with
echo
will return 0 to the shell, overriding the previouserrorlevel
.Try it command by command on your Windows command prompt, and see what happens. Just
echo %errorlevel%
, without evaluating it.And I notice that you are trying to export to CSV format. Then, try this:
-A
)-F ','
)-P footer
)(I show the output before redirecting to file):
Not aligning is much faster than aligning. Then, as you spend most time in the fetching of the rows (that's because you get a timeout in the middle of an output file write process), try fetching more rows at a time than the default 1000. You will need to play with the value, depending on the network settings at your site until you get your best value:
-v ROWS_AT_A_TIME=10000
Once you're happy with the tested output, try this command (change the
SELECT
for your needs, of course ....):The table actually contains one million rows. Note the line count in the file: 1,000,001. That's the title line included, but the footer
(1000000 rows)
removed.