[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.

[kbergha]

I had the same issue when trying to update 5.2.1 to 5.2.4.1, and had a conversation with support about this.

Some excerpts from that conversation:

We did an update from 5.2.1 to 5.2.4.1 in our pre-production environment, and I'm getting this execption when trying to apply updates:

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

It looks like Craft has lost track of the previously applied migrations, but only in the track "craft", and is trying to reapply all migrations.
This is failing on the first migration, "m221101_115859_create_entries_authors_table", because "authorId" already has been dropped from the "entries" table.

Getting the history:

craft migrate/history all --track craft
No migration has been done before.

mysql> select * from migrations where track = 'craft' \G;
Empty set (0.17 sec)

It has not lost the migrations from plugins:

mysql> select * from migrations where track = 'plugin:blitz' \G;
*************************** 1. row ***************************
         id: 3
      track: plugin:blitz
       name: Install
  applyTime: 2024-06-18 08:34:49
dateCreated: 2024-06-18 08:34:49
dateUpdated: 2024-06-18 08:34:49
        uid: 5b8759f1-0485-4603-9015-807ebf99d052
*************************** 2. row ***************************
...
...

---

It was not an issue in my local dev environment, and I ended up doing a restore of the migrations table from a backup.