Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Identify Queries failed with syntax errors and it's failure reason.

Where could I see the queries and it's respective error which failed with syntax error.
error_messages and dc_errors doesn't have the queries.
Query_requests and dc_requests_issued doesn't stores the queries failed with syntax errors.

Could anyone please help me with this.

Regards

Comments

  • Bryan_HBryan_H Employee

    Do you need to find this from a SQL query? It's in vertica.log:
    2019-06-19 14:06:48.920 Init Session:0x7f03da40b700 [Session] [Query] TX:0(v_docker_node0001-17861:0x9d98d) seledt * from foo;
    2019-06-19 14:06:48.920 Init Session:0x7f03da40b700 @v_docker_node0001: 42601/4856: Syntax error at or near "seledt" at character 1

  • Thanks for responding.
    I don't have access to check the vertica logs.
    So dependent on system logs

  • Jim_KnicelyJim_Knicely Administrator
    edited June 2019

    The ERROR_MESSAGES system table has the error:

    dbadmin=> SELECTd FROM foo;
    ERROR 4856:  Syntax error at or near "SELECTd" at character 1
    LINE 1: SELECTd FROM foo;
            ^
    dbadmin=> SELECT event_timestamp, node_name, error_level, error_code, message FROM error_messages ORDER BY event_timestamp DESC LIMIT 1;
            event_timestamp        |     node_name      | error_level | error_code |              message
    -------------------------------+--------------------+-------------+------------+-----------------------------------
     2019-06-19 15:50:28.922284-04 | v_test_db_node0001 | ERROR       |   16801924 | Syntax error at or near "SELECTd"
    (1 row)
    

    Queries that fail becuase of a syntax error are not stored in any system table.

    Maybe create an external table that reads the vertica.log info?

    Simple example:

    dbadmin=> SELECTd FROM foo;
    ERROR 4856:  Syntax error at or near "SELECTd" at character 1
    LINE 1: SELECTd FROM foo;
            ^
    dbadmin=> SELECT event_timestamp, node_name, error_level, error_code, message FROM error_messages ORDER BY event_timestamp DESC LIMIT 1;
            event_timestamp        |     node_name      | error_level | error_code |              message
    -------------------------------+--------------------+-------------+------------+-----------------------------------
     2019-06-19 16:13:18.535508-04 | v_test_db_node0001 | ERROR       |   16801924 | Syntax error at or near "SELECTd"
    (1 row)
    
    dbadmin=> CREATE EXTERNAL TABLE vertica_log (log_entry VARCHAR(1000)) AS COPY FROM '/home/dbadmin/test_db/v_test_db_node0001_catalog/vertica.log';
    CREATE TABLE
    
    dbadmin=> SELECT * FROM vertica_log WHERE log_entry ILIKE '2019-06-19 16:13:18%';
                                                                       log_entry
    -----------------------------------------------------------------------------------------------------------------------------------------------
     2019-06-19 16:13:18.535 Init Session:0x7f50a2bfd700 [Session] <INFO> [Query] TX:0(v_test_db_node0001-419314:0x1c38) SELECTd FROM foo;
     2019-06-19 16:13:18.535 Init Session:0x7f50a2bfd700 <ERROR> @v_test_db_node0001: 42601/4856: Syntax error at or near "SELECTd" at character 1
    (2 rows)
    
  • Jim_KnicelyJim_Knicely Administrator

    Note, if queries fail becuase an error that is not caused by a syntax error, you can do the following to see the error and query:

    dbadmin=> CREATE TABLE test_table (c INT);
    CREATE TABLE
    
    dbadmin=> CREATE TABLE test_table (c INT);
    ROLLBACK 4213:  Object "test_table" already exists
    
    dbadmin=> SELECT qr.node_name, qr.start_timestamp,
    dbadmin->        LEFT(REGEXP_REPLACE(qr.request, '[\r\t\f\n]', ' '), 100) AS request,
    dbadmin->        em.error_level, em.error_code, em.message
    dbadmin->   FROM v_monitor.query_requests qr
    dbadmin->   JOIN v_monitor.error_messages em
    dbadmin->     ON em.node_name = qr.node_name
    dbadmin->    AND em.session_id = qr.session_id
    dbadmin->    AND em.request_id = qr.request_id
    dbadmin->    AND em.transaction_id = qr.transaction_id
    dbadmin->  ORDER BY qr.start_timestamp DESC
    dbadmin->  LIMIT 1;
         node_name      |        start_timestamp        |             request              | error_level | error_code |              message
    --------------------+-------------------------------+----------------------------------+-------------+------------+------------------------------------
     v_test_db_node0001 | 2019-06-19 16:35:30.381791-04 | CREATE TABLE test_table (c INT); | ERROR       |     290948 | Object "test_table" already exists
    (1 row)
    
  • Thank you everyone for your suggestions.. I was looking for options if we could get query and it's syntax errors recorded in system tables..and could generate a report later at the end of the day..

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.