Database health check and self-healing Available from codebeamer 22.04 (Felicity).

This feature automatically examines the state of the database schema and in some cases executes additional self-healing operations to reach expected consistency with a reference state. This replaces the index integrity checker available in version before codebeamer 22.04 (Felicity).

Triggering health-check

Health check will run periodically 5 minutes after midnight every day. It can be manually triggered by a System Administrator on the System Admin ► Database performance job page, by clicking the Recheck Database Health link. The check runs in the background and completes in about 1 minute.

If problems were found, after refreshing the page, the system notifies the user: "Suboptimal database health detected. Please contact support to help resolving the issue."

For further information about the steps to be taken, see: How to fix database health check issues.

What does health checker validate?

The validated list of database objects are the following:

• database tables (including column datatypes, constraints)
• indices (with column list and ordering)
• sequences (with starting values and current values)

The health checker also collects any additional elements which were not recognized and could negatively affect the application's performance.

When does the self-healer run and what does it affect?

Self-healer will automatically run in one of the following cases:

• a database upgrade occurred, which executed patch files and altered the database version,
• if current database was migrated from an another dialect.

Self-healing does not affect data, since health checks only examine the database structure. The generated statements only do schema alternations which are safe to run.

If self-healing cannot be completed due to an error, it will not try to fix the database again until an another upgrade. In such a case, the user has to manually intervene. codebeamer will still start up and become accessible, however optimal performance couldn't be guaranteed. It is recommended to contact codebeamer support for further instructions (see: How to fix database health check issues).

Examine output of health checker

The resutls of health checking is recoreded in the application log.

If the database was healthy, the following two lines are in the log:

2022-01-24 10:44:42,416 INFO  upgradeLogger - Checking database health... <thread info>
2022-01-24 10:44:45,223 INFO  upgradeLogger - Database is healthy. <thread info>

In case of a problem, a longer log entry is recorded in the log:

2022-05-23 09:19:59,500 WARN  rdbms.health.DatabaseHealthCheckJob - Database health check validation result:

<report of database objects with differences>

Database self healing dry-run script output:

<generated dry run SQL script>

Save this log enty, as it is essential for codebeamer Support to help fixing the database.

How to fix database health check issues

In case the self-healer couldn't fix the database there is no additional automated way to fix the database state. Manual intervention is required. Please contact codebeamer support and attach the output of the health checker.

If this output is not found, run a health-check manually (Triggering health-check). Then extract the output of the health-check report including the dry run SQL script (Examine output of health checker) and send it in a support request.

Common self-healer issues

MySQL foreign key constraint fails

This is a data integrity error caused by some operation, which was run in the past with MySQL foreign key checks temporarily disabled. These can leave records in the database which have reference values pointing to other records, not existing anymore. When the self-healer tries to (re)apply a foreign key constraint definition to the database, these faulty rows will block the operation.

1) Extract dry-run SQL script output

As a first step find the health-checker output inside the application log (Examine output of health checker), and paste SQL script into a new file.

Proceed with either 1a) or 1b) steps.

1a) Fix by removing offending records

Manually try and fix these, by removing orphan records from the database, if foreign key was defined using DELETE CASCADE. This can be done by examining the foreign key constraint definition and rewriting it into the necessary DELETE statement.

For a foreign key constraint, its definition is in the cb.jar file (which can be opened as a .zip file) in the installed codebeamer instance. There's a <dialect>-database.sql file where the corresponding ALTER TABLE statement can be found.

For example for the activity_log_proj_fk and activity_log_user_fk foreign key constraints:

ALTER TABLE project_activity_log ADD CONSTRAINT activity_log_proj_fk FOREIGN KEY (proj_id) REFERENCES existing(proj_id) ON DELETE CASCADE;
ALTER TABLE project_activity_log ADD CONSTRAINT activity_log_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

can be cleaned up using the following DELETE statements for orphan records:

DELETE FROM project_activity_log WHERE proj_id NOT IN (SELECT proj_id FROM existing);
DELETE FROM project_activity_log WHERE user_id NOT IN (SELECT id FROM users);

1b) Fix by temporarily disabling foreign key checks

This is not recommended, and only do it if 1a) is not a feasible solution.

The self-healer script can ignore foreign key checks by inserting the SET FOREIGN_KEY_CHECKS=0; before the script and the SET FOREIGN_KEY_CHECKS=1; line after the script.

Then proceed to step 2).

2) Running self-healer script manually

Open a database console to the target database with the JDBC user of codebeamer. Execute the self-healer script extracted (and opitionally modified in step 1b).

Once the operation completes, run health checker manually (Triggering health-check); the error badge should not be present.