search

craftcms / cms

Build bespoke content experiences with Craft.
https://craftcms.com
Other
3.28k stars 635 forks source link

Integrity constraint violation: 1062 Duplicate entry 'fieldTitle-global' for key 'fields_handle_context_unq_idx' #4360

Closed p1729 closed 5 years ago

p1729 commented 5 years ago

Description

Integrity constraint violation: 1062 Duplicate entry 'fieldTitle-global' for key 'fields_handle_context_unq_idx'

Steps to reproduce

  1. Create a field with a handle, now delete the field and create the another handle with same name (to get different UID for the field but the fieldHandle name should be same)
  2. Now propagate these changes through project.yaml on another instance
  3. If insertion is happening before deletion you will get the constraint violation error

Additional info

ghost commented 5 years ago

Also getting a similar error, it might be caused by this bug too. The field that is causing problems on my end seems to be the sku.

This might help trying to investigate this bug: https://pastebin.com/56Qxvqqs

andris-sevcenko commented 5 years ago

@tim-pixeldeluxe your issue is not related to this at all. It's related to importing elements and doesn't seem like it has to do anything with project config. Please open a separate issue for this with more information what you're doing and also more information on how does the ProductImporter work, which, I assume, seems to be custom :)

@p1729 I'm not able to reproduce this issue.

If insertion is happening before deletion you will get the constraint violation error

Which is exactly why we're parsing the changes in the order deleted>changed>added (https://github.com/craftcms/cms/blob/develop/src/services/ProjectConfig.php#L940-L959), so that makes it curious as to why this is happening for you. Can you share a stack trace?

andris-sevcenko commented 5 years ago

@p1729 I'll assume you haven't encountered this since. If you are still experiencing this, can you, please, share a stack trace?

lindseybradford commented 4 years ago

Hit this issue a few times as well and have to in and manually delete the duplicate row.

2020-06-05 10:21:42 [-][4][-][error][yii\db\IntegrityException] PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'site_hero-global' for key 'fields_handle_context_unq_idx' in /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Command.php:1293
Stack trace:
#0 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Command.php(1293): PDOStatement->execute()
#1 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Command.php(1093): yii\db\Command->internalExecute()
#2 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Schema.php(432): yii\db\Command->execute()
#3 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/ActiveRecord.php(600): yii\db\Schema->insert()
#4 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/ActiveRecord.php(566): yii\db\ActiveRecord->insertInternal()
#5 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/BaseActiveRecord.php(678): yii\db\ActiveRecord->insert()
#6 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/Fields.php(1534): yii\db\BaseActiveRecord->save()
#7 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/Fields.php(826): craft\services\Fields->applyFieldSave()
#8 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1102): craft\services\Fields->handleChangedField()
#9 [internal function]: craft\services\ProjectConfig->handleChangeEvent()
#10 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Component.php(627): call_user_func()
#11 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(675): yii\base\Component->trigger()
#12 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/helpers/ProjectConfig.php(64): craft\services\ProjectConfig->processConfigChanges()
#13 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/Sections.php(595): craft\helpers\ProjectConfig::ensureAllFieldsProcessed()
#14 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1102): craft\services\Sections->handleChangedSection()
#15 [internal function]: craft\services\ProjectConfig->handleChangeEvent()
#16 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Component.php(627): call_user_func()
#17 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(678): yii\base\Component->trigger()
#18 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1092): craft\services\ProjectConfig->processConfigChanges()
#19 [internal function]: craft\services\ProjectConfig->handleChangeEvent()
#20 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Component.php(627): call_user_func()
#21 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(672): yii\base\Component->trigger()
#22 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1190): craft\services\ProjectConfig->processConfigChanges()
#23 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(539): craft\services\ProjectConfig->_applyChanges()
#24 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/controllers/ConfigSyncController.php(52): craft\services\ProjectConfig->applyYamlChanges()
#25 [internal function]: craft\controllers\ConfigSyncController->actionApplyYamlChanges()
#26 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#27 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams()
#28 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Controller.php(178): yii\base\Controller->runAction()
#29 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction()
#30 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Application.php(291): yii\base\Module->runAction()
#31 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Application.php(685): craft\web\Application->runAction()
#32 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Application.php(248): craft\web\Application->_processConfigSyncLogic()
#33 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest()
#34 /home/vagrant/projects/viget/alarm.com/web/index.php(21): yii\base\Application->run()
#35 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'site_hero-global' for key 'fields_handle_context_unq_idx'
The SQL being executed was: INSERT INTO `craft_fields` (`uid`, `groupId`, `name`, `handle`, `context`, `instructions`, `searchable`, `translationMethod`, `translationKeyFormat`, `type`, `settings`, `dateCreated`, `dateUpdated`) VALUES ('bc90f1df-b039-4618-813e-bab7024adc31', 1, 'Hero', 'site_hero', 'global', '', 1, 'site', NULL, 'craft\\fields\\Matrix', '{\"contentTable\":\"{{%matrixcontent_site_hero}}\",\"maxBlocks\":\"1\",\"minBlocks\":\"\",\"propagationMethod\":\"all\"}', '2020-06-05 17:21:42', '2020-06-05 17:21:42') in /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Schema.php:674
Stack trace:
#0 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Command.php(1298): yii\db\Schema->convertException()
#1 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Command.php(1093): yii\db\Command->internalExecute()
#2 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/Schema.php(432): yii\db\Command->execute()
#3 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/ActiveRecord.php(600): yii\db\Schema->insert()
#4 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/ActiveRecord.php(566): yii\db\ActiveRecord->insertInternal()
#5 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/db/BaseActiveRecord.php(678): yii\db\ActiveRecord->insert()
#6 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/Fields.php(1534): yii\db\BaseActiveRecord->save()
#7 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/Fields.php(826): craft\services\Fields->applyFieldSave()
#8 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1102): craft\services\Fields->handleChangedField()
#9 [internal function]: craft\services\ProjectConfig->handleChangeEvent()
#10 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Component.php(627): call_user_func()
#11 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(675): yii\base\Component->trigger()
#12 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/helpers/ProjectConfig.php(64): craft\services\ProjectConfig->processConfigChanges()
#13 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/Sections.php(595): craft\helpers\ProjectConfig::ensureAllFieldsProcessed()
#14 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1102): craft\services\Sections->handleChangedSection()
#15 [internal function]: craft\services\ProjectConfig->handleChangeEvent()
#16 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Component.php(627): call_user_func()
#17 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(678): yii\base\Component->trigger()
#18 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1092): craft\services\ProjectConfig->processConfigChanges()
#19 [internal function]: craft\services\ProjectConfig->handleChangeEvent()
#20 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Component.php(627): call_user_func()
#21 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(672): yii\base\Component->trigger()
#22 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(1190): craft\services\ProjectConfig->processConfigChanges()
#23 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/services/ProjectConfig.php(539): craft\services\ProjectConfig->_applyChanges()
#24 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/controllers/ConfigSyncController.php(52): craft\services\ProjectConfig->applyYamlChanges()
#25 [internal function]: craft\controllers\ConfigSyncController->actionApplyYamlChanges()
#26 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#27 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams()
#28 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Controller.php(178): yii\base\Controller->runAction()
#29 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction()
#30 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Application.php(291): yii\base\Module->runAction()
#31 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Application.php(685): craft\web\Application->runAction()
#32 /home/vagrant/projects/viget/alarm.com/vendor/craftcms/cms/src/web/Application.php(248): craft\web\Application->_processConfigSyncLogic()
#33 /home/vagrant/projects/viget/alarm.com/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest()
#34 /home/vagrant/projects/viget/alarm.com/web/index.php(21): yii\base\Application->run()
#35 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry 'site_hero-global' for key 'fields_handle_context_unq_idx'
)

