Issue Plesk migrator problem

[Pascal_Netenvie]

Server operating system version

Debian 11.7

Plesk version and microupdate number

18.0.54

Hello,
i use migrator to move multiple sites from plesk 18.0.44 (under Deb 9) to plesk 18.0.54 (under Deb 11.7).

For most of the websites it work ok.
But for 2 sites i have this error :

Attempt #1 failed with the following error:
Unable to create subscription 'domain1.com'.
Command execution failed on the local server with non-zero exit code.
command: /opt/psa/bin/subscription --create domain1.com -hosting true -hst_type phys -login mylogin999 -passwd '' -ip 99.99.99.99 -do-not-apply-skeleton -notify false -guid 8fde6337-b3df-45b7-a725-a1e43636a60d -creation-date 2017-12-13 -mail_service false -ignore-nonexistent-options
exit code: 1
stdout:
stderr: [2023-09-05 16:28:09.578] 3175932:64f73af86b46f ERR [panel] Unable to load object of type PhDomain with id=29: Unable to update domain data: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8fde6337-b3df-45b7-a725-a1e43636a60d' for key 'guid', query was: UPDATE `domains` SET `id` = ?, `cr_date` = ?, `name` = ?, `displayName` = ?, `dns_zone_id` = ?, `status` = ?, `htype` = ?, `real_size` = ?, `cl_id` = ?, `cert_rep_id` = ?, `limits_id` = ?, `params_id` = ?, `guid` = ?, `overuse` = ?, `vendor_id` = ?, `webspace_id` = ?, `parentDomainId` = ?, `webspace_status` = ?, `permissions_id` = ?, `external_id` = ?, `adminDescription` = ?, `resellerDescription` = ?, `description` = ?, `gl_filter` = ?, `icpStatus` = ?, `icpPermit` = ?, `monthlyTraffic` = ? WHERE (id = 29)
Unable to load object of type PhDomain with id=29: Unable to update domain data: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8fde6337-b3df-45b7-a725-a1e43636a60d' for key 'guid', query was: UPDATE `domains` SET `id` = ?, `cr_date` = ?, `name` = ?, `displayName` = ?, `dns_zone_id` = ?, `status` = ?, `htype` = ?, `real_size` = ?, `cl_id` = ?, `cert_rep_id` = ?, `limits_id` = ?, `params_id` = ?, `guid` = ?, `overuse` = ?, `vendor_id` = ?, `webspace_id` = ?, `parentDomainId` = ?, `webspace_status` = ?, `permissions_id` = ?, `external_id` = ?, `adminDescription` = ?, `resellerDescription` = ?, `description` = ?, `gl_filter` = ?, `icpStatus` = ?, `icpPermit` = ?, `monthlyTraffic` = ? WHERE (id = 29)
Unable to load object of type BsDomain with id=29: Unable to update domain data: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8fde6337-b3df-45b7-a725-a1e43636a60d' for key 'guid', query was: UPDATE `domains` SET `id` = ?, `cr_date` = ?, `name` = ?, `displayName` = ?, `dns_zone_id` = ?, `status` = ?, `htype` = ?, `real_size` = ?, `cl_id` = ?, `cert_rep_id` = ?, `limits_id` = ?, `params_id` = ?, `guid` = ?, `overuse` = ?, `vendor_id` = ?, `webspace_id` = ?, `parentDomainId` = ?, `webspace_status` = ?, `permissions_id` = ?, `external_id` = ?, `adminDescription` = ?, `resellerDescription` = ?, `description` = ?, `gl_filter` = ?, `icpStatus` = ?, `icpPermit` = ?, `monthlyTraffic` = ? WHERE (id = 29)
An error occurred during domain creation: An error occurred while updating domain settings: Unable to update domain data: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8fde6337-b3df-45b7-a725-a1e43636a60d' for key 'guid', query was: UPDATE `domains` SET `id` = ?, `cr_date` = ?, `name` = ?, `displayName` = ?, `dns_zone_id` = ?, `status` = ?, `htype` = ?, `real_size` = ?, `cl_id` = ?, `cert_rep_id` = ?, `limits_id` = ?, `params_id` = ?, `guid` = ?, `overuse` = ?, `vendor_id` = ?, `webspace_id` = ?, `parentDomainId` = ?, `webspace_status` = ?, `permissions_id` = ?, `external_id` = ?, `adminDescription` = ?, `resellerDescription` = ?, `description` = ?, `gl_filter` = ?, `icpStatus` = ?, `icpPermit` = ?, `monthlyTraffic` = ? WHERE (id = 29)

