Upgrade to 5.0.1 errors on migration

[aaronrobb]

Describe the bug or issue you're experiencing

Upgrading from 4.1.13 to 5.0.1 and running the migration shows the following error before quitting:

applying m230101_100020_FF4to5_MigrateLayout
    > create table {{%freeform_forms_layouts}} ...Exception: SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "freeform_forms_layouts" already exists
The SQL being executed was: CREATE TABLE "freeform_forms_layouts" (
    "id" serial NOT NULL PRIMARY KEY,
    "formId" integer NOT NULL,
    "dateCreated" timestamp(0) NOT NULL,
    "dateUpdated" timestamp(0) NOT NULL,
    "uid" char(36) NOT NULL DEFAULT '0'
)

Steps to reproduce

1. Upgrade Freeform via composer
2. Run a craft migration (php craft migrate/all)

Expected behavior Migration to succeed.

Craft & Plugin Info (please complete the following information):

• Craft Version: 4.7.0
• Freeform Version: 5.0.1
• Freeform Edition: Pro
• Fresh Install or Upgrade: Upgrade

Additional context

[kjmartens]

Sorry for the trouble you're experiencing, @aaronrobb.

Did you run into an issue with the migration and then attempt to try it again? If so, you will need to revert to the backup you took before upgrading to Freeform 5 and try it again. If you encounter an error/issue again, can you copy and paste that error here? It could be that there's an issue we need to address on our end. 🙂

[jbrew30]

I just updated to 5.0.1 (received the migration error) and came back to it this afternoon and updated it to 5.0.2 and I got this:

Migration: Solspace\Freeform\migrations\m230101_100020_FF4to5_MigrateLayout
Output:
> create table {{%freeform_forms_layouts}} ...Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'freeform_forms_layouts' already exists
The SQL being executed was: CREATE TABLE freeform_forms_layouts (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
formId int(11) NOT NULL,
dateCreated datetime(0) NOT NULL,
dateUpdated datetime(0) NOT NULL,
uid char(36) NOT NULL DEFAULT '0'
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_unicode_ci (/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 f...')
#1 /app/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('CREATE TABLE f...')
#2 /app/vendor/yiisoft/yii2/db/Migration.php(322): yii\db\Command->execute()
#3 /app/vendor/solspace/craft-freeform/packages/plugin/src/migrations/m230101_100020_FF4to5_MigrateLayout.php(18): yii\db\Migration->createTable('{{%freeform_for...', Array)
#4 /app/vendor/craftcms/cms/src/db/Migration.php(49): Solspace\Freeform\migrations\m230101_100020_FF4to5_MigrateLayout->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(Solspace\Freeform\migrations\m230101_100020_FF4to5_MigrateLayout))
#7 /app/vendor/craftcms/cms/src/services/Updates.php(252): 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(305): yii\base\Module->runAction('updater/migrate', Array)
#15 /app/vendor/craftcms/cms/src/web/Application.php(693): craft\web\Application->runAction('updater/migrate')
#16 /app/vendor/craftcms/cms/src/web/Application.php(262): 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(26): yii\base\Application->run()
#19 {main}

[kjmartens]

Hi @jbrew30,

Are you sure you started fresh from your backup after receiving the error when updating to 5.0.1? My hunch is that the error happened during the migration and then some of the new database tables were left when you aborted. Therefore, the migration will always fail unless you are starting from a clean v4 database. 🙂

This is what your database table should look like if proper on Freeform 4 (Freeform 5 adds numerous new tables):

[jbrew30]

Yes, I dropped my database, created a new one, imported my backup, and reran my pipeline. Once I rerun my pipeline, the table freeform_forms_layouts is added again. Rerunning the pipeline adds the 5.0.2 version of freeform.

[kjmartens]

@jbrew30, can you send me a screenshot of what the database looks like before you attempt the migration again? My hunch is that it will have the freeform_forms_layouts table already, which is a remnant of the FF5 migration.

[jbrew30]

@kjmartens I resolved the error by dropping both my production and development environments and importing the local database that already had the freeform_forms_layouts table. It seems the issue might have stemmed from how my Docker container builds and deploys. But I'm only guessing. I know this scorched earth method isn't suitable for most situations.

[kjmartens]

Thanks for letting me know @jbrew30. I'll close this issue for now, but let us know if something like this happens again or anything else pops up. 🙂