Cms: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'fields.searchable' in 'field list'

Created on 18 Jan 2019 · 17Comments · Source: craftcms/cms

Description

After running a composer update I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'fields.searchable' in 'field list' The SQL being executed was: SELECT `fields`.`id`, `fields`.`dateCreated`, `fields`.`dateUpdated`, `fields`.`groupId`, `fields`.`name`, `fields`.`handle`, `fields`.`context`, `fields`.`instructions`, `fields`.`searchable`, `fields`.`translationMethod`, `fields`.`translationKeyFormat`, `fields`.`type`, `fields`.`settings`, `fields`.`uid`, `flf`.`layoutId`, `flf`.`tabId`, `flf`.`required`, `flf`.`sortOrder` FROM `fields` `fields` INNER JOIN `fieldlayoutfields` `flf` ON `flf`.`fieldId` = `fields`.`id` INNER JOIN `fieldlayouttabs` `flt` ON `flt`.`id` = `flf`.`tabId` WHERE `flf`.`layoutId`=1 ORDER BY `flt`.`sortOrder`, `flf`.`sortOrder` Error Info: Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'fields.searchable' in 'field list' )

If you need any files, stack-traces etc etc just say so - I'll be monitoring this issue for the next 5 hours or so :)

Steps to reproduce

Update to Craft 3.1.1
Attempt to access the admin-interface

Additional info

Craft version: 3.1.1
PHP version: 7.0.24
Database driver & version:
mariadb-server-5.5.52-1.el7.x86_64 mariadb-5.5.52-1.el7.x86_64 mariadb-libs-5.5.52-1.el7.x86_64
Plugins & versions:
"require" : { "craftcms/cms" : "^3.0.0", "vlucas/phpdotenv" : "^2.4.0", "mmikkel/child-me" : "^1.0", "charliedev/element-map" : "^1.0", "sebastianlenz/linkfield" : "^1.0", "verbb/expanded-singles" : "^1.0", "craftcms/redactor" : "^2.0", "ether/simplemap" : "^3.3", "tibemolde/ncb": "^1.0", "nystudio107/craft-imageoptimize": "^1.4", "nystudio107/craft-seomatic": "^3.0", "nystudio107/craft-typogrify": "^1.1", "solspace/craft3-calendar": "^2.0", "drewm/mailchimp-api": "^2.5", "tibemolde/embedder": "^1.0", "mmikkel/incognito-field": "^1.0", "mandrill/mandrill": "^1.0", "tibemolde/tibe-gdpr": "^1.0" },

Source

sjenset

👍1

Most helpful comment

Fixed the fields.searchable SQL error for the next release.

@boldenamsterdam The initial_yml_and_snapshot migration wasn’t written with the allowAdminChanges config setting in mind. I would recommend that you don’t set that to false on any environments until _after_ they’ve been updated to Craft 3.1.

brandonkelly on 18 Jan 2019

❤1 👍1

All 17 comments

Rolled back the update, here are the affected packages:

symfony/yaml (v3.3.6) yii2tech/ar-softdelete (1.0.2) craftcms/cms (3.1.1 => 3.0.36) twig/twig (v2.6.2 => v2.6.0) justinrainbow/json-schema (5.2.8 => 5.2.7) craftcms/redactor (2.2.1 => 2.1.7) rlanvin/php-rrule (v1.6.3 => v1.6.2)

sjenset on 18 Jan 2019

I'm running into the same issue after an update.

These are my craft packages:

"require": {
    "craftcms/cms": "^3.0.0",
    "vlucas/phpdotenv": "^2.4.0",
    "nystudio107/craft-seomatic": "^3.1",
    "craftcms/redactor": "^2.1",
    "nystudio107/craft-minify": "^1.2",
    "verbb/navigation": "^1.0",
    "topshelfcraft/wordsmith": "^3.0",
    "enupal/translate": "^1.1",
    "solspace/craft3-freeform": "^2.2",
    "solspace/craft3-freeform-pro": "^2.2",
    "nystudio107/craft-cookies": "^1.1",
    "clubstudioltd/craft-asset-rev": "^6.0",
    "dolphiq/redirect": "^1.0",
    "pennebaker/craft-architect": "^2.2",
    "doublesecretagency/craft-siteswitcher": "^2.0",
    "craftcms/store-hours": "^2.1",
    "putyourlightson/craft-entry-count": "^2.0",
    "aelvan/craft-cp-element-count": "^1.0",
    "studioespresso/craft-seeder": "^2.1",
    "dasmedia/craft3-breadcrumbs": "^1.0",
    "charliedev/section-field": "^1.0.1",
    "nystudio107/craft-eagerbeaver": "^1.0.0",
    "nystudio107/craft-imageoptimize": "^1.3.2",
    "nystudio107/craft-typogrify": "^1.1.1",
    "ostark/craft-async-queue": "^1.3.0",
    "rias/craft-colour-swatches": "^1.1.2",
    "rias/craft-position-fieldtype": "^1.0.13",
    "rias/craft-width-fieldtype": "^1.0.3",
    "sebastianlenz/linkfield": "^1.0.8",
    "verbb/expanded-singles": "^1.0.3",
    "yiisoft/yii2-redis": "^2.0.6"
  },

