Constraint violation #66

Geestig · 2020-02-06T11:08:59Z

Description

After updating to the lastest Craft version (3.4.4) cp-nav seems to only work locally. Whenever I deploy to my staging environment project.yaml throws a conflict.

Steps to reproduce

Update Craft CMS to latest version (3.4.4) and install CP-Nav
Set a new group with custom layout
Deploy to staging.

Additional info

Plugin version: 3.0.2
Craft version: 3.4.4

ERROR OUTPUT:
Applying changes from project.yaml ... error: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (REMOTE_DB.craft_cpnav_navigation, CONSTRAINT craft_cpnav_navigation_layoutId_fk FOREIGN KEY (layoutId) REFERENCES craft_cpnav_layout (id) ON DELETE CASCADE)
remote: The SQL being executed was: INSERT INTO craft_cpnav_navigation (layoutId, handle, currLabel, prevLabel, enabled, order, url, prevUrl, icon, customIcon, type, newWindow, uid, dateCreated, dateUpdated) VALUES (2, 'utilities', 'Utilities', 'Utilities', 0, NULL, 'utilities', 'utilities', 'tool', NULL, '', 0, 'e9adca43-4669-4c41-bba8-0c184c50739b', '2020-02-06 11:04:49', '2020-02-06 11:04:49')

The text was updated successfully, but these errors were encountered:

fthues · 2020-02-06T12:36:55Z

Maybe related:

I've just updated a site as well and I got duplicated navigation items for the default/admin layout.
So I ran a "Reset navigation" for the default layout and got a very similar key constraint error:

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`minileas_staging`.`cpnav_navigation`, CONSTRAINT `cpnav_navigation_layoutId_fk` FOREIGN KEY (`layoutId`) REFERENCES `cpnav_layout` (`id`) ON DELETE CASCADE)
The SQL being executed was: UPDATE `cpnav_navigation` SET `layoutId`=NULL, `handle`=NULL, `prevLabel`=NULL, `currLabel`=NULL, `enabled`=NULL, `order`=NULL, `prevUrl`=NULL, `url`=NULL, `icon`=NULL, `type`=NULL, `newWindow`=NULL, `dateUpdated`='2020-02-06 12:32:04' WHERE `id`=1 in /home/minileas/public_html/staging/vendor/yiisoft/yii2/db/Schema.php:674
Stack trace:
#0 /home/minileas/public_html/staging/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'UPDATE `cpnav_n...')
#1 /home/minileas/public_html/staging/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('UPDATE `cpnav_n...')
#2 /home/minileas/public_html/staging/vendor/yiisoft/yii2/db/ActiveRecord.php(332): yii\db\Command->execute()
#3 /home/minileas/public_html/staging/vendor/yiisoft/yii2/db/BaseActiveRecord.php(810): yii\db\ActiveRecord::updateAll(Array, Array)
#4 /home/minileas/public_html/staging/vendor/yiisoft/yii2/db/ActiveRecord.php(676): yii\db\BaseActiveRecord->updateInternal(NULL)
#5 /home/minileas/public_html/staging/vendor/yiisoft/yii2/db/BaseActiveRecord.php(681): yii\db\ActiveRecord->update(false, NULL)
#6 /home/minileas/public_html/staging/vendor/verbb/cp-nav/src/services/NavigationsService.php(186): yii\db\BaseActiveRecord->save(false)
#7 /home/minileas/public_html/staging/vendor/craftcms/cms/src/services/ProjectConfig.php(1084): verbb\cpnav\services\NavigationsService->handleChangedNavigation(Object(craft\events\ConfigEvent))
#8 [internal function]: craft\services\ProjectConfig->handleChangeEvent(Object(craft\events\ConfigEvent))
#9 /home/minileas/public_html/staging/vendor/yiisoft/yii2/base/Component.php(627): call_user_func(Array, Object(craft\events\ConfigEvent))
#10 /home/minileas/public_html/staging/vendor/craftcms/cms/src/services/ProjectConfig.php(658): yii\base\Component->trigger('updateItem', Object(craft\events\ConfigEvent))
#11 /home/minileas/public_html/staging/vendor/craftcms/cms/src/services/ProjectConfig.php(478): craft\services\ProjectConfig->processConfigChanges('cp-nav.navigati...', true, NULL)
#12 /home/minileas/public_html/staging/vendor/craftcms/cms/src/services/ProjectConfig.php(494): craft\services\ProjectConfig->set('cp-nav.navigati...', NULL, NULL)
#13 /home/minileas/public_html/staging/vendor/verbb/cp-nav/src/services/NavigationsService.php(251): craft\services\ProjectConfig->remove('cp-nav.navigati...')
#14 /home/minileas/public_html/staging/vendor/verbb/cp-nav/src/controllers/NavigationController.php(258): verbb\cpnav\services\NavigationsService->deleteNavigation(Object(verbb\cpnav\models\Navigation))
#15 [internal function]: verbb\cpnav\controllers\NavigationController->actionReset()
#16 /home/minileas/public_html/staging/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#17 /home/minileas/public_html/staging/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#18 /home/minileas/public_html/staging/vendor/craftcms/cms/src/web/Controller.php(178): yii\base\Controller->runAction('reset', Array)
#19 /home/minileas/public_html/staging/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('reset', Array)
#20 /home/minileas/public_html/staging/vendor/craftcms/cms/src/web/Application.php(290): yii\base\Module->runAction('cp-nav/navigati...', Array)
#21 /home/minileas/public_html/staging/vendor/craftcms/cms/src/web/Application.php(558): craft\web\Application->runAction('cp-nav/navigati...', Array)
#22 /home/minileas/public_html/staging/vendor/craftcms/cms/src/web/Application.php(269): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#23 /home/minileas/public_html/staging/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#24 /home/minileas/public_html/staging/web/index.php(21): yii\base\Application->run()
#25 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1452
    [2] => Cannot add or update a child row: a foreign key constraint fails (`minileas_staging`.`cpnav_navigation`, CONSTRAINT `cpnav_navigation_layoutId_fk` FOREIGN KEY (`layoutId`) REFERENCES `cpnav_layout` (`id`) ON DELETE CASCADE)
)

