SQLException[Vertica][VJDBC](4856) ERROR: Syntax error at or near "10"

abbasaura

Hi,
Hope you are doing well.
I am new on vertical. While scheduling summarization jobs I am getting the error " SQLException[Vertica]VJDBC ERROR: Syntax error at or near "10" in JBoss.

marcothesane

You have an SQL statement that contains the literal 10 somewhere where it does not belong according to SQL syntax. Without knowing the SQL statement in question, knowing SQL, and finding out why the SQL statement became what it now is, you're stuck - and so are all of us. Can you share the SQL statement with us?

abbasaura

Hi, marcothesane,
According to our observation, It is not a syntax error. It is a logical error. Need your help in finding a solution.
Best Regards

Jim_Knicely

Maybe check the DC_ERRORS table? It contains a column called CURSOR_POSITION that can point you to where the syntax error is occurring.

https://www.vertica.com/blog/vertica-quick-tip-viewing-query-error-information/

Also, try running the SQL in vsql.

marcothesane

@Jim_Knicely also starts from the assumption of a syntax error. Only there do you get a CURSOR_POSITION. And SQLException only occurs when there is a SQL statement.

It might be a logical error - in the JDBC client - JBoss it will be, even if I don't know that one, but it appears in the log snipped you shared. And the logical error in JBoss(?) has the undesired effect of generating syntactically wrong SQL code.

But, for Vertica, it's a SQL Syntax error. Try to unveil the SQL code you generate. In the worst of cases, query query_requests, the system table, for queries launched near or at the timestamp when you encountered the above error, and look (and share with us) the request column's content of that query.

marcothesane

That is - find out the timestamp - and then:

SELECT 
  request 
FROM query_requests 
WHERE start_timestamp BETWEEN TIMESTAMP '2021-06-07 14:45:00' AND TIMESTAMP. '2021-06-07 15:00:00' 
  AND NOT success;

, for example. Maybe you can use a narrower time slice than 15 minutes to find the culprit

Jim_Knicely

Maybe the query won't show up in QUERY_REQUESTS?

Example:

dbadmin=> SELECT 1 10;
ERROR 4856:  Syntax error at or near "10" at character 10
LINE 1: SELECT 1 10;
                 ^
dbadmin=> SELECT time, session_id FROM dc_errors WHERE log_message ILIKE 'Syntax error at or near "10"' AND session_id = current_session();
             time              |           session_id
-------------------------------+--------------------------------
 2021-06-07 09:10:02.225278-04 | v_test_db_node0001-5781:0xedff
(1 row)

dbadmin=> SELECT user_name, request FROM query_requests WHERE session_id = 'v_test_db_node0001-5781:0xedff' ORDER BY start_timestamp DESC;
 user_name |                                                              request
-----------+-----------------------------------------------------------------------------------------------------------------------------------
 dbadmin   | SELECT user_name, request FROM query_requests WHERE session_id = 'v_test_db_node0001-5781:0xedff' ORDER BY start_timestamp DESC;
 dbadmin   | SELECT time, session_id FROM dc_errors WHERE log_message ILIKE 'Syntax error at or near "10"' AND session_id = current_session();
(2 rows)

I was able to view the SQL in the Vertica log:

2021-06-07 09:10:02.225 Init Session:0x7f53d8ffa700 [Session] <INFO> [Query] TX:0(v_test_db_node0001-5781:0xedff) SELECT 1 10;
2021-06-07 09:10:02.225 Init Session:0x7f53d8ffa700 <ERROR> @v_test_db_node0001: 42601/4856: Syntax error at or near "10" at character 10
        LOCATION:  base_yyerror, /data/qb_workspaces/jenkins2/PrimaryBuilds/build_master/build/vertica/Parser/scan.l:1043

abbasaura

Hi,

Thank you for your response.

Actually, we were testing on our lab and summarization jobs were working fine. Then we have uploaded all the batch files from production and removed the old ones after getting back up. One summarization job "FASocActiveAlarmsCountJob" has different SQL files for production and lab environments. which is producing "syntax error near 10".

Problematic SQL:

FAS_OCcountersExtractAndCalculate.sql

EXECUTE (10) select
'INSERT INTO TMP_OC_ACTIVE_ALARMS_COUNTERS (TIME, OPERATIONCONTEXTID, ALARMTYPEID, SEVERITYID, PROBABLECAUSEID, SPECIFICPROBLEMSID, MANAGEDOBJECTNAME, AOACKNOWLEDGED, AOOUTSTANDING, AOHANDLED, AONOTHANDLED) (WITH SNAPSHOTTIME AS (select TO_TIMESTAMP('''
|| TIME_SLICE(lastmodificationtimestamp,10,'MINUTE') ||
''',''YYYY-MM-DD HH24:MI:SS'') AS SELECTEDTIME)
SELECT SNAPSHOTTIME.SELECTEDTIME "TIME", OPERATIONCONTEXTID, ALARMTYPEID, SEVERITYID, PROBABLECAUSEID, SPECIFICPROBLEMSID, MANAGEDOBJECTNAME,
sum(case when ACKFLAG = 1 and ACKTIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AOACKNOWLEDGED,
sum(case when (SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (ACKFLAG = 0 or (ACKFLAG = 1 AND ACKTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AOOUTSTANDING,
sum(case when HANDLEFLAG = 1 and HANDLETIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (CLOSEFLAG = 0 or(CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AOHANDLED,
sum(case when (SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (HANDLEFLAG = 0 or(HANDLEFLAG = 1 AND HANDLETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (CLOSEFLAG = 0 or (CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) then 1 else 0 end) AONOTHANDLED
FROM FCT_FAULT, SNAPSHOTTIME WHERE ALARMCLASS = 0 and
(
(ACKFLAG = 1 and ACKTIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME))) or
((SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (ACKFLAG = 0 or (ACKFLAG = 1 AND ACKTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (TERMFLAG = 0 or (TERMFLAG = 1 AND TERMTIMESTAMP > SNAPSHOTTIME.SELECTEDTIME))) or
(HANDLEFLAG = 1 and HANDLETIMESTAMP < SNAPSHOTTIME.SELECTEDTIME AND (CLOSEFLAG = 0 or(CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME))) or
((SNAPSHOTTIME.SELECTEDTIME > CREATIONTIMESTAMP) and (HANDLEFLAG = 0 or(HANDLEFLAG = 1 AND HANDLETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)) and (CLOSEFLAG = 0 or (CLOSEFLAG = 1 AND CLOSETIMESTAMP > SNAPSHOTTIME.SELECTEDTIME)))
)
GROUP BY SNAPSHOTTIME.SELECTEDTIME, OPERATIONCONTEXTID, ALARMTYPEID, SEVERITYID, PROBABLECAUSEID, SPECIFICPROBLEMSID, MANAGEDOBJECTNAME
)
' from fct_fault WHERE ${data['CDCFILTER']} group by TIME_SLICE(lastmodificationtimestamp,10,'MINUTE')

Need help in solving the issue.

Best Regards