Database Upgrade Error - Duplicate Column 2.1.2

Get Help
1

Hi all,

Struggling with this one - I’m running the Phinx migration with:

php vendor\bin\phinx migrate -c phinx.php

and getting:

warning no environment specified, defaulting to: production
using adapter mysql
using database xibo2018

== 20180131122645 OneRegionPerPlaylistMigration: migrating

[PDOException]
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'regionId’

How do I find out which table this relates to, and can I dump and recreate the table easily to get it to migrate? I’m stuck with The CMS is temporarily off-line as an upgrade is in progress. Please check with your system administrator for updates or refresh your page in a few minutes. at the moment, and no xibo!

Thanks,

James

1 Like
2

I had the exact same issue, and I landed here without finding an answer. I managed to fix the issue as follows:

Inside the db/migrations directory, there is a file named 20180131122645_one_region_per_playlist_migration.php

Open that file and modify lines 13 - 20 as follows:

13         $playlist = $this->table('playlist');
14         $playlist
15             /*->addColumn('regionId', 'integer', ['null' => true])*/
16             ->addColumn('createdDt', 'datetime', ['default' => '2019-12-01 00:00:00'])
17             ->addColumn('modifiedDt', 'datetime', ['default' => '2019-12-01 00:00:00'])
18             ->addColumn('duration', 'integer', ['default' => 0])
19             ->addColumn('requiresDurationUpdate', 'integer', ['default' => 0, 'limit' => \Phinx\Db\Adapter\MysqlAdapter::INT_TINY])
20             ->save();

In short:

  1. Comment out the addColumn call for the regionId, since it already exists
  2. Set default values for the createdDt and modifiedDt columns (you can set any value, but the empty value 0000-00-00 00:00:00 is not valid, which is why I chose the first of December for this year as a dummy value.). I had to do this as I was getting the following error:

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'createdDt'

But the modified default value fixes that.

Once you’ve made the changes just run the upgrade script again and you’re sorted.

1 Like