Enterprise Manager log reporting "ORA-02291: integrity constraint (APMDB.APPMAP_TRANSACTIONS_FK3) violated - parent key not found" errors after running configimport.

Products

• APP PERF MANAGEMENT
• CA Application Performance Management Agent (APM / Wily / Introscope)
• CUSTOMER EXPERIENCE MANAGER
• INTROSCOPE

Issue/Introduction

After importing an exported APM database configuration into a new schema using the configimport script the following errors are observed in the Enterprise Manger log file:

1/11/16 10:01:40.083 AM CET [ERROR] [Thread-ModelSynchronization] [Manager.AppMap] Unable to complete model synchronization with storage! 
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO appmap_edges(source_id, target_id, transaction_id, start_time, end_time) VALUES (?, ?, ?, ?, ?)]; ORA-02291: integrity constraint (APMDB.APPMAP_TRANSACTIONS_FK3) violated - parent key not found ; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (APMDB.APPMAP_TRANSACTIONS_FK3) violated - parent key not found

Cause

 The configimport script does not insert the required rows in tables APPMAP_ID_MAPPINGS and APPMAP_VERTICES when initializing the tables.

Environment

This problem occurs when using APM 10.1-10.5 configimport script against an Oracle APM Database on either Windows or Linux.

Resolution

1.    Using SQLPlus or similar tool logon to the Oracle system as the schema owner

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

2. Verify that both tables are empty using the following SQL

SQL> SELECT * FROM APPMAP_ID_MAPPINGS;

no rows selected

SQL> SELECT * FROM APPMAP_VERTICES;

no rows selected.

 

Note: These tables will be empty right after upgrade. However, they could have data already after EM is running.

A better way would be checking if any row with vertex_id = 0 exists:

SQL> SELECT * FROM APPMAP_ID_MAPPINGS WHERE vertex_id = 0;

SQL> SELECT * FROM APPMAP_VERTICES WHERE vertex_id = 0;

 

3. Create the missing rows using the following SQL and commit the change (See the note below for performing in APM 10.2 and later.)

SQL> INSERT INTO appmap_id_mappings (vertex_id, external_id, type)

SELECT 0, 'CA_APM_INTERNAL', 'V' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM appmap_id_mappings WHERE vertex_id = 0) ;

1 row created.

SQL> INSERT INTO appmap_vertices (vertex_id, start_time, end_time, business_service)

SELECT 0, TIMESTAMP '1979-1-1 0:0:0', TIMESTAMP '1979-1-1 0:0:0', 'CA_APM_INTERNAL' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM appmap_vertices WHERE vertex_id = 0);  

1 row created.

SQL> COMMIT;

Commit complete.

The error messages will now stop.

 

Note:  

In APM 10.2 and later, for #3 above, substitute instead the following SQL. (To support the addition of the 'fork' vertex which has been available since APM 10.2.) :

INSERT INTO appmap_vertices (vertex_id, start_time, end_time, fork, business_service) 
SELECT 0, TIMESTAMP '1979-1-1 0:0:0', TIMESTAMP '1979-1-1 0:0:0', 0,'CA_APM_INTERNAL' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM appmap_vertices WHERE vertex_id = 0); 

Additional Information

 This issue is fixed in APM 10.5.1.