Database Exception: SQLSTATE[42S21]: Column already exists

[giampo1991]

Bug Description

We have a problem with the update fom NEO 3.9.11 to Neo 4.0.2. Every time we try to update we get the following error:

Database Exception: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'iconId'
The SQL being executed was: ALTER TABLE tsl_neoblocktypes ADD iconId int(11) AFTER description

Migration: benf\neo\migrations\m221231_110307_add_block_type_icon_property

Output:

add column iconId integer AFTER description to table {{%neoblocktypes}} ...Exception: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'iconId'
The SQL being executed was: ALTER TABLE tsl_neoblocktypes ADD iconId int(11) AFTER description (/Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE ts...')
#1 /Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('ALTER TABLE ts...')
#2 /Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/db/Migration.php(376): yii\db\Command->execute()
#3 /Users//websites/teslatel2/web/app/vendor/spicyweb/craft-neo/src/migrations/m221231_110307_add_block_type_icon_property.php(17): yii\db\Migration->addColumn('{{%neoblocktype...', 'iconId', Object(craft\db\mysql\ColumnSchemaBuilder))
#4 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/db/Migration.php(49): benf\neo\migrations\m221231_110307_add_block_type_icon_property->safeUp()
#5 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(benf\neo\migrations\m221231_110307_add_block_type_icon_property))
#7 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/services/Updates.php(252): craft\db\MigrationManager->up()
#8 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(493): craft\services\Updates->runMigrations(Array)
#9 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#10 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#11 /Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#13 /Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#14 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/web/Application.php(305): yii\base\Module->runAction('updater/migrate', Array)
#15 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/web/Application.php(693): craft\web\Application->runAction('updater/migrate')
#16 /Users//websites/teslatel2/web/app/vendor/craftcms/cms/src/web/Application.php(262): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#17 /Users//websites/teslatel2/web/app/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /Users/******/websites/teslatel2/web/index.php(10): yii\base\Application->run()
#19 {main}

We are unable to update our system. Have you got any suggestion?

Steps to reproduce

We have no instructions for reproduction, we only use NEO on this site. We try to update via composer and also via backend but when the system tries to update db it returns the described error

Expected behaviour

No response

Neo version

3.9.11

Craft CMS version

4.6.0

What is the affected Neo field's propagation method?

No response

Does this issue involve templating, and if so, is eager-loading used?

This is not a templating issue

[ttempleton]

The iconId column was added in 3.6.0, so it's unrelated to the Neo 4 update and I'm not really sure why the error hasn't happened earlier. From what I can find (specifically craftcms/cms#8079 (comment) and craftcms/cms#6593), this happens when an update has failed and the database wasn't restored before retrying the update. In this specific case, there are three options you could take:

• If none of your block types have an iconId set, you could manually remove the column and then run the update.
• If the iconId column and the associated foreign key both exist, you could:
  • remove lines 17 and 18 of vendor/spicyweb/craft-neo/src/migrations/m221231_110307_add_block_type_icon_property.php and then run the update.
  • or add the missing row to your migrations table, with the name column set to m221231_110307_add_block_type_icon_property and the track column set to plugin:neo.