initial_yml_and_snapshot migration failed, no transaction rollback Craft 3.1

[ostark]

Description

The initial_yml_and_snapshot migration adds the config and configMap field to the info table.

When the something fails later in _getProjectConfigData(), e.g. in my case it was a missing array
index, the migration fails.

Craft/Yii will try to run the migration again, as it wasn't finished. However the columns in the info table still exist. Then you run into another issue Column already exists: 1060 Duplicate column name 'config'.

Removing the column manually (and upgrading to 3.1.1) solved it eventually for me.

Idea: Add the columns only if they don't exist.

// Check if  columns exist before adding them
$infoSchema = $this->getDb()->getTableSchema(Table::INFO);        
if ($infoSchema->getColumn('config') === null && $infoSchema->getColumn('configMap') === null) {
    $this->addColumn(Table::INFO, 'config', $this->mediumText()->null()->after('maintenance'));
    $this->addColumn(Table::INFO, 'configMap', $this->mediumText()->null()->after('config'));
}

Additional info

• Craft version: 3.1.0 / 3.1.1
• PHP version: 7.2
• Database driver & version: Mysql 5.7

[smcyr]

I have the same error:

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

Migration: craft\migrations\m180521_173000_initial_yml_and_snapshot
...

I did the upgrade to 3.1.1 in local first, then I created the project.yaml and deployed everything on preprod. And it's on preprod, when running the migrations that I had the error.

[smcyr]

Also, I tried to delete the columns config and configMap in the database and I re-runned the migrations and I have this error now:

Not Supported: Changes to the project config are not possible while in read-only mode.

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.024s)
> add column configMap mediumtext NULL DEFAULT NULL AFTER config to table {{%info}} ... done (time: 0.025s)

[ostark]

@smcyr related to your issue #3653

[brandonkelly]

Migrations shouldn't have to worry about cleaning up after past failed migrations... that’s what database backups and restores are for. Otherwise every line of every migration is going to need to start being defensive. And many migrations make “lossy” changes that wouldn’t be able to be guarded against even if we wanted to.

And worth noting that PostgreSQL doesn’t have this problem, only MySQL, because MySQL auto-commits transactions as soon as the schema is changed in any way, whereas PostgreSQL will be able to roll back all of its changes in the event of an error.

[ostark]

Good for PostgreSQL, not so good for the rest of us.

[brandonkelly]

Agreed but what you’re asking is like playing whack-a-mole with 10,000 mole holes.