How to notify SQL_ERROR to Windows Event log.

[iti-attunity-sup]

Hello Experts,

I have some questions regarding notifications for task event.

The customer creates some notifications for task event when errors or warnings occur.

They encountered the SQL_ERROR below the other day, but no notification messages were recorded in the Windows Event log.

00015984: 2024-06-12T11:06:01 [TARGET_APPLY ]I: Error in bulk, bulk state: bulk confirmed record id - '1', bulk last record id - '1060705', confirmed record id - '1060585', sorter confirmed record id - '1060585' (bulk_apply.c:2476)
00015984: 2024-06-12T11:06:01 [TARGET_APPLY ]I: Failed (retcode -1) to execute statement: INSERT INTO ...
00015984: 2024-06-12T11:06:01 [TARGET_APPLY ]I: RetCode: SQL_ERROR SqlState: XX000 NativeError: 30 Message: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR: Cannot insert a NULL value into ...
DETAIL: 
-----------------------------------------------
error: Cannot insert a NULL value into column xxx
code: 8007
context: query execution
query: 701014253
location: column:40
process: query0_230_701014253 [pid=22460]
-----------------------------------------------
[1022502] (ar_odbc_stmt.c:5046)

Though the above SQL Errors were occurring repeatedly at that time, the customer could not notice it immediately, then latency increased, eventually reaching 7200 seconds.

Questions:

1. My understanding is that the above message was marked as Info(I:) in the task log, so it was not notified as an error or a warning. Is it correct?

2. How can we get a notification when such SQL_ERROR occurs? (How should I create a notification?)

If this kind of issue should be asked as a support case, could you let me know?

Regards,
Kyoko Tajima

[sureshkumar]

Hello @iti-attunity-sup 

Please make sure "Windows Event Log" option is selected while configuring the Notification settings.

Regards,

Suresh

[iti-attunity-sup]

Hello Suresh

Thank you for your update.

According to the diagnostics package (task_export.json),

"delivery_methods" is "EVENT_VIEWER", so I think it's OK.

"name": "AnyError",
"trigger_type": "GENERAL_ERROR",
"on_condition": "1",
"audit_on_message": "[{{SERVER_NAME}}\\{{NOTIFICATION_NAME}}] {{TASK_NAME}} replication task encountered the following error:\n{{ERROR_TEXT}}",
"trigger_timeout": 0,
"delivery_methods": [{
 "type": "EVENT_VIEWER",
 "on_message": "[{{SERVER_NAME}}\\{{NOTIFICATION_NAME}}] {{TASK_NAME}}:replication task encountered the following error:\n{{ERROR_TEXT}}.",
 "on_subject": ""
 }],

Regards,

Kyoko Tajima

[sureshkumar]

Hello @iti-attunity-sup 
If the task gets into error/warning the notifications will be trigged based on the settings you've configured not during INFO mode.

Hope it helps.

Regards,

Suresh

[aarun_arasu]

Hello @iti-attunity-sup ,

Yes, you were right. I believe since it was an INFO message, it would have failed to trigger a notification.

I recommend opening a support ticket so our internal team can validate the log and understand why the error message was written as an INFO message.

Regards,
Arun

[john_wang]

Hello @iti-attunity-sup ,

Not sure what's the Replicate version you are running now, however you may try below option:

1. Open Redshift target endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4. Add a new parameter named $info.query_syntax.error_code_data_failure
   
5. Press <Enter> and set the parameter's value to:
   
   8007
   where 8007 is the error code in the error message. You may add more error codes there like "8007, 8008" etc.
   
   Please take note I did not try it in my labs and in general it works for other specific endpoints. Hope it works for you, any feedback is welcome.

Regards,

John.

[iti-attunity-sup]

@john_wang 

Hello John

Thank you for your reply and suggesting a workaround.

The customer is using v2023.05 and internal parameter $info.query_syntax.error_code_data_failure is available.

May I confirm your suggested workaround?

1. From the description of your update, when we use this parameter, does it mean that we need to set the error code that appears in the following message individually?

In the customer's case, 8007

-----------------------------------------------
error: Cannot insert a NULL value into column xxx
code: 8007 <-----
context: query execution
query: 701014253
location: column:40
process: query0_230_701014253 [pid=22460]
-----------------------------------------------

I wonder the customer cannot be sure in advance what other error code may occur besides this error.
Are there any settings that would be enabled for all error codes?

2. If the error code specified in $info.query_syntax.error_code_data_failure occurs,
my understanding that some warning or error occurs and then notify it to the event log.
In this case, the table causes warnings or errors will be suspended. Is my understanding correct?

We are trying to reproduce this error in house but have not been able to do for now, so I would like to confirm it.

Regards,
Kyoko Tajima

[john_wang]

Hello Tajima-san,

Thank you so much for the information.

No need to care about all other error message codes, I'm just trying to find a quick workaround for this issue. I'm not sure if this internal parameter work or not as it's introduced for other endpoints, I do not see it in the default Redshift syntax. If it works then we may work with R&D to get an official solution, the test result would speed up the progress. If it does not work then the test result helps us to set the case proper priority.

Regards,

John.

[iti-attunity-sup]

Hello John

Thank you for your update.

I understood.

Currently I can not reproduce the same error with my simple testcase as I mentioned in my previous update.
My testcase causes 'A NOT NULL constraint violation' instead of 'code 8007'...
So I could not confirm the effectiveness of the parameter for now.

I've asked the customer to provide their table/index/constraint definition and sample data.
I will update if I can confirm it.

Regards,
Kyoko Tajima

[john_wang]

Hello Tajima-san, @iti-attunity-sup 

Thank you so much for your quick update. No worries and take your time, we are here always for help.

Good luck,

John.