Do you have a solution to fix this ?

 

[Pascal_Netenvie]

Ok i compared domains table in both psa base and yes there is already 2 websites on target server with same guid.
The fact is, in past, i created a migration for this 2 websites and used it to create 2 new websites from these copies on target server.

So now how to fix this ? In which way can i change guid without causing problems ?
Do i have to change guid for sites on source server or change guid for 2 copies i did on target ?
Last choice sounds more logic to me as they have updated/new domains ...
But how to do this without causing problem ? Is there a way to calculate guid from domain ? Do i have to change it also in some other tables ?

 

[Kaspar]

The guid is random generated string. I don't think changing the guid of a domain can cause any (major) issue. You could alter the guid by just changing one of the charters to a (different) number. So for example if the domain guid is 8fde6337-b3df-45b7-a725-a1e43636a60d, change the last charter, in this case d to a 1. If it causes any issue, you can always change it back.

 

[Peter Debik]

Alternative approach:

1) Backup the psa database

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

2)

# 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"

 

[Peter Debik]

And one more, similar to Kaspar's:

1) Backup the psa database

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

2) Find ID of the domains that have the same GUID:

# plesk db "select id from domains where guid='3ebe939b-c58c-4a56-a3e4-98750d90b157'\G;"

outputs something like

*************************** 1. row ***************************
id: 137

*************************** 2. row ***************************
id: 338

3) Manually generate a new GUID by changing several symbols of the GUID from the error, for example from:
3ebe939b-c58c-4a56-a3e4-98750d90b157
to
3ebe939b-c58c-4a56-a3e4-98750d90b378

4) Verify that the newly generated GUID is not assigned to any domain:

# plesk db "select guid,id from domains where guid='3ebe939b-c58c-4a56-a3e4-98750d90b378'\G;"

5) set newly generated GUID to one of the domains ID from step №3:

# plesk db "update domains set guid='3ebe939b-c58c-4a56-a3e4-98750d90b378' where id=137;"

 

[Peter Debik]

Root cause according to docs here can be that a migrated domain on the target server was renamed to another, and then it was attempted to migrate the renamed domain again.

 

[Pascal_Netenvie]

Alternative approach:

1) Backup the psa database

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

2)

# 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"

Hi Peter,
This approach can't work cause there can't be duplicate GUID in base, and that's why migrator fail ...
Thx for anwsers.

 

[Pascal_Netenvie]

And one more, similar to Kaspar's:

1) Backup the psa database

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

2) Find ID of the domains that have the same GUID:

# plesk db "select id from domains where guid='3ebe939b-c58c-4a56-a3e4-98750d90b157'\G;"

outputs something like

*************************** 1. row ***************************
id: 137

*************************** 2. row ***************************
id: 338

3) Manually generate a new GUID by changing several symbols of the GUID from the error, for example from:
3ebe939b-c58c-4a56-a3e4-98750d90b157
to
3ebe939b-c58c-4a56-a3e4-98750d90b378

4) Verify that the newly generated GUID is not assigned to any domain:

# plesk db "select guid,id from domains where guid='3ebe939b-c58c-4a56-a3e4-98750d90b378'\G;"

5) set newly generated GUID to one of the domains ID from step №3:

# plesk db "update domains set guid='3ebe939b-c58c-4a56-a3e4-98750d90b378' where id=137;"

Yes i will test this one (thx Kaspar).
One last question : This GUID is not used as key elsewhere ?