EMS 7.4.4 Upgrade Failed Due to fk_software_software_icons Constraint — Fixed by Manual DB Repair

[limbaev]

Hello everyone,

I ran into a tricky issue upgrading FortiClient EMS from 7.4.0 → 7.4.4. The installation failed with the following PostgreSQL / Sqitch error:

ERROR: insert or update on table "software" violates foreign key constraint "fk_software_software_icons" DETAIL: Key (icon_id, vdom_id)=(5800, 1) is not present in table "software_icons".

Even though the upgrade script tried to rollback, it left the DB in an inconsistent state. The installer log showed it reverted to version 7400 and aborted.

Here’s how I resolved it:

---

:white_heavy_check_mark: Steps I took

1. Connect to the correct EMS database
   The problem tables were in fcm_default, not fcm.

   sudo -u postgres psql fcm_default

2. Check schema of software and software_icons
   I confirmed software had icon_id and no vdom_id, and software_icons had id.

3. Clear orphaned icon_id references
   I ran:

   UPDATE software SET icon_id = NULL WHERE icon_id IS NOT NULL AND icon_id NOT IN (SELECT id FROM software_icons);

4. Verify no invalid refs remain

   SELECT COUNT(*) FROM software WHERE icon_id IS NOT NULL AND icon_id NOT IN (SELECT id FROM software_icons);

   → Result: 0

5. Rerun the installer (from /root, not /tmp)
   After ensuring /tmp was remounted to disk (so it had enough free space), I ran:

   ./forticlientems_7.4.4.2034.F.amd64.bin -- --allowed_hosts '*' --enable_remote_https

The installer passed the failing mantis_1141481_mt_improvement_upgrade_changes.sql step and completed cleanly.

---

:pushpin: Points worth noting

• The default upgrade path uses vdom_id in some schemas, but my installation did not use that, so the Sqitch script’s multi-tenant logic failed.

• The root cause was data drift — some rows in software referenced icons that didn’t exist in software_icons.

• This fix worked without losing data — it only nullified invalid icon_id pointers.

• Be sure to not run the installer from /tmp (Ubuntu uses tmpfs which is too small for EMS upgrades). Mount /tmp to disk space before running.

If anyone from Fortinet or the community has seen this exact error before (or has a KB article), I’d love to compare notes.

Thanks!