engram-design · 2020-02-11T21:56:28Z

Fixed in 3.0.3

fthues · 2020-02-13T10:39:38Z

I just attempted to reset the default layout, got this error again (using 3.0.3):

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`minileas_production`.`cpnav_navigation`, CONSTRAINT `cpnav_navigation_layoutId_fk` FOREIGN KEY (`layoutId`) REFERENCES `cpnav_layout` (`id`) ON DELETE CASCADE)
The SQL being executed was: UPDATE `cpnav_navigation` SET `layoutId`=NULL, `handle`=NULL, `prevLabel`=NULL, `currLabel`=NULL, `enabled`=NULL, `order`=NULL, `prevUrl`=NULL, `url`=NULL, `icon`=NULL, `type`=NULL, `newWindow`=NULL, `dateUpdated`='2020-02-13 10:33:06' WHERE `id`=1 in /home/minileas/public_html/production/vendor/yiisoft/yii2/db/Schema.php:674

engram-design · 2020-02-13T11:15:30Z

@fthues Are there any rows in your cpnav_layout database table?

fthues · 2020-02-13T11:34:52Z

Yes:

Also, I've just had this happen on my local environment when trying to reset the default layout:

engram-design · 2020-02-15T03:05:21Z

@fthues Do you have useProjectConfigFile enabled, and if so, can you share the cp-nav portion?

engram-design · 2020-02-15T03:06:46Z

@fthues Oh, and a snapshot of your cpnav_navigation table, just to check the layoutId column is as it should be

