Unable to create domain alias or Plesk upgrade fails: Duplicate entry for key ‘guid’

Symptoms

• Unable to upgrade Plesk with the following error message: 

  Migration failed: 18.0.31/2020-09-28-06-53-58_add_unique_guid_to_domains.php
  Query failed: ALTER TABLE `domains` ADD UNIQUE KEY `guid` (`guid` ASC)
  Duplicate entry '3ebe939b-c58c-4a56-a3e4-98750d90b157' for key 'guid'

  ---

  ERROR: Duplicate guid were found in the 'domains' table:
  3ebe939b-c58c-4a56-a3e4-98750d90b157

• In an attempt to create a domain alias in Plesk > Domains > Add domain Alias, the process fails with the error below:

  No matches found

Cause

Database inconsistency, several domains have the same GUID. 

Resolution

1. Log into the server via SSH.

2. Create a backup for Plesk database:

   # plesk db dump psa > psa.backup`date +%F-%s`.sql

3. Run the command below to fix the database inconsistency with the duplicated domain GUID: 

   # plesk db -Ne "SELECT guid, count(guid) FROM domains GROUP BY guid HAVING COUNT(guid) > 1" | while read G C; do echo "> DUPLICATED GUID: $G"; for ((i=0;i<(C-1);i++)); do plesk db -Ne "SELECT id, name FROM domains WHERE guid = '$G' limit 1" | while read I D; do plesk db -Ne "UPDATE domains SET guid = UUID() WHERE id = '$I'"; echo "- FIXED: $D"; done; done; done; echo "> COMPLETED"