[5.x]: Database Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'authorId' in 'field list'

[alexanderbuergin]

What happened?

Description

When trying to upgrade Craft CMS from 5.1.5 to 5.2.5, the following error occurs:

Database Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'authorId' in 'field list'
The SQL being executed was: INSERT INTO `entries_authors` (`entryId`, `authorId`, `sortOrder`)
SELECT `id`, `authorId`, '1' FROM `entries`
WHERE `authorId` IS NOT NULL

Migration: craft\migrations\m221101_115859_create_entries_authors_table

Output:

    > dropping {{%entries_authors}} if it exists ... done (time: 0.023s)
    > create table {{%entries_authors}} ... done (time: 0.009s)
    > create index idx_phgcihakfuodfyotwtdbnoippcqnuihwbdme on {{%entries_authors}} (authorId) ... done (time: 0.016s)
    > create index idx_jlxfmiuclcfzpmwrxcpfocliarsjwlbdjkwl on {{%entries_authors}} (entryId,sortOrder) ... done (time: 0.022s)
    > add foreign key fk_kzmzrzbihrwnqhiqubnkfavjeymhbmugedlj: {{%entries_authors}} (entryId) references {{%entries}} (id) ... done (time: 0.029s)
    > add foreign key fk_qjlzsmfbqtvtstryxzysaunnkfzzyxictohs: {{%entries_authors}} (authorId) references {{%users}} (id) ... done (time: 0.023s)
    > execute SQL: INSERT INTO {{%entries_authors}} ([[entryId]], [[authorId]], [[sortOrder]])
SELECT [[id]], [[authorId]], '1' FROM {{%entries}}
WHERE [[authorId]] IS NOT NULL ...Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'authorId' in 'field list'
The SQL being executed was: INSERT INTO `entries_authors` (`entryId`, `authorId`, `sortOrder`)
SELECT `id`, `authorId`, '1' FROM `entries`
WHERE `authorId` IS NOT NULL (/home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `en...')
#1 /home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute('INSERT INTO `en...')
#2 /home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()
#3 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/migrations/m221101_115859_create_entries_authors_table.php(34): yii\db\Migration->execute('INSERT INTO {{%...')
#4 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/db/Migration.php(50): craft\migrations\m221101_115859_create_entries_authors_table->safeUp()
#5 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m221101_115859_create_entries_authors_table))
#7 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/services/Updates.php(245): craft\db\MigrationManager->up()
#8 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(499): craft\services\Updates->runMigrations(Array)
#9 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/controllers/UpdaterController.php(207): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#10 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#11 /home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#13 /home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#14 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/web/Application.php(349): yii\base\Module->runAction('updater/migrate', Array)
#15 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/web/Application.php(737): craft\web\Application->runAction('updater/migrate')
#16 /home/site/public_html/site/siteSystem/siteOs/vendor/craftcms/cms/src/web/Application.php(245): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#17 /home/site/public_html/site/siteSystem/siteOs/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /home/site/public_html/site/siteSystem/siteOs/web/index.php(23): yii\base\Application->run()
#19 {main}

Craft CMS version

5.1.5

PHP version

8.2

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

-

[brandonkelly]

That migration is supposed to be run as part of the initial Craft 5 upgrade. Did you manually adjust the database or remove rows from the migrations table?

[alexanderbuergin]

We did the migration from Craft cms 4 to 5 some time ago. The upgrade was to 5.0.x.

We did not delete any migrations from the DB.

[brandonkelly]

Do you have a database backup from after the initial Craft 5 upgrade? Is m221101_115859_create_entries_authors_table listed in the migrations table?

[alexanderbuergin]

No, we do not have such an entry (m221101_115859_create_entries_authors_table ) in the DB migrations table. I'm not sure why.

Can you just put this entry in the DB I suppose?

[brandonkelly]

The error you’re getting suggests that the migration has in fact been run before though, as the last thing the migration does is drop the authorId column from the entries table.

You could manually add the row, but I’d be worried about why it got removed in the first place, and whether other migrations’ rows are also missing.