If syncing project.yaml fails for whatever reason, your database gets messed up and you have to restore from backup

[RaederDev]

Description

I migrated an old project over to using project.yaml, deployed it on my staging system and pressed the apply changes button. During the migrations it complained that it had no write permissions to copy the project.yaml somewhere?

One of Craft CMS’s migrations failed.

yii\base\ErrorException: rename(/app/config/project.yaml,/app/config/project.yaml.N7hgOI1Rhg): Permission denied

Migration: craft\migrations\m180521_173000_initial_yml_and_snapshot

Output:

> add column config mediumtext NULL DEFAULT NULL AFTER maintenance to table {{%info}} ... done (time: 0.003s)
> add column configMap mediumtext NULL DEFAULT NULL AFTER config to table {{%info}} ... done (time: 0.003s)
> renaming project.yaml to project.yaml.N7hgOI1Rhg ... Exception: rename(/app/config/project.yaml,/app/config/project.yaml.N7hgOI1Rhg): Permission denied (/app/vendor/craftcms/cms/src/migrations/m180521_173000_initial_yml_and_snapshot.php:37)
#0 /app/vendor/craftcms/cms/src/web/ErrorHandler.php(76): yii\base\ErrorHandler->handleError(2, 'rename(/app/con...', '/app/vendor/cra...', 37)
#1 [internal function]: craft\web\ErrorHandler->handleError(2, 'rename(/app/con...', '/app/vendor/cra...', 37, Array)
#2 /app/vendor/craftcms/cms/src/migrations/m180521_173000_initial_yml_and_snapshot.php(37): rename('/app/config/pro...', '/app/config/pro...')
#3 /app/vendor/craftcms/cms/src/db/Migration.php(56): craft\migrations\m180521_173000_initial_yml_and_snapshot->safeUp()
#4 /app/vendor/craftcms/cms/src/db/MigrationManager.php(243): craft\db\Migration->up(true)
#5 /app/vendor/craftcms/cms/src/db/MigrationManager.php(163): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m180521_173000_initial_yml_and_snapshot))
#6 /app/vendor/craftcms/cms/src/services/Updates.php(215): craft\db\MigrationManager->up()
#7 /app/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(521): craft\services\Updates->runMigrations(Array)
#8 /app/vendor/craftcms/cms/src/controllers/UpdaterController.php(209): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#9 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#10 /app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /app/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 /app/vendor/craftcms/cms/src/web/Controller.php(109): yii\base\Controller->runAction('migrate', Array)
#13 /app/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('migrate', Array)
#14 /app/vendor/craftcms/cms/src/web/Application.php(297): yii\base\Module->runAction('updater/migrate', Array)
#15 /app/vendor/craftcms/cms/src/web/Application.php(683): craft\web\Application->runAction('updater/migrate')
#16 /app/vendor/craftcms/cms/src/web/Application.php(223): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#17 /app/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /app/web/index.php(21): yii\base\Application->run()
#19 {main}

And now my database is broken:

One of Craft CMS’s migrations failed.

Database Exception: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'config'
The SQL being executed was: ALTER TABLE kim_info ADD config mediumtext NULL DEFAULT NULL AFTER maintenance

Migration: craft\migrations\m180521_173000_initial_yml_and_snapshot

Output:

> add column config mediumtext NULL DEFAULT NULL AFTER maintenance to table {{%info}} ...Exception: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'config'
The SQL being executed was: ALTER TABLE kim_info ADD config mediumtext NULL DEFAULT NULL AFTER maintenance (/app/vendor/yiisoft/yii2/db/Schema.php:664)
#0 /app/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE ki...')
#1 /app/vendor/yiisoft/yii2/db/Command.php(1075): yii\db\Command->internalExecute('ALTER TABLE ki...')
#2 /app/vendor/yiisoft/yii2/db/Migration.php(377): yii\db\Command->execute()
#3 /app/vendor/craftcms/cms/src/migrations/m180521_173000_initial_yml_and_snapshot.php(26): yii\db\Migration->addColumn('{{%info}}', 'config', Object(craft\db\mysql\ColumnSchemaBuilder))
#4 /app/vendor/craftcms/cms/src/db/Migration.php(56): craft\migrations\m180521_173000_initial_yml_and_snapshot->safeUp()
#5 /app/vendor/craftcms/cms/src/db/MigrationManager.php(243): craft\db\Migration->up(true)
#6 /app/vendor/craftcms/cms/src/db/MigrationManager.php(163): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m180521_173000_initial_yml_and_snapshot))
#7 /app/vendor/craftcms/cms/src/services/Updates.php(215): craft\db\MigrationManager->up()
#8 /app/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(521): craft\services\Updates->runMigrations(Array)
#9 /app/vendor/craftcms/cms/src/controllers/UpdaterController.php(209): 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(157): yii\base\InlineAction->runWithParams(Array)
#13 /app/vendor/craftcms/cms/src/web/Controller.php(109): yii\base\Controller->runAction('migrate', Array)
#14 /app/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('migrate', Array)
#15 /app/vendor/craftcms/cms/src/web/Application.php(297): yii\base\Module->runAction('updater/migrate', Array)
#16 /app/vendor/craftcms/cms/src/web/Application.php(683): craft\web\Application->runAction('updater/migrate')
#17 /app/vendor/craftcms/cms/src/web/Application.php(223): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#18 /app/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#19 /app/web/index.php(21): yii\base\Application->run()
#20 {main}

Why doesn't craft check if all requirements are met on the host BEFORE running destructive migrations? Why are migrations not atomic and revers-able in case an error like the one I mentioned above occurs?

Steps to reproduce

1. Have old craft version on target host
2. Have permission problem
3. Start project.yaml sync
4. Profit

[andris-sevcenko]

One issue here is that MySQL does not really respect transactions if a table needs to be altered, so transactions cannot help to make it atomic. (reference)

As far as reversibility goes, it's also not that simple. All of the migrations supplied by Craft are not reversible for the simple reason that the codebase expects a certain schema to be in place. If migrations were allowed to be reversible, you could end up in a situation where code tried to execute a query on a database schema it expected only to find out that the table structure is not what was expected.

One more trickiness is if a migration fails somewhere in-between (how it happened for you), where it becomes super-awkward, as MySQL commits the transaction every time a table is altered, leaving it in a messed up state.

The first order of business here is to move the project config file to a place that's writable, though, so I'll start with that.

[andris-sevcenko]

Fixed the underlying issue of Craft copying the existing config file to a folder that was not required to be writable.

The general "migrations can break" issue cannot be easily fixed due to the reasons outlined above.

[RaederDev]

Thanks for the fast response!