Loading...
> >

Resolving "duplicate key value violates unique constraint" DB errors

TomhipTomhip Posts: 418
edited January 19 in Technical Help (nuts n bolts)

So was updating my DAZ and took a look at the log file to check for any errors.

I can see several Warnings of the form:

2025-01-19 21:35:55.431 [WARNING] :: \src\sdksource\cloud\dzcloudtasknotifier.cpp(201): Query (UPDATE "product" SET (...)
"2025-01-19 21:35:55.431 [WARNING] :: \src\sdksource\cloud\dzcloudtasknotifier.cpp(201): ERROR:  duplicate key value violates unique constraint "product_guid_key"
DETAIL:  Key (guid)=(b8f97527-7ff1-4aa3-944b-e0f12dbe3a25) already exists."

 

As I understand this DAZ is trying to do some Update on a row but there already is a product under that GUID.

I have connected to the Db and ran queries to see whats sitting on the guids and it seems its always the correct products. I have tried to uninstall the offending products but that seems to make no difference and in fact one of the product I have uninstalled months ago.

 

Thinking I can try to install one again and use the "generate new guid" option but that may make it even worse. Alternatively uninstall and then delete these rows. Any ideas?

 

Post edited by Tomhip on

Comments

  • crosswindcrosswind Posts: 7,707

    You logged in Daz Connect with the option of Update Metadata ？

    Firstly, try Fix Installed File Registry in DIM > Advanced Settings > Installation.. by right-click on your Daz Library (ss1)  Then, try again.

    SNAG-2025-1-20-003.png
    1191 x 914 - 96K
  • TomhipTomhip Posts: 418
    edited January 20

    crosswind said:

    You logged in Daz Connect with the option of Update Metadata ？

    Not sure what you mean by that? I have connected to Postgre db as superuser.

     

    crosswind said:

    Firstly, try Fix Installed File Registry in DIM > Advanced Settings > Installation.. by right-click on your Daz Library (ss1)  Then, try again.

    That did not help unfortunately, still same issue with the same 10 products.

     

    Post edited by Tomhip on
  • crosswindcrosswind Posts: 7,707

    The message in the log stated it was updating metadata from cloud, aka Daz Connect. Check the login status in your Smart Content, see below ss1 (the button in upper-right corner). If it is Work Offline, means you've logged in Daz Connect.

    Besides, I don't understand. What do you mean by "still same issue with the same 10 products" ? And what did you get by filtering the above GUID in postgreSQL DBA software ? IM Ballet Poses, like the one in below ss2 ?

    SNAG-2025-1-20-004.png
    501 x 1294 - 240K
    SNAG-2025-1-20-005.png
    2560 x 1392 - 85K
  • Richard HaseltineRichard Haseltine Posts: 102,027

    crosswind said:

    You logged in Daz Connect with the option of Update Metadata ？

    Firstly, try Fix Installed File Registry in DIM > Advanced Settings > Installation.. by right-click on your Daz Library (ss1)  Then, try again.

    That is a file which lists files that DIM installs from a product but which were already present - it is there to ensure that uninstall a product will not remove needed shared files.

    The error is referring to metadata/database issues - two products have the same identifier and so clash. This would require a Daz fix.

  • TomhipTomhip Posts: 418
    edited January 20

    crosswind said:

    The message in the log stated it was updating metadata from cloud, aka Daz Connect. Check the login status in your Smart Content, see below ss1 (the button in upper-right corner). If it is Work Offline, means you've logged in Daz Connect.

    Besides, I don't understand. What do you mean by "still same issue with the same 10 products" ? And what did you get by filtering the above GUID in postgreSQL DBA software ? IM Ballet Poses, like the one in below ss2 ?

    Yes I was Logged into DAZ.

    That error comes up for 10 items. "IM Ballet Poses" was just one of examples.

    The final error says "[WARNING] :: Metadata update failed: 94 items succeeded, 10 items failed"

     

    Full error:

    2025-01-20 08:53:47.447 [WARNING] :: \src\sdksource\cloud\dzcloudtasknotifier.cpp(201): Query (UPDATE "product" SET name = 'IM Ballet Poses', default_name = 'IM Ballet Poses', store_id = 1, token = '79932', artists = 'Paper Tiger,Ironman', description = '', guid = 'b8f97527-7ff1-4aa3-944b-e0f12dbe3a25', meta_hash = '96ac5b82ce604dbb465648cc2e6d5532', file_hash = '58c00c16f06c4ceb3d1e55812785181f', thumbnail_path = '', thumbnail_hash = '', last_update = '2020-12-16 07:12:52', date_purchased = '2023-08-28 23:34:38', date_installed = '2025-01-20 08:53:47', release_date = '1970-01-01 00:00:00', group_num = 0, hide = 'f', is_vendor = 't', has_cloud_meta_data = 't', is_installed = 'f', is_cloud_owned = 't', needs_update = 'f', normalized_name = 'im ballet poses', normalized_name_first_letter = 'I' WHERE id = 8742) failed with exception: ERROR:  duplicate key value violates unique constraint "product_guid_key"
    DETAIL:  Key (guid)=(b8f97527-7ff1-4aa3-944b-e0f12dbe3a25) already exists.

    2025-01-20 08:53:47.448 [WARNING] :: \src\sdksource\cloud\dzcloudtasknotifier.cpp(201): ERROR:  duplicate key value violates unique constraint "product_guid_key"
    DETAIL:  Key (guid)=(b8f97527-7ff1-4aa3-944b-e0f12dbe3a25) already exists.

     

    I queried the Id of "WHERE id = 8742" and it comes as empty.

     

    Select on (b8f97527-7ff1-4aa3-944b-e0f12dbe3a25) exported to csv

    "id";"name";"default_name";"store_id";"token";"artists";"description";"guid";"meta_hash";"file_hash";"thumbnail_hash";"thumbnail_path";"last_update";"date_purchased";"group_num";"hide";"is_vendor";"has_cloud_meta_data";"is_installed";"is_cloud_owned";"needs_update";"normalized_name";"normalized_name_first_letter";"date_installed";"release_date"
    2824;"IM Ballet Poses";"IM Ballet Poses";1;"79932 ";"Paper Tiger,Ironman";"";"b8f97527-7ff1-4aa3-944b-e0f12dbe3a25";"96ac5b82ce604dbb465648cc2e6d5532";"58c00c16f06c4ceb3d1e55812785181f";"";"/Runtime/Support/DAZ_3D_79932__IM_Ballet_Poses.jpg";"2024-04-30 20:48:03";"2020-12-18 16:19:57";0;f;t;f;t;f;f;"im ballet poses";"I";"2023-09-02 14:57:42.118";"2024-04-30 20:48:03"

     

     

     

     

    This are all the items with this error:

    'Wet And Tanned Skins For Genesis 8 Male(s)'

    'Sexy Stewardess Outfit for Genesis 8 Female'

    'Egyptian Poses for Genesis 8 Female'

    'People Carrying People Volume 2 for Genesis 8'

    'Bunny''s Nightmare Outfit and Poses for Genesis 8 Females'

    'IM Belly Dance Pose Collection'

    'IM Ballet Poses'

    'Glamorous Style Hair for Genesis 8 and 9'

    'Micaela for Gabriela 8'

    Post edited by Tomhip on
  • TomhipTomhip Posts: 418
    edited January 20

    Richard Haseltine said:

    crosswind said:

    You logged in Daz Connect with the option of Update Metadata ？

    Firstly, try Fix Installed File Registry in DIM > Advanced Settings > Installation.. by right-click on your Daz Library (ss1)  Then, try again.

    That is a file which lists files that DIM installs from a product but which were already present - it is there to ensure that uninstall a product will not remove needed shared files.

    The error is referring to metadata/database issues - two products have the same identifier and so clash. This would require a Daz fix.

    So those GUIDs come from DAZ and are not local to users? I googled a bit around the issue and seems some suggested otherwise?

    Additionally when you "Edit Metadata" of product in Studio you get option to "Generate" next to the GUID in top right.

    Post edited by Tomhip on
  • crosswindcrosswind Posts: 7,707
    edited January 20

    Full error:

    2025-01-20 08:53:47.447 [WARNING] :: \src\sdksource\cloud\dzcloudtasknotifier.cpp(201): Query (UPDATE "product" SET name = 'IM Ballet Poses', default_name = 'IM Ballet Poses', store_id = 1, token = '79932', artists = 'Paper Tiger,Ironman', description = '', guid = 'b8f97527-7ff1-4aa3-944b-e0f12dbe3a25', meta_hash = '96ac5b82ce604dbb465648cc2e6d5532', file_hash = '58c00c16f06c4ceb3d1e55812785181f', thumbnail_path = '', thumbnail_hash = '', last_update = '2020-12-16 07:12:52', date_purchased = '2023-08-28 23:34:38', date_installed = '2025-01-20 08:53:47', release_date = '1970-01-01 00:00:00', group_num = 0, hide = 'f', is_vendor = 't', has_cloud_meta_data = 't', is_installed = 'f', is_cloud_owned = 't', needs_update = 'f', normalized_name = 'im ballet poses', normalized_name_first_letter = 'I' WHERE id = 8742) failed with exception: ERROR:  duplicate key value violates unique constraint "product_guid_key"
    DETAIL:  Key (guid)=(b8f97527-7ff1-4aa3-944b-e0f12dbe3a25) already exists.

    2025-01-20 08:53:47.448 [WARNING] :: \src\sdksource\cloud\dzcloudtasknotifier.cpp(201): ERROR:  duplicate key value violates unique constraint "product_guid_key"
    DETAIL:  Key (guid)=(b8f97527-7ff1-4aa3-944b-e0f12dbe3a25) already exists.

     

    I queried the Id of "WHERE id = 8742" and it comes as empty.

     

    Select on (b8f97527-7ff1-4aa3-944b-e0f12dbe3a25) exported to csv

    "id";"name";"default_name";"store_id";"token";"artists";"description";"guid";"meta_hash";"file_hash";"thumbnail_hash";"thumbnail_path";"last_update";"date_purchased";"group_num";"hide";"is_vendor";"has_cloud_meta_data";"is_installed";"is_cloud_owned";"needs_update";"normalized_name";"normalized_name_first_letter";"date_installed";"release_date"
    2824;"IM Ballet Poses";"IM Ballet Poses";1;"79932 ";"Paper Tiger,Ironman";"";"b8f97527-7ff1-4aa3-944b-e0f12dbe3a25";"96ac5b82ce604dbb465648cc2e6d5532";"58c00c16f06c4ceb3d1e55812785181f";"";"/Runtime/Support/DAZ_3D_79932__IM_Ballet_Poses.jpg";"2024-04-30 20:48:03";"2020-12-18 16:19:57";0;f;t;f;t;f;f;"im ballet poses";"I";"2023-09-02 14:57:42.118";"2024-04-30 20:48:03"

     

    Well, see the above Token data I've highlighted in red. Daz Cloud wants to update the record with Token '79932' But in cms database, the record of IM Ballet Poses has a Token "79932 " (Be noted there's a space character after number: 79932)  (Same issue on my side but I never update metadata via Daz Connect).

    Besides, the fields of has_cloud_meta_data, is_installed, is_cloud_owned have totoally different data in above Full error paragraph (true, false, true)... comparing to the fields data in exported CSV (false, true, false) ...

    So, I presume Daz Cloud didn't find the record with all conditions matched ("space character" in Token is just the culprit...) , then it would create a new record, however, of course GUID has to be unique, then an update failure was reported in the log.

    You can try correct the Token to  '79932' i.e. delete the space character... (or correct it in DSX file of this product in runtime\support, then re-import metadata...) Then I guess this error will be gone when Update, at least for this IM Ballet Poses product.

     

    More clues, if the above way doesn't work:

    You can find a table named: migration_link in which there're two fields: old_id and new_id . SQL Query by "where old_id = 8742" and then "where new_id = 8742". See what you'll find... (probably nothing but no harm to check...)

     

    Post edited by crosswind on
  • crosswindcrosswind Posts: 7,707
    edited January 20

    Tomhip said:

    Richard Haseltine said:

    crosswind said:

    You logged in Daz Connect with the option of Update Metadata ？

    Firstly, try Fix Installed File Registry in DIM > Advanced Settings > Installation.. by right-click on your Daz Library (ss1)  Then, try again.

    That is a file which lists files that DIM installs from a product but which were already present - it is there to ensure that uninstall a product will not remove needed shared files.

    The error is referring to metadata/database issues - two products have the same identifier and so clash. This would require a Daz fix.

    So those GUIDs come from DAZ and are not local to users? I googled a bit around the issue and seems some suggested otherwise?

    Additionally when you "Edit Metadata" of product in Studio you get option to "Generate" next to the GUID in top right.

    GUID comes from *.dsx Metadata file. When installing the product or re-importing Metadata, GUID is written to cms database. Technically you can Generate a new GUID in Content DB Editor, but if you update or re-import metadata, a product record will be created in cms database, i.e. same product will have two records which is not correct.

    So, better not generate new GUID directly. If you do want to do so, tick Auto Export option beforehand in Content DB Editor, so as to update GUID data in DSX file at the same time...after modification.

    Post edited by crosswind on
  • TomhipTomhip Posts: 418

    Well, see the above Token data I've highlighted in red. Daz Cloud wants to update the record with Token '79932' But in cms database, the record of IM Ballet Poses has a Token "79932 " (Be noted there's a space character after number: 79932)  (Same issue on my side but I never update metadata via Daz Connect).

    Besides, the fields of has_cloud_meta_data, is_installed, is_cloud_owned have totoally different data in above Full error paragraph (true, false, true)... comparing to the fields data in exported CSV (false, true, false) ...

    So, I presume Daz Cloud didn't find the record with all conditions matched ("space character" in Token is just the culprit...) , then it would create a new record, however, of course GUID has to be unique, then an update failure was reported in the log.

    You can try correct the Token to  '79932' i.e. delete the space character... (or correct it in DSX file of this product in runtime\support, then re-import metadata...) Then I guess this error will be gone when Update, at least for this IM Ballet Poses product.

    Thats a good catch for the whitespace, bane of every dbm. I have run check on this across all the products and all include whitespace with one exception where token is just missing.

    "Micaela for Gabriela 8";"b3f58ab9-d312-47bd-ad1a-5e4622bde835";" 62143"
    "Glamorous Style Hair for Genesis 8 and 9";"3d474c8f-44c9-4e74-8735-443a7e0d4c0d";""
    "Egyptian Poses for Genesis 8 Female";"11c93728-9b96-4d7a-993e-984e6342bb99";"72329 "
    "IM Ballet Poses";"b8f97527-7ff1-4aa3-944b-e0f12dbe3a25";"79932 "
    "IM Belly Dance Pose Collection";"4401256d-a09d-4109-8d64-696e80339e51";"79838 "
    "People Carrying People Volume 2 for Genesis 8";"3bbaf4fe-e0a0-4040-a44a-7140cc164145";"73607 "
    "Wet And Tanned Skins For Genesis 8 Male(s)";"af1999b9-1d15-4bb7-b8e3-0ce718798f5a";"55305 "
    "Better Together Poses for Genesis 8";"2f0b2a05-1cad-4508-944a-936b52b5e8e0";"79860 "
    "Bunny's Nightmare Outfit and Poses for Genesis 8 Females";"8defd14a-dcc6-4836-bb13-638ddfd015d1";"78961  "
    "Sexy Stewardess Outfit for Genesis 8 Female";"38a3c322-b5b5-41be-ace6-38c5720a3d74";"59743 "

     

    More clues, if the above way doesn't work:

    You can find a table named: migration_link in which there're two fields: old_id and new_id . SQL Query by "where old_id = 8742" and then "where new_id = 8742". See what you'll find... (probably nothing but no harm to check...)

    It did in fact produce an entry... and resulting new_id had another entry of its own... 3 times. None have any matching entry in product table :D

    8742 => 32983 => 151545 => 779840

     

     

    If the data comes from DAZ i guess it is time for Support Ticket then?

     

     

  • crosswindcrosswind Posts: 7,707

    OK, if so, it proves that all the Token values in the metadata files of these 10 items' IM package are wrongly defined... while the metadata from Daz Connect are just correct ~~

    Yes, you can report the issues by filling a ticket(s)... though I doubt if you'll get updated IM packages then... You can fix them yourself ~~

Sign In or Register to comment.