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

abbasauraabbasaura Community Edition User

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.

Tagged:

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    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?

  • abbasauraabbasaura Community Edition User

    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_KnicelyJim_Knicely - Select Field - Administrator
    edited June 2021

    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.

  • marcothesanemarcothesane - Select Field - Administrator

    @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.

  • marcothesanemarcothesane - Select Field - Administrator

    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_KnicelyJim_Knicely - Select Field - Administrator

    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
    
    
  • abbasauraabbasaura Community Edition User

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file