fthues · 2020-02-24T12:34:59Z

Here's the project.yaml excerpt

And the cpnav_navigation table:

Thanks!

fthues · 2020-02-25T10:34:09Z

I just updated to cp-nav 3.0.6 and now the error pops up whenever I try to access the control panel, not just when in the plugin's settings context:

So the control panel is completely inaccessible for me now.

engram-design · 2020-02-25T10:45:36Z

Spent some time tonight trying to reproduce this and I can’t. I can’t see how the data from a project config event would be null. I might ping @andris-sevcenko to see in what situations this can happen with project config..

engram-design · 2020-02-25T19:25:42Z

Originally from @andris-sevcenko

The first step would be to update Craft to the latest released version and then run a ./craft project-config/rebuild, I think.

Otherwise, it's hard to exclude bugs that have been already fixed and some minor project config discrepancies that might have crept in because of those bugs.

fthues · 2020-02-26T14:26:27Z

Running the latest version of both Craft and the plugin, did project-config/rebuild, the problem persists.

The plugin's handleChangedNavigation function receives an updateItem event with a $navigationId (via $event->tokenMatches[0]) UID that does not exist in the project config, only in the database:

The corresponding item has a different UID in the project config:

I'm guessing that's why both $event->oldValue and $event->newValue are NULL?

engram-design · 2020-02-26T23:57:47Z

@fthues Interesting. Maybe you can delete your project.yaml file, so that Craft re-generates it from the database? Something has clearly got out of sync here, and the rebuild command should've fixed that up...

engram-design · 2020-02-27T07:07:25Z

@fthues I'd also try clearing any caches. Otherwise - maybe you could send through an export of your cpnav_* tables?

Thanks for your patience!

KatieMFritz · 2020-03-13T14:20:50Z

I'm having a similar undefined offset error, but only for some of my profiles.

I'll try the troubleshooting steps above and report back.

KatieMFritz · 2020-03-13T15:13:10Z

Craft Pro 3.4.9 and latest CP Nav plugin.

It was working great except for one layout, tied to the "Manager" user group, which gets the error above on every CP screen.

Things I've tried:

clearing caches
deleting project.yaml and rebuilding project config
deleting the vendor/verbb directory and reinstalling
resetting the Manager layout
deleting the Manger layout and recreating it with default settings

No luck. I just uninstalled the plugin for now, but if you think of something, please let me know! Here's the cp-nav section of my project.yaml before uninstalling.