after rollback to a working version again these are the affected packages:

Package operations: 0 installs, 2 updates, 1 removal
  - Removing yii2tech/ar-softdelete (1.0.2)
  - Downgrading craftcms/cms (3.1.1 => 3.0.37): Loading from cache
  - Downgrading craftcms/redactor (2.2.1 => 2.1.7): Loading from cache
Writing lock file

michtio on 18 Jan 2019

I tried disabling SEOMatic before applying the update. The result is a slightly different error-message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dateDeleted' in 'where clause' The SQL being executed was: SELECT * FROM `entrytypes` WHERE `dateDeleted` IS NULL

sjenset on 18 Jan 2019

I think this can happen when you don’t revert the db completely after failing 3.1 migrations.

carlcs on 18 Jan 2019

I had the same issue when updating to Craft 3.1.1 last night from 3.0.30.2. It took down all of CraftCMS for our website.

"require": {
"craftcms/cms": "^3.0.4",
"twig/extensions": "^1.5",
"aelvan/inlin": "^2.0",
"aelvan/imager": "^2.0",
"nystudio107/craft-minify": "^1.2",
"verbb/image-resizer": "^2.0",
"craftcms/aws-s3": "^1.0",
"craftcms/redactor": "^2.0.1",
"craftcms/element-api": "^2.5.2",
"mikestecker/craft-videoembedder": "^1.0",
"dolphiq/sitemap": "^1.0",
"fruitstudios/linkit": "^1.0",
"nystudio107/craft-seomatic": "^3.0.3",
"ether/seo": "^3.2",
"verbb/cp-nav": "^2.0",
"firstborn/migrationmanager": "^3.0",
"rias/craft-password-policy": "^1.0.2",
"craftcms/query": "^2.0"
}

jam2z9 on 18 Jan 2019

I think this can happen when you don’t revert the db completely after failing 3.1 migrations.

Quite possible - for me the error occurred on the initial visit to /admin after running composer update, so I dunno if there were any migrations that had failed already.

However I have found out that manually disabling all installed plugins, running composer update, going to /admin to let it "do it's stuff" and then manually re-enabling all plugins allows me to upgrade to 3.1.

sjenset on 18 Jan 2019

I think this can happen when you don’t revert the db completely after failing 3.1 migrations.

This error you mean? I ran a straight migration through my CI pipeline where the project is rebuilt from scratch via composer each time. The DB persists on an Amazon RDS instance but when I deployed the code with the new version of CraftCMS I ran into the aforementioned error. All Craft pages are now returning the same error and I am unable to even reach the CP.

I can SSH into the server and fix this with a workaround, but that won't fly when this pipeline reaches prod of course.

Edit - I do not think any migrations were run because I was unable to even reach the CP to run them.

jam2z9 on 18 Jan 2019

We are also facing this issue with the same error message.

After some tests, we think it has to do with the new general config setting:

allowAdminChanges

The moment this config is set to false on the environment where you are trying to update the Craft to 3.1.1, it gives the error: SQLSTATE[42S22]

boldenamsterdam on 18 Jan 2019

Same error here…

timkelty on 18 Jan 2019

+1 Experiencing this as well.

BrandonJamesBorders on 18 Jan 2019

Fixed the fields.searchable SQL error for the next release.

brandonkelly on 18 Jan 2019

❤1 👍1

3.1.2 is out with the fix now.

brandonkelly on 19 Jan 2019

❤1

I'm getting this error after updating from craftcms/cms (3.0.36 => 3.1.21.1)

Is there a work around?

mofman on 5 Apr 2019

However I have found out that manually disabling all installed plugins, running composer update, going to /admin to let it "do it's stuff" and then manually re-enabling all plugins allows me to upgrade to 3.1.

@mofman

sjenset on 5 Apr 2019

@mofman can you find the error in storage/logs/web.log and post the stack trace that follows it?

brandonkelly on 6 Apr 2019

Hi Brandon, Sorry for the delay in getting this to you.

Next yii\db\Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dateDeleted' in 'where clause'
The SQL being executed was: SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`, `structureelements`.`structureId`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`, `structureelements`.`structureId`
FROM `craft_elements` `elements`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON `content`.`elementId` = `elements`.`id`
LEFT JOIN `craft_structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (EXISTS (SELECT *
FROM `craft_structures`
WHERE (`id` = `structureelements`.`structureId`) AND (`dateDeleted` IS NULL)))
WHERE (`elements_sites`.`siteId`=1) AND (`content`.`siteId`=1) AND (`elements`.`id`=2) AND (`elements`.`archived`=FALSE)
ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC
LIMIT 1) `subquery`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId` = `subquery`.`structureId`)
ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC in /Sites/c3computers/vendor/yiisoft/yii2/db/Schema.php:664

