Column not found: 1054 Unknown column 'siteId' in 'field list'

[jmcgaha]

Description

I updated a site from Craft2 to 3 and installed FeedMe which we were using on v2. However the only page that will load is the feedme -> settings -> General tab. Every other one gives me a server error that will be pasted below.

2018-05-23 13:46:19 [::1][1][faabea9f873e0bb4185f7cfc6475e51c][error][yii\db\Exception] PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'siteId' in 'field list' in /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Command.php:1258 Stack trace:

0 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Command.php(1258): PDOStatement->execute()

1 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT id, `n...')

2 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)

3 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()

4 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/ActiveQuery.php(133): yii\db\Query->all(NULL)

5 /Users/username/Sites/company/craft/vendor/verbb/feed-me/src/services/Feeds.php(31): yii\db\ActiveQuery->all()

6 /Users/username/Sites/company/craft/vendor/verbb/feed-me/src/controllers/FeedsController.php(28): verbb\feedme\services\Feeds->getFeeds()

7 [internal function]: verbb\feedme\controllers\FeedsController->actionFeedsIndex()

8 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)

9 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)

10 /Users/username/Sites/company/craft/vendor/craftcms/cms/src/web/Controller.php(103): yii\base\Controller->runAction('feeds-index', Array)

11 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('feeds-index', Array)

12 /Users/username/Sites/company/craft/vendor/craftcms/cms/src/web/Application.php(273): yii\base\Module->runAction('feed-me/feeds/f...', Array)

13 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('feed-me/feeds/f...', Array)

14 /Users/username/Sites/company/craft/vendor/craftcms/cms/src/web/Application.php(262): yii\web\Application->handleRequest(Object(craft\web\Request))

15 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))

16 /Users/username/Sites/company/craft/html/index.php(21): yii\base\Application->run()

17 {main}

Next yii\db\Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'siteId' in 'field list' The SQL being executed was: SELECT id, name, feedUrl, feedType, primaryElement, elementType, elementGroup, siteId, duplicateHandle, fieldMapping, fieldUnique, passkey, backup, dateCreated, dateUpdated, uid FROM craft_feedme_feeds in /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Schema.php:664 Stack trace:

0 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'SELECT id, `n...')