cp-nav:
  layouts:
    8a074b2e-54d6-470a-8638-1fb089fd14c9:
      isDefault: '0'
      name: Manager
      permissions: '["a17f6951-e1ec-488b-a78a-944f944347c2"]'
    dfafe559-47d8-416d-b24f-f261a7b6bbd3:
      isDefault: '1'
      name: Default
      permissions: null
    e98a5684-d3b0-4ca1-8604-d891a766c0fc:
      isDefault: '0'
      name: Student
      permissions: '["acbec432-50bf-4f50-8e0a-4d51f979e162","6bd490f9-575c-41be-bb21-cdbf168c664a"]'
  navigations:
    081cef12-3add-4ed3-aaab-7067fd5fca18:
      currLabel: Dashboard
      customIcon: null
      enabled: '1'
      handle: dashboard
      icon: gauge
      layoutId: '3'
      newWindow: '0'
      order: 1
      prevLabel: Dashboard
      prevUrl: dashboard
      type: '0'
      url: dashboard
    134218d1-39ac-4d5f-99af-c09248b657d6:
      currLabel: 'Entries (Pages)'
      customIcon: null
      enabled: '1'
      handle: entries
      icon: section
      layoutId: '2'
      newWindow: false
      order: '2'
      prevLabel: Entries
      prevUrl: entries
      type: '0'
      url: entries
    149a9dad-2854-4a5d-8e7c-d826ef2494c3:
      currLabel: Entries
      customIcon: null
      enabled: true
      handle: entries
      icon: section
      layoutId: '1'
      newWindow: false
      order: 1
      prevLabel: Entries
      prevUrl: entries
      type: ''
      url: entries
    17dcffa3-8f21-46bb-9f56-d433dac0646f:
      currLabel: Dashboard
      customIcon: null
      enabled: true
      handle: dashboard
      icon: gauge
      layoutId: '1'
      newWindow: false
      order: 0
      prevLabel: Dashboard
      prevUrl: dashboard
      type: ''
      url: dashboard
    1cb0df1c-1217-4bd5-8096-b275a8fd9620:
      currLabel: SEOmatic
      customIcon: null
      enabled: true
      handle: seomatic
      icon: /Users/fritzk/code/safs-effs/vendor/nystudio107/craft-seomatic/src/icon-mask.svg
      layoutId: '1'
      newWindow: false
      order: 8
      prevLabel: SEOmatic
      prevUrl: seomatic
      type: ''
      url: seomatic
    22640919-5fe0-42a5-8027-06fc66526047:
      currLabel: Categories
      customIcon: null
      enabled: true
      handle: categories
      icon: categories
      layoutId: '1'
      newWindow: false
      order: 3
      prevLabel: Categories
      prevUrl: categories
      type: ''
      url: categories
    242a8ac3-c605-4a2d-bb86-e87828245b9c:
      currLabel: 'Contact Info'
      customIcon: null
      enabled: '1'
      handle: contactInfo
      icon: mail
      layoutId: '2'
      newWindow: '0'
      order: 5
      prevLabel: 'Contact Info'
      prevUrl: globals/contactInfo
      type: manual
      url: globals/contactInfo
    2c75922c-c9ee-4176-9aaa-708c961e9bb7:
      currLabel: 'Blog Categories'
      customIcon: null
      enabled: '1'
      handle: blogCategories
      icon: tags
      layoutId: '2'
      newWindow: '0'
      order: 6
      prevLabel: 'Blog Categories'
      prevUrl: /admin/categories/blogCategories
      type: manual
      url: /admin/categories/blogCategories
    2c9bf91e-4187-4435-b63c-535f28875eb6:
      currLabel: Users
      customIcon: '["386"]'
      enabled: '1'
      handle: users
      icon: null
      layoutId: '2'
      newWindow: '0'
      order: 8
      prevLabel: Users
      prevUrl: users
      type: '0'
      url: users
    2caa497d-95e3-49f2-bd03-d128e53c18c3:
      currLabel: SEO
      customIcon: null
      enabled: '1'
      handle: seomatic
      icon: /srv/users/serverpilot/apps/safs-effs-live/public_html/vendor/nystudio107/craft-seomatic/src/icon-mask.svg
      layoutId: '2'
      newWindow: '0'
      order: 9
      prevLabel: SEOmatic
      prevUrl: seomatic
      type: '0'
      url: seomatic
    2f53a7e2-cf31-4f69-b6ac-d35d35cee966:
      currLabel: 'Edit Help Links'
      customIcon: null
      enabled: '1'
      handle: help-links
      icon: help
      layoutId: '2'
      newWindow: '0'
      order: 11
      prevLabel: 'Help Links'
      prevUrl: help-links
      type: '0'
      url: help-links
    44dbe402-2637-41bf-8dbb-3627fec9713f:
      currLabel: 'Assets (Images)'
      customIcon: null
      enabled: '1'
      handle: assets
      icon: assets
      layoutId: '3'
      newWindow: '0'
      order: 3
      prevLabel: Assets
      prevUrl: assets
      type: '0'
      url: assets
    48dc3812-e28c-42aa-85a5-62878f4a8f9d:
      currLabel: 'Entries (Pages)'
      customIcon: null
      enabled: '1'
      handle: entries
      icon: section
      layoutId: '3'
      newWindow: '0'
      order: 2
      prevLabel: Entries
      prevUrl: entries
      type: '0'
      url: entries
    54514163-a438-4176-8137-173647ca20cc:
      currLabel: Utilities
      customIcon: null
      enabled: true
      handle: utilities
      icon: tool
      layoutId: '1'
      newWindow: false
      order: 9
      prevLabel: Utilities
      prevUrl: utilities
      type: ''
      url: utilities
    54751a19-29e0-44be-b0b6-f86f1bd207cb:
      currLabel: Navigation
      customIcon: '["387"]'
      enabled: '1'
      handle: navigation
      icon: null
      layoutId: '2'
      newWindow: '0'
      order: 4
      prevLabel: Navigation
      prevUrl: globals/navigation
      type: manual
      url: globals/navigation
    5f28b1ca-8eee-4a72-8bc8-1ea477b7c871:
      currLabel: Settings
      customIcon: null
      enabled: true
      handle: settings
      icon: settings
      layoutId: '1'
      newWindow: false
      order: 10
      prevLabel: Settings
      prevUrl: settings
      type: ''
      url: settings
    64cfc3b1-7721-4d5a-b235-08260ef4f7a8:
      currLabel: 'Plugin Store'
      customIcon: null
      enabled: true
      handle: plugin-store
      icon: plugin
      layoutId: '1'
      newWindow: false
      order: 11
      prevLabel: 'Plugin Store'
      prevUrl: plugin-store
      type: ''
      url: plugin-store
    71382b5f-779a-4dad-beff-fb4033449bf5:
      currLabel: Users
      customIcon: null
      enabled: true
      handle: users
      icon: users
      layoutId: '1'
      newWindow: false
      order: 5
      prevLabel: Users
      prevUrl: users
      type: ''
      url: users
    81ae0a5f-dfc2-438b-9580-d14cf0b98453:
      currLabel: 'Course Categories'
      customIcon: null
      enabled: '1'
      handle: courseCategories
      icon: category
      layoutId: '2'
      newWindow: '0'
      order: 7
      prevLabel: 'Course Categories'
      prevUrl: /admin/categories/courseCategories
      type: manual
      url: /admin/categories/courseCategories
    890a9388-54a4-48e8-8477-ac4e28d1a284:
      currLabel: 'Utilities (Just in Case)'
      customIcon: null
      enabled: '1'
      handle: utilities
      icon: tool
      layoutId: '2'
      newWindow: '0'
      order: 10
      prevLabel: Utilities
      prevUrl: utilities
      type: '0'
      url: utilities
    8e674a5e-b973-4216-a5a7-b9f1370e7102:
      currLabel: 'Assets (Images)'
      customIcon: null
      enabled: '1'
      handle: assets
      icon: asset
      layoutId: '2'
      newWindow: false
      order: '3'
      prevLabel: Assets
      prevUrl: assets
      type: '0'
      url: assets
    95f67dbc-e06e-43b9-a457-4d4f5987a6f7:
      currLabel: Categories
      customIcon: '["385"]'
      enabled: '0'
      handle: categories
      icon: null
      layoutId: '2'
      newWindow: '0'
      order: 13
      prevLabel: Categories
      prevUrl: categories
      type: '0'
      url: categories
    99557ed5-d975-47d9-b7be-fce79d9d8455:
      currLabel: Globals
      customIcon: null
      enabled: true
      handle: globals
      icon: globe
      layoutId: '1'
      newWindow: false
      order: 2
      prevLabel: Globals
      prevUrl: globals
      type: ''
      url: globals
    9c418407-9c5d-423f-974e-7d408d75a4a6:
      currLabel: Categories
      customIcon: null
      enabled: '0'
      handle: categories
      icon: categories
      layoutId: '3'
      newWindow: '0'
      order: 5
      prevLabel: Categories
      prevUrl: categories
      type: '0'
      url: categories
    a55f001b-6b7f-4358-9ebb-abf485e8f3c9:
      currLabel: Assets
      customIcon: null
      enabled: true
      handle: assets
      icon: assets
      layoutId: '1'
      newWindow: false
      order: 4
      prevLabel: Assets
      prevUrl: assets
      type: ''
      url: assets
    ae3a449c-d1f7-4676-80f2-3ecdeff114ed:
      currLabel: Dashboard
      customIcon: null
      enabled: '1'
      handle: dashboard
      icon: gauge
      layoutId: '2'
      newWindow: '0'
      order: 1
      prevLabel: Dashboard
      prevUrl: dashboard
      type: '0'
      url: dashboard
    b0c83f3f-3731-4985-bca9-c51a5c9ce67e:
      currLabel: 'Blog Categories'
      customIcon: null
      enabled: '1'
      handle: blogCategories
      icon: tags
      layoutId: '3'
      newWindow: false
      order: '4'
      prevLabel: 'Blog Categories'
      prevUrl: /admin/categories/blogCategories
      type: manual
      url: /admin/categories/blogCategories
    f52f29f2-ef43-4420-aedc-1db5987aece1:
      currLabel: Globals
      customIcon: null
      enabled: '0'
      handle: globals
      icon: null
      layoutId: '2'
      newWindow: '0'
      order: 12
      prevLabel: Globals
      prevUrl: globals
      type: '0'
      url: globals
    f62017d7-70e4-44fc-a7d0-1f2f7d333b27:
      currLabel: 'Help Links'
      customIcon: null
      enabled: true
      handle: help-links
      icon: /Users/fritzk/code/safs-effs/vendor/adigital/help-links/src/icon-mask.svg
      layoutId: '1'
      newWindow: false
      order: 7
      prevLabel: 'Help Links'
      prevUrl: help-links
      type: ''
      url: help-links

