Duplicate Key Error During Migration from DOMjudge 7.3.3 to 9.x

[alessiojr]

Summary

After migrating from DOMjudge 7.3.3 to version 9.x, the system encountered duplicate key constraint violations when creating judging runs. This occurred because the migration left orphaned records (judgetaskid) in the database that conflicted with new judge tasks being created. The issue was resolved by adding an ON DUPLICATE KEY UPDATE clause to the INSERT statement. judging_run

Problem Description

Original Code (Before Fix)

$this->em->getConnection()->executeQuery(
    'INSERT INTO judging_run (judgingid, judgetaskid, testcaseid)
        SELECT :judgingid, judgetaskid, testcase_id FROM judgetask
        WHERE jobid = :judgingid ORDER BY judgetaskid',
    ['judgingid' => $judging->getJudgingid()]
);

Error Encountered

When migrating from DOMjudge 7.3.3 to 9.x, this code produced duplicate key violations on the unique constraint (judgingid, testcaseid) in the table. judging_run

Root Cause

The migration from version 7.3.3 to 9.x left existing records in the database that were associated with old or incomplete judgings. When the system attempted to create new judge tasks and corresponding judging runs for these judgings, it encountered conflicts because: judging_run

1. The table has a unique constraint on (judgingid, testcaseid) judging_run
2. Old judging runs from version 7.3.3 still existed in the database
3. New judge tasks were being created with different judgetaskid values
4. The simple INSERT statement failed due to the existing records

Database Schema Context

From : JudgingRun.php

#[ORM\UniqueConstraint(name: 'testcaseid', columns: ['judgingid', 'testcaseid'])]

The unique constraint ensures that each combination of (judgingid, testcaseid) can only exist once, which is correct behavior. However, during migration, some records persisted from the old version.

Solution Implemented

Fixed Code

// Step 3: Insert the corresponding judging runs.
$this->em->getConnection()->executeQuery(
    'INSERT INTO judging_run (judgingid, judgetaskid, testcaseid)
        SELECT :judgingid, judgetaskid, testcase_id FROM judgetask
        WHERE jobid = :judgingid ORDER BY judgetaskid
        ON DUPLICATE KEY UPDATE judgetaskid = VALUES(judgetaskid)',
    ['judgingid' => $judging->getJudgingid()]
);

How It Works

The ON DUPLICATE KEY UPDATE clause handles the conflict gracefully:

1. If the record doesn't exist: Insert it normally
2. If the record exists (duplicate judgingid + testcaseid): Update the existing record's judgetaskid to the new value from the SELECT statement
3. This allows the system to "repair" orphaned judging runs from the migration by associating them with the correct new judge tasks

Why This Solution Is Correct

• Preserves Data Integrity: The unique constraint remains in place
• Handles Migration State: Deals with leftover data from version 7.3.3
• Updates References: Ensures judging runs point to the current, valid judge tasks
• Idempotent: Can be run multiple times without errors
• Non-Destructive: Doesn't delete data, just updates the foreign key reference

Impact Before Fix

• System crashed when trying to judge submissions after migration
• Duplicate key constraint violations in database logs
• Submissions stuck in judging state
• Manual database cleanup was required to fix each affected judging

Impact After Fix

• System handles existing judging runs gracefully
• Migration from 7.3.3 to 9.x completes successfully
• Judge tasks are properly linked to judging runs
• No manual database intervention required

##Aditional
As an additional preventive measure that could be considered by the DOMjudge development team, a dedicated database migration could be created to proactively clean up orphaned and inconsistent judging data during upgrades from version 7.3.x to 9.x. This migration would identify and resolve several potential issues before they cause runtime errors: removing records that reference non-existent judgetask entries, deleting incomplete runs associated with invalid judgings, cleaning up orphaned judge tasks without corresponding judgings, and resolving duplicate (judgingid, testcaseid) combinations by keeping only the most recent record. While I have not implemented this solution in my installation (relying instead on the ON DUPLICATE KEY UPDATE approach which successfully handles conflicts at runtime), such a migration would provide "defense in depth" by addressing data inconsistencies at their source during the upgrade process, potentially eliminating the need for runtime conflict resolution altogether and providing administrators with detailed reports about the cleanup performed for audit and verification purposes. judging_run

Conclusion

The ON DUPLICATE KEY UPDATE solution successfully resolves the duplicate key constraint violation that occurred during migration from DOMjudge 7.3.3 to 9.x. This fix allows the system to handle pre-existing judging runs gracefully by updating their judge task references rather than failing on insert. The solution is minimal, non-destructive, and handles the migration state correctly without requiring manual database intervention.