#0 /Sites/c3computers/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'SELECT `element...')
#1 /Sites/c3computers/vendor/yiisoft/yii2/db/Command.php(1158): yii\db\Command->internalExecute('SELECT `element...')
#2 /Sites/c3computers/vendor/yiisoft/yii2/db/Command.php(413): yii\db\Command->queryInternal('fetch', NULL)
#3 /Sites/c3computers/vendor/yiisoft/yii2/db/Query.php(274): yii\db\Command->queryOne()
#4 /Sites/c3computers/vendor/craftcms/cms/src/db/Query.php(177): yii\db\Query->one(NULL)
#5 /Sites/c3computers/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1215): craft\db\Query->one(NULL)
#6 /Sites/c3computers/vendor/craftcms/cms/src/services/Elements.php(215): craft\elements\db\ElementQuery->one()
#7 /Sites/c3computers/vendor/craftcms/cms/src/services/Elements.php(272): craft\services\Elements->getElementById(2, 'craft\\elements\\...', 1)
#8 /Sites/c3computers/vendor/nystudio107/craft-seomatic/src/services/MetaContainers.php(801): craft\services\Elements->getElementByUri('__home__', 1, false)
#9 /Sites/c3computers/vendor/nystudio107/craft-seomatic/src/services/MetaContainers.php(157): nystudio107\seomatic\services\MetaContainers->setMatchedElement('', '1')
#10 /Sites/c3computers/vendor/nystudio107/craft-seomatic/src/twigextensions/SeomaticTwigExtension.php(47): nystudio107\seomatic\services\MetaContainers->loadMetaContainers('', NULL)
#11 /Sites/c3computers/vendor/twig/twig/src/ExtensionSet.php(326): nystudio107\seomatic\twigextensions\SeomaticTwigExtension->getGlobals()
#12 /Sites/c3computers/vendor/twig/twig/src/Environment.php(939): Twig\ExtensionSet->getGlobals()
#13 /Sites/c3computers/vendor/twig/twig/src/Environment.php(959): Twig\Environment->getGlobals()
#14 /Sites/c3computers/vendor/twig/twig/src/Template.php(359): Twig\Environment->mergeGlobals(Array)
#15 /Sites/c3computers/vendor/craftcms/cms/src/web/twig/Template.php(34): Twig\Template->display(Array, Array)
#16 /Sites/c3computers/vendor/twig/twig/src/Template.php(367): craft\web\twig\Template->display(Array)
#17 /Sites/c3computers/vendor/twig/twig/src/TemplateWrapper.php(45): Twig\Template->render(Array, Array)
#18 /Sites/c3computers/vendor/twig/twig/src/Environment.php(318): Twig\TemplateWrapper->render(Array)
#19 /Sites/c3computers/vendor/craftcms/cms/src/web/View.php(343): Twig\Environment->render('_special/dbupda...', Array)
#20 /Sites/c3computers/vendor/craftcms/cms/src/web/View.php(393): craft\web\View->renderTemplate('_special/dbupda...', '[]')
#21 /Sites/c3computers/vendor/craftcms/cms/src/web/Controller.php(161): craft\web\View->renderPageTemplate('_special/dbupda...', '[]')
#22 /Sites/c3computers/vendor/craftcms/cms/src/controllers/TemplatesController.php(105): craft\web\Controller->renderTemplate('_special/dbupda...')
#23 [internal function]: craft\controllers\TemplatesController->actionManualUpdateNotification()
#24 /Sites/c3computers/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#25 /Sites/c3computers/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#26 /Sites/c3computers/vendor/craftcms/cms/src/web/Controller.php(109): yii\base\Controller->runAction('manual-update-n...', Array)
#27 /Sites/c3computers/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('manual-update-n...', Array)
#28 /Sites/c3computers/vendor/craftcms/cms/src/web/Application.php(297): yii\base\Module->runAction('templates/manua...', Array)
#29 /Sites/c3computers/vendor/craftcms/cms/src/web/Application.php(672): craft\web\Application->runAction('templates/manua...')
#30 /Sites/c3computers/vendor/craftcms/cms/src/web/Application.php(223): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#31 /Sites/c3computers/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#32 /Sites/c3computers/public/index.php(22): yii\base\Application->run()
#33 /Users/andrew/.composer/vendor/laravel/valet/server.php(151): require('/Sites/c3comput...')
#34 {main}

web.log

mofman on 16 Apr 2019

Thanks @mofman, just fixed that for the next release.

To get the fix early. change your craftcms/cms requirement in composer.json to:

"require": {
  "craftcms/cms": "dev-develop#147464d069dec9d23fa5f8045de6148f7470bbcd as 3.1.23",
  "...": "..."
}

Then run composer update.

brandonkelly on 16 Apr 2019

👍1

Was this page helpful?

0 / 5 - 0 ratings

Related issues

Unable to update to Craft 3.1 due to dependency conflict

lukebailey · 3Comments

Feature Request: Add button to open currently edited element in inline element editor in a new tab

Mosnar · 3Comments

Add 'default' option to the number field

angrybrad · 3Comments

[Feature Request] Ability to remove "All entries" from left panel of Entries page

benface · 3Comments

Single asset edit form pages

angrybrad · 3Comments