engram-design · 2020-03-16T07:23:01Z

Should be fixed in 3.0.8

engram-design closed this as completed Feb 11, 2020

engram-design reopened this Feb 15, 2020

engram-design closed this as completed Mar 16, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Constraint violation #66

Constraint violation #66

Geestig commented Feb 6, 2020

fthues commented Feb 6, 2020 •

edited

Loading

engram-design commented Feb 11, 2020

fthues commented Feb 13, 2020

engram-design commented Feb 13, 2020

fthues commented Feb 13, 2020

engram-design commented Feb 15, 2020

engram-design commented Feb 15, 2020

fthues commented Feb 24, 2020

fthues commented Feb 25, 2020 •

edited

Loading

engram-design commented Feb 25, 2020

engram-design commented Feb 25, 2020

fthues commented Feb 26, 2020

engram-design commented Feb 26, 2020

engram-design commented Feb 27, 2020

KatieMFritz commented Mar 13, 2020

KatieMFritz commented Mar 13, 2020

engram-design commented Mar 16, 2020

Constraint violation #66

Constraint violation #66

Comments

Geestig commented Feb 6, 2020

Description

Steps to reproduce

Additional info

fthues commented Feb 6, 2020 • edited Loading

engram-design commented Feb 11, 2020

fthues commented Feb 13, 2020

engram-design commented Feb 13, 2020

fthues commented Feb 13, 2020

engram-design commented Feb 15, 2020

engram-design commented Feb 15, 2020

fthues commented Feb 24, 2020

fthues commented Feb 25, 2020 • edited Loading

engram-design commented Feb 25, 2020

engram-design commented Feb 25, 2020

fthues commented Feb 26, 2020

engram-design commented Feb 26, 2020

engram-design commented Feb 27, 2020

KatieMFritz commented Mar 13, 2020

KatieMFritz commented Mar 13, 2020

engram-design commented Mar 16, 2020

fthues commented Feb 6, 2020 •

edited

Loading

fthues commented Feb 25, 2020 •

edited

Loading