Cms: Error from `craft utils/repair/section-structure` command

[michaelroper]

Hey team - I'm having structural issues similar to #6204 / #6222, where some entries seem to have been orphaned from the structure. They are showing in All Entries, but if I click on one of them it errors with "entry not found" (though i can actually still edit them from the HUD in another entry?).

So I tried to follow the advice in those other issues, to run the craft utils/repair/section-structure pages command, but am receiving this error:

```
➜ nitro craft utils/repair/section-structure pages
connecting to www-cms.test
Exception 'yii\db\Exception' with message 'SQLSTATE[42804]: Datatype mismatch: 7 ERROR: CASE types timestamp without time zone and integer cannot be matched
LINE 9: ...WHEN ("structureelements"."lft" IS NOT NULL) THEN 0 ELSE "el...
^
The SQL being executed was: SELECT "structureelements"."root", "structureelements"."lft", "structureelements"."rgt", "structureelements"."level", "elements"."id", "elements"."fieldLayoutId", "elements"."uid", "elements"."enabled", "elements"."archived", "elements"."dateCreated", "elements"."dateUpdated", "elements_sites"."id" AS "siteSettingsId", "elements_sites"."slug", "elements_sites"."siteId", "elements_sites"."uri", "elements_sites"."enabled" AS "enabledForSite", "entries"."sectionId", "entries"."typeId", "entries"."authorId", "entries"."postDate", "entries"."expiryDate", "content"."id" AS "contentId", "content"."title", "content"."field_copy", "content"."field_entryBuildStatus", "content"."field_entryDescription", "content"."field_entryIcon", "content"."field_entrySeo", "content"."field_entryTitle", "content"."field_heading", "content"."field_marketoForms", "content"."field_msdBrands", "content"."field_msdDescription", "content"."field_msdEndDate", "content"."field_msdStartDate", "content"."field_optimizedImages", "content"."field_table"
FROM (SELECT "elements"."id" AS "elementsId", "elements_sites"."id" AS "elementsSitesId", "content"."id" AS "contentId"
FROM "elements" "elements"
INNER JOIN "entries" "entries" ON "entries"."id" = "elements"."id"
INNER JOIN "elements_sites" "elements_sites" ON "elements_sites"."elementId" = "elements"."id"
INNER JOIN "content" "content" ON "content"."elementId" = "elements"."id"
LEFT JOIN "structureelements" "structureelements" ON ("structureelements"."elementId" = "elements"."id") AND ("structureelements"."structureId"=9)
WHERE ("entries"."sectionId"=10) AND ("elements"."archived"=FALSE) AND ("elements"."dateDeleted" IS NULL) AND ("elements"."draftId" IS NULL) AND ("elements"."revisionId" IS NULL)
ORDER BY CASE WHEN ("structureelements"."lft" IS NOT NULL) THEN 0 ELSE "elements"."dateCreated" END ASC, "structureelements"."lft") "subquery"
INNER JOIN "entries" "entries" ON "entries"."id" = "subquery"."elementsId"
INNER JOIN "elements" "elements" ON "elements"."id" = "subquery"."elementsId"
INNER JOIN "elements_sites" "elements_sites" ON "elements_sites"."id" = "subquery"."elementsSitesId"
INNER JOIN "content" "content" ON "content"."id" = "subquery"."contentId"
LEFT JOIN "structureelements" "structureelements" ON ("structureelements"."elementId" = "elements"."id") AND ("structureelements"."structureId"=9)
ORDER BY CASE WHEN ("structureelements"."lft" IS NOT NULL) THEN 0 ELSE "elements"."dateCreated" END ASC, "structureelements"."lft"'

in /app/vendor/yiisoft/yii2/db/Schema.php:678

Error Info:
Array
(
[0] => 42804
[1] => 7
[2] => ERROR: CASE types timestamp without time zone and integer cannot be matched
LINE 9: ...WHEN ("structureelements"."lft" IS NOT NULL) THEN 0 ELSE "el...
^
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[42804]: Datatype mismatch: 7 ERROR: CASE types timestamp without time zone and integer cannot be matched
LINE 9: ...WHEN ("structureelements"."lft" IS NOT NULL) THEN 0 ELSE "el...
^'

in /app/vendor/yiisoft/yii2/db/Command.php:1299

Stack trace:

0 /app/vendor/yiisoft/yii2/db/Command.php(1299): PDOStatement->execute()

1 /app/vendor/yiisoft/yii2/db/Command.php(1165): yii\db\Command->internalExecute('SELECT "structu...')

2 /app/vendor/yiisoft/yii2/db/Command.php(407): yii\db\Command->queryInternal('fetchAll', NULL)

3 /app/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll()

4 /app/vendor/craftcms/cms/src/db/Query.php(152): yii\db\Query->all(NULL)

5 /app/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1522): craft\db\Query->all(NULL)

6 /app/vendor/craftcms/cms/src/console/controllers/utils/RepairController.php(127): craft\elements\db\ElementQuery->all()

7 /app/vendor/craftcms/cms/src/console/controllers/utils/RepairController.php(68): craft\console\controllers\utils\RepairController->repairStructure(9, Object(craft\elements\db\EntryQuery))

8 [internal function]: craft\console\controllers\utils\RepairController->actionSectionStructure('products')

9 /app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)

10 /app/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array)

11 /app/vendor/yiisoft/yii2/console/Controller.php(184): yii\base\Controller->runAction('section-structu...', Array)

12 /app/vendor/craftcms/cms/src/console/Controller.php(192): yii\console\Controller->runAction('section-structu...', Array)

13 /app/vendor/yiisoft/yii2/base/Module.php(534): craft\console\Controller->runAction('section-structu...', Array)

14 /app/vendor/yiisoft/yii2/console/Application.php(181): yii\base\Module->runAction('utils/repair/se...', Array)

15 /app/vendor/craftcms/cms/src/console/Application.php(89): yii\console\Application->runAction('utils/repair/se...', Array)

16 /app/vendor/yiisoft/yii2/console/Application.php(148): craft\console\Application->runAction('utils/repair/se...', Array)

17 /app/vendor/yiisoft/yii2/base/Application.php(392): yii\console\Application->handleRequest(Object(craft\console\Request))

18 /app/craft(23): yii\base\Application->run()

19 {main}

Error: exit status 1```

