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.
 |
This is an expert step and will irreversibly alter data in the database. Create a backup before proceeding! If unsure about the consequences of these steps, contact Codebeamer support for help!
|
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;
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);
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.