Cms: SQLSTATE[42S22]: Column not found

Created on 5 Aug 2020 · 11Comments · Source: craftcms/cms

Description

I try to upgrade craft. With no luck.

Error:

Database Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'channel' in 'field list'
The SQL being executed was: INSERT INTO craft_queue (channel, job, description, timePushed, ttr, delay, priority) VALUES ('queue', 'O:34:\"craft\\queue\\jobs\\UpdateSearchIndex\":7:{s:11:\"elementType\";s:20:\"craft\\elements\\Entry\";s:9:\"elementId\";s:1:\"2\";s:6:\"siteId\";s:1:\"1\";s:12:\"fieldHandles\";a:0:{}s:11:\"description\";N;s:30:\"\0craft\\queue\\BaseJob\0_progress\";i:0;s:35:\"\0craft\\queue\\BaseJob\0_progressLabel\";N;}', 'Aktualisiere Suchindexe', 1596624483, 300, 0, 1024)

Migration: craft\migrations\m190913_152146_update_preview_targets

Output:

Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'channel' in 'field list'
The SQL being executed was: INSERT INTO craft_queue (channel, job, description, timePushed, ttr, delay, priority) VALUES ('queue', 'O:34:\"craft\\queue\\jobs\\UpdateSearchIndex\":7:{s:11:\"elementType\";s:20:\"craft\\elements\\Entry\";s:9:\"elementId\";s:1:\"2\";s:6:\"siteId\";s:1:\"1\";s:12:\"fieldHandles\";a:0:{}s:11:\"description\";N;s:30:\"\0craft\\queue\\BaseJob\0_progress\";i:0;s:35:\"\0craft\\queue\\BaseJob\0_progressLabel\";N;}', 'Aktualisiere Suchindexe', 1596624483, 300, 0, 1024) (/Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/db/Schema.php:674)
#0 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO cr...')
#1 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('INSERT INTO cr...')
#2 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/queue/Queue.php(536): yii\db\Command->execute()
#3 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2-queue/src/Queue.php(196): craft\queue\Queue->pushMessage('O:34:"craft\\que...', 300, 0, NULL)
#4 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/queue/Queue.php(173): yii\queue\Queue->push(Object(craft\queue\jobs\UpdateSearchIndex))
#5 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/Elements.php(2168): craft\queue\Queue->push(Object(craft\queue\jobs\UpdateSearchIndex))
#6 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/Elements.php(524): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, false, true)
#7 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/Sections.php(1545): craft\services\Elements->saveElement(Object(craft\elements\Entry))
#8 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/Sections.php(755): craft\services\Sections->_ensureSingleEntry(Object(craft\models\Section), Array)
#9 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/ProjectConfig.php(1083): craft\services\Sections->handleChangedSection(Object(craft\events\ConfigEvent))
#10 [internal function]: craft\services\ProjectConfig->handleChangeEvent(Object(craft\events\ConfigEvent))
#11 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/base/Component.php(627): call_user_func(Array, Object(craft\events\ConfigEvent))
#12 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/ProjectConfig.php(657): yii\base\Component->trigger('updateItem', Object(craft\events\ConfigEvent))
#13 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/ProjectConfig.php(1073): craft\services\ProjectConfig->processConfigChanges('sections.0030ab...')
#14 [internal function]: craft\services\ProjectConfig->handleChangeEvent(Object(craft\events\ConfigEvent))
#15 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/base/Component.php(627): call_user_func(Array, Object(craft\events\ConfigEvent))
#16 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/ProjectConfig.php(654): yii\base\Component->trigger('addItem', Object(craft\events\ConfigEvent))
#17 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/ProjectConfig.php(478): craft\services\ProjectConfig->processConfigChanges('sections.0030ab...', true, '')
#18 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/migrations/m190913_152146_update_preview_targets.php(54): craft\services\ProjectConfig->set('sections.0030ab...', Array)
#19 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/db/Migration.php(52): craft\migrations\m190913_152146_update_preview_targets->safeUp()
#20 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#21 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/db/MigrationManager.php(153): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m190913_152146_update_preview_targets))
#22 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/services/Updates.php(225): craft\db\MigrationManager->up()
#23 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(510): craft\services\Updates->runMigrations(Array)
#24 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#25 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#26 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#27 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#28 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/web/Controller.php(178): yii\base\Controller->runAction('migrate', Array)
#29 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('migrate', Array)
#30 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/web/Application.php(290): yii\base\Module->runAction('updater/migrate', Array)
#31 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/web/Application.php(645): craft\web\Application->runAction('updater/migrate')
#32 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/craftcms/cms/src/web/Application.php(222): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#33 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#34 /Users/atelierworkstation/Documents/git-local/cristina-spagnolo/www/index.php(21): yii\base\Application->run()
#35 {main}

