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

sjenset · 2019-01-18T10:15:51Z

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"
	},

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

sjenset · 2019-01-18T10:19:11Z

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)

michtio · 2019-01-18T10:57:31Z

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

sjenset · 2019-01-18T11:02:35Z

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

carlcs · 2019-01-18T13:27:06Z

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

justin-masse · 2019-01-18T13:47:21Z

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"
}

sjenset · 2019-01-18T13:48:11Z

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.

justin-masse · 2019-01-18T14:30:21Z

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.

boldenamsterdam · 2019-01-18T16:30:56Z

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]

timkelty · 2019-01-18T18:04:58Z

Same error here…

BrandonJamesBorders · 2019-01-18T21:27:29Z

+1 Experiencing this as well.

brandonkelly · 2019-01-18T22:26:39Z

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 · 2019-01-19T02:12:01Z

3.1.2 is out with the fix now.

mofman · 2019-04-05T08:53:31Z

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

Is there a work around?

sjenset · 2019-04-05T09:31:17Z

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

brandonkelly · 2019-04-05T22:20:35Z

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

mofman · 2019-04-16T15:22:55Z

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

fixes #3663 (again)

brandonkelly · 2019-04-16T20:21:38Z

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 closed this as completed in 668f819 Jan 18, 2019

brandonkelly added a commit that referenced this issue Apr 16, 2019

Fixed SQL error when updating to 3.1

147464d

fixes #3663 (again)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

sjenset commented Jan 18, 2019

sjenset commented Jan 18, 2019

michtio commented Jan 18, 2019

sjenset commented Jan 18, 2019

carlcs commented Jan 18, 2019

justin-masse commented Jan 18, 2019 •

edited

Loading

sjenset commented Jan 18, 2019

justin-masse commented Jan 18, 2019 •

edited

Loading

boldenamsterdam commented Jan 18, 2019

timkelty commented Jan 18, 2019

BrandonJamesBorders commented Jan 18, 2019

brandonkelly commented Jan 18, 2019

brandonkelly commented Jan 19, 2019

mofman commented Apr 5, 2019

sjenset commented Apr 5, 2019

brandonkelly commented Apr 5, 2019

mofman commented Apr 16, 2019 •

edited

Loading

brandonkelly commented Apr 16, 2019

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

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

Comments

sjenset commented Jan 18, 2019

Description

Steps to reproduce

Additional info

sjenset commented Jan 18, 2019

michtio commented Jan 18, 2019

sjenset commented Jan 18, 2019

carlcs commented Jan 18, 2019

justin-masse commented Jan 18, 2019 • edited Loading

sjenset commented Jan 18, 2019

justin-masse commented Jan 18, 2019 • edited Loading

boldenamsterdam commented Jan 18, 2019

timkelty commented Jan 18, 2019

BrandonJamesBorders commented Jan 18, 2019

brandonkelly commented Jan 18, 2019

brandonkelly commented Jan 19, 2019

mofman commented Apr 5, 2019

sjenset commented Apr 5, 2019

brandonkelly commented Apr 5, 2019

mofman commented Apr 16, 2019 • edited Loading

brandonkelly commented Apr 16, 2019

justin-masse commented Jan 18, 2019 •

edited

Loading

justin-masse commented Jan 18, 2019 •

edited

Loading

mofman commented Apr 16, 2019 •

edited

Loading