Bad data left over from previous bug stops Flyway from completing which prevents TC from starting up

[camerojo]

The problem is that the candidate_visa_job_check table has multiple entries for the same candidate opportunity: as defined by candidate id and job opp id.

You can see this by running the following sql:

select c.id,cvjc.job_opp_id,count(*) as cnt from candidate_visa_job_check cvjc join salesforce_job_opp jo on cvjc.job_opp_id = jo.id join candidate_visa_check cvc on cvjc.candidate_visa_check_id = cvc.id join candidate c on cvc.candidate_id = c.id group by c.id, cvjc.job_opp_id order by cnt desc;

This means that there is ambiguity in moving the dependants_id field across from candidate_visa_job_check to candidate_opportunity. So the Flyway attempting to do that fails.

[camerojo]

This situation happens because there are duplicate jobs. See this screen shot (from staging).

The current code checks for duplicate jobs - so this bad data seems to relate to a bug that has since been fixed. But the bad data is still there in both staging and prod. So the solution is probably just to manually remove the bad data.

By running the above query I can see that the bad data only relates to 7 candidates in prod and 4 candidates in staging.

[camerojo]

• [x] Fix bad data on staging
• [x] Fix bad data on prod