Options
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
0
Comments
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
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)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)