2020-06-05 10:21:41 [-][4][-][info][application] $_GET = [
    'p' => 'admin/actions/config-sync/apply-yaml-changes'
]
juddlyon commented 4 years ago

I've had this happen a few times when writing and testing migrations.

_output Exception: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'defaultPageHeroBgImage-global' for key 'fields_handle_context_unqidx'

The SQL being executed was:

INSERT INTO `fields` 
            (`uid`, 
             `groupid`, 
             `name`, 
             `handle`, 
             `context`, 
             `instructions`, 
             `searchable`, 
             `translationmethod`, 
             `translationkeyformat`, 
             `type`, 
             `settings`, 
             `datecreated`, 
             `dateupdated`) 
VALUES     ('0f46543a-6b38-45a6-a0ae-3f1db3a22bd5', 
            9, 
            'Default Hero Background Image', 
            'defaultPageHeroBgImage', 
            'global', 
            '', 
            1, 
            'site', 
            NULL, 
            'craft\\fields\\Assets', 
'{\"allowSelfRelations\":false,\"allowedKinds\":null,\"defaultUploadLocationSource\":\"volume:70a8ba0e-8bed-49ec-81cb-1ac3937ea5fd\",\"defaultUploadLocationSubpath\":\"\",\"limit\":1,\"localizeRelations\":\"\",\"restrictFiles\":false,\"selectionLabel\":\"\",\"showUnpermittedFiles\":false,\"showUnpermittedVolumes\":false,\"singleUploadLocationSource\":\"\",\"singleUploadLocationSubpath\":null,\"source\":null,\"sources\":[\"volume:70a8ba0e-8bed-49ec-81cb-1ac3937ea5fd\"],\"targetSiteId\":null,\"useSingleFolder\":false,\"validateRelatedElements\":false,\"viewMode\":\"list\"}'
            , 
'2020-07-24 00:24:02', 
'2020-07-24 00:24:02')
brandonkelly commented 4 years ago

We got to the bottom of the original issue reported here in #5946, and have fixed the bug for Craft 3.5.

@juddlyon if you’re not running into the error while applying project config changes, then it’s more likely a bug in your own code. You can’t have two global fields with the same handle.

juddlyon commented 4 years ago

I encountered this when deploying to my staging server where the field didn't yet exist. I'm running migrate/all prior to project-config/sync. My DB is disposable in this case but I'm trying to understand what went haywire.

brandonkelly commented 4 years ago

@juddlyon The error is saying that there is already a row in the fields table with the same handle as the one you were trying to create. MySQL wouldn't give you that error unless it was true.

juddlyon commented 4 years ago

Thanks @brandonkelly. I figured this out. Posting this here in case anybody else makes the same mistake.

  1. Locally I manually created a migration and ran it
  2. general config file has useProjectConfigFile => true so project.yaml now has the new field
  3. Deployed to staging where by deployment script runs craft migrate/all and creates the field
  4. The deployment script then runs craft project-config/sync and it blows up since the field already exists (albeit for 3 seconds)

TL;DR: I was trying to programmatically create the field twice upon deployment