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

esangrame

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

Bryan_H

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

esangrame

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

Jim_Knicely

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_Knicely

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)

esangrame

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