[SOLVED] Upgrading from 3.3.7 to 4.0.1, Custom install

[ONeill]

While trying to upgrade my Xibo from version 3.3.7 to 4.0.1, I got the following error:
PDOException: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘createdAt’ in vendor\robmorgan\phinx\src\Phinx\Db\Adapter\PdoAdapter.php:192

I have managed to successfully solve this problem, so I’m writing here this mini guide and solution that worked for me.

Details about my system are as follows:

Installation Method

Custom install

Operating System

I’m running Xibo on Windows Server 2019 with Apache 2.4.57, PHP 8.2 and MariaDB 10.11.5.

Issue

Getting this error while trying to upgrade/migrate database:
PDOException: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘createdAt’ in vendor\robmorgan\phinx\src\Phinx\Db\Adapter\PdoAdapter.php:192

My solution to upgrade from 3.3.7 to 4.0.1 is this:

1. Of course, first you must download Xibo 4.0.1 for WAMP, extract .zip file and replace your current “xibo” folder with this new one.

2. Using text editor like Notepad, open this file:
   C:\your_xibo_root_folder\db\migrations\20220928091249_player_software_refactor_migration.php

• on line 38, comment out this whole section where it says “Add column”.
  It should look like this (notice the beginning of the first line, and ending of the last line):

		          /*  ->addColumn('createdAt', 'datetime', ['null' => true, 'default' => null])
		            ->addColumn('modifiedAt', 'datetime', ['null' => true, 'default' => null])
		            ->addColumn('modifiedBy', 'string', ['null' => true, 'default' => null])
		            ->addColumn('fileName', 'string') 
		            ->addColumn(
		                'size',
		                'integer',
		                ['limit' => \Phinx\Db\Adapter\MysqlAdapter::INT_BIG, 'default' => null, 'null' => true]
		            )
            ->addColumn('md5', 'string', ['limit' => 32, 'default' => null, 'null' => true]) */

• on line 174, comment out:
  /* ->dropForeignKey('mediaId') */

3. Using your text editor, open this file:
   C:\your_xibo_root_folder\db\migrations\20230310143321_saved_report_move_out_migration.php

• on line 38, comment out this:

  /*  ->addColumn('fileName', 'string')
      ->addColumn('size', 'integer', ['limit' => \Phinx\Db\Adapter\MysqlAdapter::INT_BIG, 'default' => null, 'null' => true])
      ->addColumn('md5', 'string', ['limit' => 32, 'default' => null, 'null' => true]) */

• on line 78, comment out:
  /* ->dropForeignKey('mediaId') */

4. Now, go to your shell terminal and enter command for migrating. Keep in mind, you have to place yourself in your Xibo root folder before executing this command.

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

Step 5 is optional, I do it every time after upgrade.
5. run command to enable XTR:

php -f “C:\your_xibo_root_folder\bin\xtr.php”

And that’s it.
I now have Xibo 4.0.1 and everything works.

[dan]

Thanks for writing this up for others.

Those columns didn’t exist in 3.3.7, so I expect the migration failed at a subsequent step, but you then had to comment out those lines to run it again. The CMS will not function correctly if those columns do not exist.

I note this in case anyone else comes across an upgrade error and does this by default, which may not work.

Thanks!