Staff

Troubleshooting Tip: How to resolve Database and AppSvr errors related to corrupted index for ph_incident_detail table

Forum|Forum|1 year ago
December 16, 2024
0 replies
1756 views

Description

This article describes an approach to troubleshooting and resolving repetitive errors observed related to pthe h_incident_detail table in AppSvr and database logs.

Scope

FortiSIEM.

Solution

The Symptoms for the occurrence of the issue would be No new Incidents being generated in FortiSIEM

The following errors observed in:'/cmdb/data/pg_log/postgresql.log':

< 2024-12-10 04:31:32.661 GMT >STATEMENT: insert into ph_incident_detail (collector_id, creation_time, cust_org_id, last_modified_time, owner_id, duration, incident_detail, incident_id, incident_timestamp, notif_recipients, schedule_interval_time, trigger_events, id) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)

< 2024-12-10 04:31:33.681 GMT >ERROR: index "ph_incident_detail_y2024m12_pkey" contains unexpected zero page at block 1774

< 2024-12-10 04:31:33.681 GMT >HINT: Please REINDEX it.

< 2024-12-10 04:31:33.681 GMT >CONTEXT: SQL statement "INSERT INTO ph_incident_detail_y2024m12 VALUES ($1.*)"

PL/pgSQL function ph_incident_detail_partition_tables() line 67 at EXECUTE

The following errors observed in AppSvr logs: '/opt/glassfish/domains/domain1/logs/server.log':

[2024-12-10T10:05:03.191+0530] [glassfish 5.1] [ERROR] [] [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] [tid: _ThreadID=1461 _ThreadName=pool-15-thread-317] [timeMillis: 1733805303191] [levelValue: 1000] [[

ERROR: index "ph_incident_detail_y2024m12_pkey" contains unexpected zero page at block 1774

Hint: Please REINDEX it.

Where: SQL statement "INSERT INTO ph_incident_detail_y2024m12 VALUES ($1.*)"

PL/pgSQL function ph_incident_detail_partition_tables() line 67 at EXECUTE]]

[2024-12-10T10:05:03.191+0530] [glassfish 5.1] [ERROR] [] [org.hibernate.internal.ExceptionMapperStandardImpl] [tid: _ThreadID=1461 _ThreadName=pool-15-thread-317] [timeMillis: 1733805303191] [levelValue: 1000] [[

HHH000346: Error during managed flush [org.hibernate.exception.GenericJDBCException: could not execute batch]]]

[2024-12-10T10:05:03.191+0530] [glassfish 5.1] [WARNING] [enterprise_distributedtx.before_completion_excep] [javax.enterprise.resource.jta.com.sun.enterprise.transaction] [tid: _ThreadID=1461 _ThreadName=pool-15-thread-317] [timeMillis: 1733805303191] [levelValue: 900] [[

DTX5014: Caught exception in beforeCompletion() callback:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute batch

..

Caused by: org.hibernate.exception.GenericJDBCException: could not execute batch

..

Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into ph_incident_detail (collector_id, creation_time, cust_org_id, last_modified_time, owner_id, duration, incident_detail, incident_id, incident_timestamp, notif_recipients, schedule_interval_time, trigger_events, id) values (NULL, 1733805303117, 2009, 1733805303117, 0, 300000, 'procName:ls, swParam:-1 /proc/4025422/fd, ', 147622284, 1733805300000, NULL, NULL, '<triggerEventLists><triggerEvents subpatName="Filter">3029092962293901684</triggerEvents><triggerEventTypes subpatName="Filter">LINUX_PROCESS_EXEC</triggerEventTypes></triggerEventLists>', 767771992) was aborted: ERROR: index "ph_incident_detail_y2024m12_pkey" contains unexpected zero page at block 1774

Hint: Please REINDEX it.

Where: SQL statement "INSERT INTO ph_incident_detail_y2024m12 VALUES ($1.*)"

PL/pgSQL function ph_incident_detail_partition_tables() line 67 at EXECUTE Call getNextException to see other errors in the batch.

The following steps describe an approach to resolve the errors at the Database level.

Take a snapshot of Supervisor's instance.
Login to Database from Supervisor CLI.

psql -U phoenix phoenixdb

Try Re-indexing the affected index first.

REINDEX INDEX ph_incident_detail_y2024m12_pkey;

If there are no further errors, the command will run successfully.
If there are errors after running the above command, such as below.

ERROR: duplicate key value violates unique constraint "ph_incident_detail_y2024m12_pkey"

Try Re-indexing the affected table: Here, for example, ph_incident_detail_y2024m12.

REINDEX TABLE ph_incident_detail_y2024m12;

If there are no further errors, the command will run successfully.
If any errors exist, check the D of incidents that are duplicates of the errors.
Try checking into the ph_incident_detail table if there are any duplicates

select * from ph_incident_detail where id=<ID_Observed_From_errors>;

Note down the ID of the Incident and check if it can be corrected before removing it.
If Duplicate entries cannot be corrected, remove duplicate entries using the below command.

delete from ph_incident_detail where id='<Duplicate_Incident_ID>';

Try Re-indexing the table again.

REINDEX TABLE ph_incident_detail_y2024m12;

Try correcting Duplicate entries if more errors are observed while Re-indexing the table.
If the command gets executed successfully, check from AppSvr logs again.

tail -f /opt/glassfish/domains/domain1/logs/server.log

Check again by Logging in to GUI and see if Incidents start triggering again.

Fortisiem

Sign up

Login with SSO

Login to the community

Login with SSO