Please help me return these orphans to their parents!

Additional info

• Craft version: 3.6.10
• PHP version: 7.4
• Database driver & version: postgres 13

[brandonkelly]

Thanks for reporting that. Just fixed for the next release.

To get the fix early, change your craftcms/cms requirement in composer.json to "dev-develop" and run composer update.

[michaelroper]

@brandonkelly when is that release likely to be, out of interest? I seem to get stuck trying to use the develop branch, due to other plugin dependency requirements:

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires percipioglobal/craft-colour-swatches 1.4.1.1 -> satisfiable by percipioglobal/craft-colour-swatches[1.4.1.1].
    - percipioglobal/craft-colour-swatches 1.4.1.1 requires craftcms/cms ^3.0.0 -> found craftcms/cms[3.0.0-beta.1, ..., 3.7.x-dev] but it conflicts with your root composer.json require (dev-develop).
  Problem 2
    - Root composer.json requires supercool/tablemaker dev-master -> satisfiable by supercool/tablemaker[dev-master].
    - supercool/tablemaker dev-master requires craftcms/cms ^3.0.0-RC1 -> found craftcms/cms[3.0.0-RC1, ..., 3.7.x-dev] but it conflicts with your root composer.json require (dev-develop).
  Problem 3
    - angellco/spoon[3.3.3, ..., 3.4.3] require craftcms/cms ^3.1.0 -> found craftcms/cms[3.1.0-beta.1, ..., 3.7.x-dev] but it conflicts with your root composer.json require (dev-develop).
    - angellco/spoon[3.5.0, ..., 3.5.3] require craftcms/cms ^3.4.0 -> found craftcms/cms[3.4.0-beta.1, ..., 3.7.x-dev] but it conflicts with your root composer.json require (dev-develop).
    - angellco/spoon[3.6.0, ..., 3.6.1] require craftcms/cms ^3.5.0 -> found craftcms/cms[3.5.0-beta.1, ..., 3.7.x-dev] but it conflicts with your root composer.json require (dev-develop).
    - Root composer.json requires angellco/spoon ^3.3.3 -> satisfiable by angellco/spoon[3.3.3, ..., 3.6.1].

Use the option --with-all-dependencies (-W) to allow upgrades, downgrades and removals for packages currently locked to specific versions.

I tried that last option too, didn't seem to make a difference..

[brandonkelly]

Sorry, try "dev-develop as 3.6.10". The next release is scheduled for next Tuesday.

[michaelroper]

Thanks, that worked, as did the repair structure command - appreciate your quick response+fix on that one!