cancel
Showing results for 
Search instead for 
Did you mean: 

Error in PEM worker on PEM server

SOLVED
EDB Team Member

Re: Error in PEM worker on PEM server

Hi Polem,

 

Could you please attach the worker.log for our reference in order to investigate further on this.

Level 3 Adventurer

Re: Error in PEM worker on PEM server


@kapil wrote:

Hi Polem,

 

Could you please attach the worker.log for our reference in order to investigate further on this.


Due to limitation of 75000 chars I upload only an extract:

Wed Nov 21 15:36:23 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:36:33 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:36:43 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:36:53 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:03 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:13 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:23 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:33 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:43 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:54 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement
EDB Team Member

Re: Error in PEM worker on PEM server

Hi Polem,

 

Could you please check if their is any further corruption, if yes could you please check and elminate the corruption, once you confirm that corruption is no longer exit could you please reindex the tables "alert" and "alert_status" and verify the woker logs for the error exist or not.

Level 3 Adventurer

Re: Error in PEM worker on PEM server

Hi,
Concerning corruption I have dumped PEM database successfully, for reindexing the 2 tables tomorrow because I'm not in front of the system and I don't have remote access.
Kindly
Paul-Emmanuel
EDB Team Member

Re: Error in PEM worker on PEM server

Hi Polem,

 

Sure, let's have reindex for these two tables and share logs with us. 

Level 3 Adventurer

Re: Error in PEM worker on PEM server


@Ranjan wrote:

Hi Polem,

 

Sure, let's have reindex for these two tables and share logs with us. 


Hi,

I get this error when I try to REINDEX:

pem=# reindex table pem.alert;
ERROR:  failed to find parent tuple for heap-only tuple at (50,14) in table "alert"
pem=# reindex table pem.alert_status;
ERROR:  failed to find parent tuple for heap-only tuple at (33,3) in table "alert_status"
pem=#

Kindly

Paul-Emmanuel

EDB Team Member

Re: Error in PEM worker on PEM server

Hi Polem,

 

Their is a corruption in the table, You can look at the specific row involved here with i.e, 

 select * from alert where ctid = '(50,14)'; 

 select * from alert_status where ctid = '(33,13)';
 

Probably the best recovery strategy is to DELETE that row by ctid,then reinsert the same data. Lather, rinse, repeat until you can reindex successfully. Better check your other tables too.

Level 3 Adventurer

Re: Error in PEM worker on PEM server

Hi,

There is the output:

pem=# select * from pem.alert where ctid = '(50,14)';
-[ RECORD 1 ]---------------------+------------------------------
id                                | 580
name                              | Server Down
enabled                           | t
template_id                       | 233
agent_id                          | 0
server_id                         | 35
database_name                     |
schema_name                       |
package_name                      |
object_name                       |
params                            | {}
operator                          | >
thresholds                        | {0.1,0.2,0.3}
check_frequency                   | 1
history_retention                 | 30
error_message                     |
acknowledged                      | f
email_group_id                    |
send_email                        | f
last_mail_send                    |
flapping_detected                 | f
last_flapping_detection_processed | 2018-10-05 10:50:32.118661+02
send_trap                         | f
snmp_trap_version                 | 2
error_timestamp                   |
low_send_trap                     | f
low_email_group_id                |
med_send_trap                     | f
med_email_group_id                |
high_send_trap                    | f
high_email_group_id               |
execute_script                    | f
execute_script_on_clear           | f
execute_script_on_pem_server      | f
script_code                       |
submit_to_nagios                  | f
auto_created                      | t
 
pem=# select * from pem.alert_status where ctid = '(33,13)';
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alert_id            | 2444
current_value       | 1
current_state       |
current_state_since |
last_processed      | 2018-10-29 14:10:34.691279+01
state_change_count  | 0
info                | Query = SELECT 1                                                                                                                                                                                                                                                       +
                    | Is Idle? = t                                                                                                                                                                                                                                                           +
                    | Username = enterprisedb                                                                                                                                                                                                                                                +
                    | Is vacuum? = f                                                                                                                                                                                                                                                         +
                    | Process ID = 18720                                                                                                                                                                                                                                                     +
                    | Wait event =                                                                                                                                                                                                                                                           +
                    | Is Waiting? = f                                                                                                                                                                                                                                                        +
                    | Database name = postgres                                                                                                                                                                                                                                               +
                    | Is autovacuum? = f                                                                                                                                                                                                                                                     +
                    | Wait event type =                                                                                                                                                                                                                                                      +
                    | Query start time = 2018-10-29 14:09:07.800886+01                                                                                                                                                                                                                       +
                    | State change time = 2018-10-29 14:09:07.801004+01                                                                                                                                                                                                                      +
                    | Process start time = 2018-10-29 13:54:45.221114+01                                                                                                                                                                                                                     +
                    | State change since = 00:01:26.890275                                                                                                                                                                                                                                   +
                    | Transaction start time =                                                                                                                                                                                                                                               +
                    | Is idle in transaction? = f                                                                                                                                                                                                                                            +
                    |                                                                                                                                                                                                                                                                        +
                    |                                                                                                                                                                                                                                                                        +
                    |
info_cols           | {Query,"Is Idle?",Username,"Is vacuum?","Process ID","Wait event","Is Waiting?","Database name","Is autovacuum?","Wait event type","Query start time","State change time","Process start time","State change since","Transaction start time","Is idle in transaction?"}
info_vals           | {{"SELECT 1",t,enterprisedb,f,18720,"",f,postgres,f,"","2018-10-29 14:09:07.800886+01","2018-10-29 14:09:07.801004+01","2018-10-29 13:54:45.221114+01",00:01:26.890275,"",f}}
display_value       |
 
pem=#
EDB Team Member

Re: Error in PEM worker on PEM server

Hi Polem,

Probably the best recovery strategy is to DELETE that row by ctid,then reinsert the same data. Lather, rinse, repeat until you can reindex successfully. Better check your other tables too.

Level 3 Adventurer

Re: Error in PEM worker on PEM server

Hi,

I delete manually a lot of corrupted records. Can I truncate those 2 tables and start with a fresh status of alerting in PEM?

 

Kindly,

Paul-Emmanuel