[4.x]: Upgrading from 4.3.4 to 4.5.5 and getting table already exists

[moldedjelly]

What happened?

Description

I'm upgrading a project from 4.3.4 to 4.5.5. The deployment is using a terraform controlled docker based install.

Steps to reproduce

1. When we deploy to the staging setup to check it's all working, when trying to login into the admin, it's suggesting cleanup needs to happen, but when clicking "Finish Up", an SQL error is presented.

Expected behavior

Cleanup finishes without any problems.

Actual behavior

SQL error is reported:

> create table {{%elementactivity}} ...Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'elementactivity' already exists
The SQL being executed was: CREATE TABLE elementactivity (
elementId int(11) NOT NULL,
userId int(11) NOT NULL,
siteId int(11) NOT NULL,
draftId int(11) NULL DEFAULT NULL,
type varchar(255) NOT NULL,
timestamp datetime(0),
PRIMARY KEY(elementId, userId, type)
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8 (/app/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /app/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE ...')
#1 /app/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('CREATE TABLE p...')
#2 /app/vendor/yiisoft/yii2/db/Migration.php(322): yii\db\Command->execute()
#3 /app/vendor/craftcms/cms/src/migrations/m230710_162700_element_activity.php(25): yii\db\Migration->createTable('{{%elementactiv...', Array)
#4 /app/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m230710_162700_element_activity->safeUp()
#5 /app/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /app/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m230710_162700_element_activity))
#7 /app/vendor/craftcms/cms/src/services/Updates.php(245): craft\db\MigrationManager->up()
#8 /app/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(493): craft\services\Updates->runMigrations(Array)
#9 /app/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#10 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#11 /app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /app/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#13 /app/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#14 /app/vendor/craftcms/cms/src/web/Application.php(304): yii\base\Module->runAction('updater/migrate', Array)
#15 /app/vendor/craftcms/cms/src/web/Application.php(694): craft\web\Application->runAction('updater/migrate')
#16 /app/vendor/craftcms/cms/src/web/Application.php(241): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#17 /app/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /app/web/index.php(12): yii\base\Application->run()
#19 {main}

If a migration has been run before for the database - would successive deployments try to run the migrations again. Should the migrations be designed with "create table if not exists ..."?

Craft CMS version

4.5.5

PHP version

8.0.29

Operating system and version

docker - craftcms/nginx:8.0

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

No response

[i-just]

Hi, thanks for reaching out. In general, if migration fails or you want to test the deployment process again, you should restore the DB backup from before the update. However, that won’t remove any new tables that were added by the migration. I just raised a PR to adjust this migration.

[moldedjelly]

@i-just
I managed to write a "sed" command into our build process so that it modified that migration - adding the dropTableIfExists as you wrote in the PR. Unfortunately, we then encountered the following error. I'm not sure if it's having all these problems because something is wrong with our DB, but I've managed to get a clean backup and will be trying with that. Just wanted to put this here, in case that "dropTableIfExists" might not be the correct fix...

> dropping {{%elementactivity}} if it exists ... done (time: 0.068s)
    > create table {{%elementactivity}} ... done (time: 0.054s)
    > create index xxxxx_idx_ifjjdovezbftclfcvyqaxdbhgconovlbuczp on {{%elementactivity}} (elementId,timestamp,userId) ... done (time: 0.042s)
    > add foreign key xxxxx_fk_yrtyrmyztpuoytalxububruzenjfolyygxwa: {{%elementactivity}} (elementId) references {{%elements}} (id) ... done (time: 0.174s)
    > add foreign key xxxxx_fk_neffcrfphlhpbbqlagapdkidveirqlbnnzdc: {{%elementactivity}} (userId) references {{%users}} (id) ...Exception: SQLSTATE[HY000]: General error: 1824 Failed to ope
n the referenced table 'xxxxx_users'
The SQL being executed was: ALTER TABLE `xxxxx_elementactivity` ADD CONSTRAINT `xxxxx_fk_neffcrfphlhpbbqlagapdkidveirqlbnnzdc` FOREIGN KEY (`userId`) REFERENCES `xxxxx_users` (`id`) ON DELET
E CASCADE (/app/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /app/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE `pi...')
#1 /app/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('ALTER TABLE `pi...')
#2 /app/vendor/yiisoft/yii2/db/Migration.php(466): yii\db\Command->execute()
#3 /app/vendor/craftcms/cms/src/db/Migration.php(433): yii\db\Migration->addForeignKey('xxxxx_fk_neffcr...', '{{%elementactiv...', Array, '{{%users}}', Array, 'CASCADE', NULL)
#4 /app/vendor/craftcms/cms/src/migrations/m230710_162700_element_activity.php(31): craft\db\Migration->addForeignKey(NULL, '{{%elementactiv...', Array, '{{%users}}', Array, 'CASCADE', NULL)
#5 /app/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m230710_162700_element_activity->safeUp()
#6 /app/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()
#7 /app/vendor/craftcms/cms/src/console/controllers/MigrateController.php(361): yii\console\controllers\BaseMigrateController->migrateUp('m230710_162700_...')
#8 [internal function]: craft\console\controllers\MigrateController->actionAll()
#9 /app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#10 /app/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#11 /app/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('all', Array)
#12 /app/vendor/craftcms/cms/src/console/ControllerTrait.php(90): yii\console\Controller->runAction('all', Array)
#13 /app/vendor/yiisoft/yii2/base/Module.php(552): craft\console\controllers\MigrateController->runAction('all', Array)
#14 /app/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#15 /app/vendor/craftcms/cms/src/console/Application.php(90): yii\console\Application->runAction('migrate/all', Array)
#16 /app/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#17 /app/vendor/craftcms/cms/src/console/Application.php(121): yii\console\Application->handleRequest(Object(craft\console\Request))
#18 /app/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#19 /app/craft(13): yii\base\Application->run()

[moldedjelly]

@i-just
I've tested this further with a clean backup of the DB and it worked fine without the line "dropTableIfExists" and with the line. So I think I can put this down to it being a DB issue that was causing this - most likely a migration that failed and I hadn't restored the database correctly afterwards.

[brandonkelly]

We generally do intend to have those dropTableIfExists() calls to prevent this error. Fixed for the next release via #13721.

[brandonkelly]

Craft 4.5.6 is out now with that fix.