1 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT id, `n...')

2 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)

3 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/Query.php(237): yii\db\Command->queryAll()

4 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/db/ActiveQuery.php(133): yii\db\Query->all(NULL)

5 /Users/username/Sites/company/craft/vendor/verbb/feed-me/src/services/Feeds.php(31): yii\db\ActiveQuery->all()

6 /Users/username/Sites/company/craft/vendor/verbb/feed-me/src/controllers/FeedsController.php(28): verbb\feedme\services\Feeds->getFeeds()

7 [internal function]: verbb\feedme\controllers\FeedsController->actionFeedsIndex()

8 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)

9 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)

10 /Users/username/Sites/company/craft/vendor/craftcms/cms/src/web/Controller.php(103): yii\base\Controller->runAction('feeds-index', Array)

11 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('feeds-index', Array)

12 /Users/username/Sites/company/craft/vendor/craftcms/cms/src/web/Application.php(273): yii\base\Module->runAction('feed-me/feeds/f...', Array)

13 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('feed-me/feeds/f...', Array)

14 /Users/username/Sites/company/craft/vendor/craftcms/cms/src/web/Application.php(262): yii\web\Application->handleRequest(Object(craft\web\Request))

15 /Users/username/Sites/company/craft/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))

16 /Users/username/Sites/company/craft/html/index.php(21): yii\base\Application->run()

17 {main}

Additional Information: Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'siteId' in 'field list' )

2018-05-23 13:46:19 [::1][1][faabea9f873e0bb4185f7cfc6475e51c][info][application] $_GET = [ 'p' => 'admin/feed-me/feeds' ]

Additional info

• Plugin version: occurred on Beta 13 and 14
• Craft version: 3.0.8 and 3.0.9

[engram-design]

The native Craft migration should replace all instanced of columns that related to locales or localeId with sites/siteId. It seems this migration hasn't been done, or there is some other issue here. I've seen this happen when foreign keys weren't setup correct in the first place (or otherwise not allowed by the database). Not sure if that's your specific issue here.

Otherwise, the solution would be to backup your database table, uninstall and reinstall Feed Me to get those tables working.

You could also manually rename the column yourself from locale to siteId.

[bstein-clever]

I just ran into this as well - also during an upgrade. After renaming the column, I get a different error: Column not found: 1054 Unknown column 'paginationNode' What's the fix for this one? I have several feeds, and I don't really want to recreate them all. I'd love it if there were some way of properly migrating my data. Any suggestions?

[engram-design]

@bstein-clever It sounds like a migration hasn't run, as this was added back in 3.0.0-beta.27. I'm not sure why this would've happened.

Rather than un-installing the plugin and losing all your feeds (which would fix this), its probably easies to manually create the column. Create a TEXT column called paginationNode in your feedme_feeds database table.

[bstein-clever]

It's a month later, so I don't remember exactly what I tried. I think I added the columns, and ran into other errors - the sync didn't recognize the column transformations, or something.

After a few days of not hearing back, I gave up and just converted them manually. I agree that I'm not clear why the migration wasn't run - all I did was follow the Craft guide to upgrade to Craft3, and then installed Feedme directly from the plugin store.

It's not entirely clear to me when in that flow the "upgrade" would get triggered.

[engram-design]

The migration should be triggered any time the schema version of Feed Me is changed, which in this case happened. Regardless, I'll look into what the issue might be.

[jamesmacwhite]

Just throwing in my experience with this as well.

Craft version: 3.1.16

1. Coming from Craft 2, Craft 2 to 3 DB migration ran successfully.
2. Install 3.x FeedMe plugin with composer and enable plugin ./craft install/plugin feed-me
3. Proceed to admin/feed-me/feeds and get the above SQL error on siteId.

Confirmed all migrations had been run with ./craft migrate/all

I did recently switch the dependency in Composer from feed-me-pro to feed-me because of the native edition support in 3.1.14, but I don't think that's related. I'm assuming it's down to the existing craft_feedme_feeds table from Craft 2.

I can see there is a migration for the locale -> siteId field in the table, but it doesn't look like it ever ran.

To manually resolve the issues.

Rename field locale to siteId

ALTER TABLE craft_feedme_feeds
CHANGE COLUMN `locale` `siteId` VARCHAR(255);

Add missing paginationNode column

ALTER TABLE craft_feedme_feeds
ADD `paginationNode` TEXT;

It seems the migration actions in the migrations below, don't seem to get run:

https://github.com/verbb/feed-me/blob/craft-3/src/migrations/m180305_000000_migrate_feeds.php#L69-L72

https://github.com/verbb/feed-me/blob/craft-3/src/migrations/m181113_000000_add_paginationNode.php

[engram-design]

@jamesmacwhite Thanks for your input here. Very odd how those migrations weren't run. You didn't get any errors in your migrations from Craft 2 > 3?

I wonder if it might be due to the new schema version number being lower than the C2 version of Feed Me - which is an oversight on my part for sure. I've adjusted it just in case it is this issue, for future upgrades.

Is there an entry in your craft_migrations table for those migrations by the way? That'll tell us if they really did run.

[jamesmacwhite]

@engram-design No errors from migrating to Craft 2 -> 3 with first jump at the CMS level and no errors when installing Feed Me 3.x. What I could do is test out a fresh migration with a dump of the original Craft 2 database I have, with the bumped schema version to see if this is triggered now. I can only think this might be the case, because it's weird seeing the migration code in the plugin, yet not being ran at all.

I checked the craft_migrations table and did a lookup on the name column. I did not find any reference to Feed Me 3.x migrations, only stuff from Feed Me 2.x. So I'm pretty sure no Craft 3 based migrations have ever occurred in this case.

I'll report back when I've tested a Craft 2 -> 3 migration with bare install to see if there's any differences now.

[jamesmacwhite]

Boom. Good catch, looks like it's been the schema version being lower the entire time. As a dirty quick test I just modified the schema version directly within the vendor folder to trigger Craft to start the migration process, upon doing that, the migrations have been run, for the first time as they are present in the craft_migrations table now.

442 75  plugin  m180305_000000_migrate_feeds    09/03/2019 00:03    09/03/2019 00:03    09/03/2019 00:03    73116c26-ea3c-40fb-a076-57f84395e00d
443 75  plugin  m181113_000000_add_paginationNode   09/03/2019 00:03    09/03/2019 00:03    09/03/2019 00:03    b2a2c4f9-ec38-4a1b-ba49-fc8952e411b9
444 75  plugin  m190201_000000_update_asset_feeds   09/03/2019 00:03    09/03/2019 00:03    09/03/2019 00:03    4874b791-fdcd-4702-8a11-8ef47bc27bb2

A new release with the schema version bump fix should migrate Craft 2 DB table data correctly now by the looks of it.

[engram-design]

@jamesmacwhite Thanks so much for testing this! I'm not sure if something changes along with way with Craft, but in the past it was a simple check against current and new schema values, and if they weren't the same. Looks like that must've changed to be proper version-checking.

So, that's just bad form on my part. Thanks again!

[jamesmacwhite]

@engram-design No problem! Thanks for sticking with us on this!

I think you're right, Craft upstream must have changed the handling of schema versions. I was also under impression that any schema version change triggered a migration, but perhaps the migration code uses version_compare() on values now, so the value does need to be greater than a previous schema version in the DB. As the Craft 2 schema version was 2.0.5 prior to fix, this would seem to confirm this behaviour.