Steps to reproduce

Update from Craft 3.2.9 to 3.4

Additional info

Craft version: 3.2.9
PHP version: 7.2.2
Database driver & version:
Plugins & versions:

bug

Source

kaspar-allenbach

All 11 comments

Can you please send your Composer files and a database backup into [email protected]? That should help us reproduce the error and figure out a fix.

brandonkelly on 5 Aug 2020

One day later, setup the installation new on my local env, and it works. I have absolutely no idea why.

kaspar-allenbach on 6 Aug 2020

Hm, weird. craft\queue\Queue::pushMessage() (where the error originated) will only include a channel key in the new row data if the channel column actually exists already:

https://github.com/craftcms/cms/blob/bd3820f19a0680c635a89c7f16805299964d97f2/src/queue/Queue.php#L542-L545

I suspect maybe this was a caching issue, where the column did exist previously (prior failed update attempt perhaps?) and the schema cache was saved with the column in there.

brandonkelly on 6 Aug 2020

I copied the LIVE db down a few times onto the local env. Maybe there was something wrong with the import.

You mean the db was cached? This goes beyond my knowledge.

kaspar-allenbach on 7 Aug 2020

Craft’s record of the DB _schema_ (the tables, columns, indexes, and foreign keys) gets cached. I just made a change for the next release, so that the schema cache will automatically get cleared before running each migration, so that should fix issues like this going forward.

brandonkelly on 8 Aug 2020

❤1

Craft 3.5.2 is out now with that change.

brandonkelly on 9 Aug 2020

🎉1

I'm currently seeing this issue, was craft 3.5.2 ever released, I can't see it in the releases (https://github.com/craftcms/cms/releases)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'content.field_facebook' in 'field list'

magicspon on 28 Nov 2020

Yes. Currently at 3.5.16.

brandonkelly on 29 Nov 2020

durp... numbers! my brain was reading 5.5.2 as more than 5.5.16

Somehow, i've managed to end up with duplicate fields..

if i delete one.

I get this:

Database Exception – yii\db\Exception
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'content.field_facebook' in 'field list'
The SQL being executed was: SELECT `users`.`password`, `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`, `users`.`username`, `users`.`firstName`, `users`.`lastName`, `users`.`email`, `users`.`unverifiedEmail`, `users`.`admin`, `users`.`locked`, `users`.`pending`, `users`.`suspended`, `users`.`lastLoginDate`, `users`.`lockoutDate`, `users`.`photoId`, `users`.`hasDashboard`, `content`.`id` AS `contentId`, `content`.`field_blurb`, `content`.`field_facebook`, `content`.`field_formHeading`, `content`.`field_formSubHeading`, `content`.`field_heading`, `content`.`field_instagram`, `content`.`field_key`, `content`.`field_pinterest`, `content`.`field_productId`, `content`.`field_productOptionsHeading`, `content`.`field_productOptionsIntro`, `content`.`field_productOptionsPosition`, `content`.`field_richText`, `content`.`field_route`, `content`.`field_seo`, `content`.`field_serviceHeading`, `content`.`field_serviceList`, `content`.`field_servicePosition`, `content`.`field_serviceSubHeading`, `content`.`field_serviceText`, `content`.`field_showcaseHeading`, `content`.`field_showcasePosition`, `content`.`field_subHeading`, `content`.`field_text`, `content`.`field_textList`, `content`.`field_theme`, `content`.`field_twitter`, `content`.`field_unit`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `users` `users` ON `users`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
WHERE (`elements`.`id`=1) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `users`.`username`
LIMIT 1) `subquery`
INNER JOIN `users` `users` ON `users`.`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`
ORDER BY `users`.`username`
Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'content.field_facebook' in 'field list'
)
↵
Caused by: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'content.field_facebook' in 'field list'
in /home/vagrant/dev/clients/oomph/server/vendor/yiisoft/yii2/db/Command.php at line 1293

which i can't recover from... eeee!
any thoughts!?

magicspon on 29 Nov 2020

Not sure how you ended up with duplicate fields (maybe your project config defined two fields with the same handle after a bad Git merge or something), but it’s not possible to have two database columns with the same name, so if you deleted one of them from within the control panel, that would have deleted their shared content table column.

Do you have a database backup from before you deleted the field? If so, restore it, and then write into [email protected] and we can help look into the duplicate field issue.

brandonkelly on 29 Nov 2020

Sorted:

You can fix this by running those three queries on your DB.
Remove the duplicate fields:

DELETE FROM fields WHERE id IN (69,70,71,72)
Then clear the duplicate group:

DELETE FROM fieldgroups WHERE id=8
Finally, clear the old columns:

ALTER TABLE content
DROP field_facebook_old,
DROP field_twitter_old,
DROP field_pinterest_old,
DROP field_instagram_old;