Unable to login to Xibo 1.8 CMS / Failed upgrade to 2.3

karldonteljames · March 5, 2020, 12:21pm

CMS Version

1.8

Installation Method

Custom

Operating System

Server 2016

Issue

Morning Guys and Gals. I was previously running 1.8, and I wasn’t sure an upgrade was going to be the best route, so I’ve just set up a new instance of the CMS (2.3) on my IIS server and finally got that working. (as a separate site running a separate library and MySQL database.)

In the process of this, I’ve clearly messed something up, and I’m not unable to log into my old cms.
Currently the error message I get presented with is as follows:

Fatal Error - sorry this shouldn’t happen. An exception has been thrown during the rendering of a template (“SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘group.isSystemNotification’ in ‘field list’”).

I do still have access to the MySQL database (as this runs on another virtual server) and the xibolibrary.

Is there a way of fixing this, please?

Old instance: xibo.mydomain
Old library: c:\XiboLib
Old db name: Xibo
new instance xibo2.mydomain
New library: c:\XiboLib2
New db name: Xibo2

Many thanks.

karldonteljames · March 5, 2020, 1:13pm

I’ve managed to get to the point where it says the cms is off-line as an upgrade is in progress… but the commands i’m running to upgrade it aren’t working.

php vendor\bin\phinx migrate -c phinx.php
This is being run from c:\inetpub\wwwroot\xibo

karldonteljames · March 5, 2020, 3:04pm

I’ve now managed to get the database upgrade started, but have run into another issue.

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘xibo.lkregionplaylist’ doesn’t exist

If i run the command again, i get:

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

karldonteljames · March 5, 2020, 3:18pm

Resolved the second issue with this:

However ‘xibo.lkregionplaylist’ error is now back

karldonteljames · March 5, 2020, 3:29pm

I’ve removed the comments from line 13 as referenced and the script finished like this:

C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

Now i’m getting an HTML 500 error.
The full command window reads as follows:

C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180130073838 InstallMigration: migrating
 == 20180130073838 InstallMigration: migrated 0.0462s

 == 20180131113100 OldUpgradeStep85Migration: migrating
 == 20180131113100 OldUpgradeStep85Migration: migrated 0.0145s

 == 20180131113853 OldUpgradeStep86Migration: migrating
 == 20180131113853 OldUpgradeStep86Migration: migrated 0.0293s

 == 20180131113941 OldUpgradeStep87Migration: migrating
 == 20180131113941 OldUpgradeStep87Migration: migrated 0.0201s

 == 20180131113948 OldUpgradeStep88Migration: migrating
 == 20180131113948 OldUpgradeStep88Migration: migrated 0.0143s

 == 20180131113952 OldUpgradeStep92Migration: migrating
 == 20180131113952 OldUpgradeStep92Migration: migrated 0.0238s

 == 20180131113957 OldUpgradeStep120Migration: migrating
 == 20180131113957 OldUpgradeStep120Migration: migrated 0.0119s

 == 20180131114002 OldUpgradeStep121Migration: migrating
 == 20180131114002 OldUpgradeStep121Migration: migrated 0.0170s

 == 20180131114007 OldUpgradeStep122Migration: migrating
 == 20180131114007 OldUpgradeStep122Migration: migrated 0.0108s

 == 20180131114013 OldUpgradeStep123Migration: migrating
 == 20180131114013 OldUpgradeStep123Migration: migrated 0.0144s

 == 20180131114017 OldUpgradeStep124Migration: migrating
 == 20180131114017 OldUpgradeStep124Migration: migrated 0.0168s

 == 20180131114021 OldUpgradeStep125Migration: migrating
 == 20180131114021 OldUpgradeStep125Migration: migrated 0.0066s

 == 20180131114025 OldUpgradeStep126Migration: migrating
 == 20180131114025 OldUpgradeStep126Migration: migrated 0.0170s

 == 20180131114030 OldUpgradeStep127Migration: migrating
 == 20180131114030 OldUpgradeStep127Migration: migrated 0.0061s

 == 20180131114050 OldUpgradeStep128Migration: migrating
 == 20180131114050 OldUpgradeStep128Migration: migrated 0.0156s

 == 20180131114058 OldUpgradeStep129Migration: migrating
 == 20180131114058 OldUpgradeStep129Migration: migrated 0.0200s

 == 20180131114103 OldUpgradeStep130Migration: migrating
 == 20180131114103 OldUpgradeStep130Migration: migrated 0.0111s

 == 20180131114107 OldUpgradeStep131Migration: migrating
 == 20180131114107 OldUpgradeStep131Migration: migrated 0.0309s

 == 20180131114110 OldUpgradeStep132Migration: migrating
 == 20180131114110 OldUpgradeStep132Migration: migrated 0.0057s

 == 20180131114114 OldUpgradeStep133Migration: migrating
 == 20180131114114 OldUpgradeStep133Migration: migrated 0.0072s

 == 20180131114118 OldUpgradeStep134Migration: migrating
 == 20180131114118 OldUpgradeStep134Migration: migrated 0.0053s

 == 20180131114123 OldUpgradeStep135Migration: migrating
 == 20180131114123 OldUpgradeStep135Migration: migrated 0.0056s

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'xibo.lkregionplaylist' doesn't exist


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

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


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

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


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

  SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'createdDt'


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'xibo.lkregionplaylist' doesn't exist


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'xibo.lkregionplaylist' doesn't exist


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'asHome' in 'field list'


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'asHome' in 'field list'


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating

In PdoAdapter.php line 134:

  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'viewPath' in 'field list'


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180131122645 OneRegionPerPlaylistMigration: migrating
 == 20180131122645 OneRegionPerPlaylistMigration: migrated 0.0125s

 == 20180131123038 PlaylistTagsMigration: migrating
 == 20180131123038 PlaylistTagsMigration: migrated 0.3080s

 == 20180131123248 WidgetFromToDtMigration: migrating
 == 20180131123248 WidgetFromToDtMigration: migrated 0.1175s

 == 20180212143336 DaypartSystemEntriesAsRecords: migrating

In PdoAdapter.php line 145:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'xibo.daypart' doesn't exist


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

 == 20180212143336 DaypartSystemEntriesAsRecords: migrating

In PdoAdapter.php line 145:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'xibo.daypart' doesn't exist


migrate [-c|--configuration CONFIGURATION] [-p|--parser PARSER] [-e|--environment ENVIRONMENT] [-t|--target TARGET] [-d|--date DATE] [-x|--dry-run]


C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php
Phinx by CakePHP - https://phinx.org. 0.9.2

using config file .\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

C:\inetpub\wwwroot\Xibo>php vendor\bin\phinx migrate -c phinx.php

alex · March 5, 2020, 7:33pm

What version of PHP are you using? Xibo 2.3 requires PHP 7.0.8 as a minimum.

karldonteljames · March 5, 2020, 7:39pm

I’m using 7.2
I Noticed there are some errors in the php log, so just addressing those, but they are related to imagic, so i guess it shouldnt make a difference.

alex · March 5, 2020, 8:00pm

You probably need to put the CMS in to Test mode so you can see what the PHP error is. Could be as simple as bad filesystem permissions on the library or cache directory.

You can swap it by running the following SQL on the database:

update `setting` set value="Test" where `setting`="SERVER_MODE";

You can turn that back off again with

update `setting` set value="Production" where `setting`="SERVER_MODE";

karldonteljames · March 5, 2020, 8:11pm

Just going to run that now. The file system permissions are set at the “c:\inetpub\www” level, and the new xibo setup is working.

karldonteljames · March 5, 2020, 8:13pm

ok, so that has allowed a page to load, that says:

Unexpected Error, please contact support.

karldonteljames · March 5, 2020, 8:13pm

debug shows:

alex · March 5, 2020, 8:26pm

This is Xibo 1.8 running on PHP 7.2? The last few versions of 1.8 run on PHP 7.1 but I’m not sure about 7.2.

What’s the PHP Error 500? You might look in the log table of the database.

karldonteljames · March 5, 2020, 8:29pm

No, I gave up trying to fix 1.8, so removed the files, installed 2.3 and tried to upgrade the database.

I’m currently running 2 sites on 2.3
the old database xibo.mydomain
and xibo2.mydomain

I just need to be able to log in to the old version back online.

How do i find the php 500 error?

alex · March 5, 2020, 8:31pm

You said in Production mode it was giving you an error 500? You’d expect the same in Test mode, only it would show you the error text from PHP. It may potentially also log stuff in the log table of the database.

If you’ve commented lines out of the migration files then I wouldn’t be very confident that it will work at all. You would be better restoring from backup and then following the suggested upgrade route (ie 1.8.13 - 2.3.1) and let the migrations run entirely.

karldonteljames · March 5, 2020, 8:34pm

I commented the line that is linked above. is there anyway i might be able to run the upgrade script manually, There is a complete capture of the commands that were run above.

in test mode i see the following:

karldonteljames · March 5, 2020, 8:37pm

if i run the same command again i get the following:

using config file .C:\inetpub\wwwroot\Xibo\phinx.php
using config parser php
using migration paths
 - C:\inetpub\wwwroot\Xibo\db\migrations
warning no environment specified, defaulting to: production
using adapter mysql
using database Xibo

alex · March 5, 2020, 8:40pm

Yes, so what is in the CMS log? You’ll need to look in the log table of the database.

alex · March 5, 2020, 8:40pm

Running it again once migrations have been completed won’t do anything, even if you make changes. It’s marked them as done so it won’t try and run them again.

karldonteljames · March 5, 2020, 8:41pm

Where will the cms log be location in windows?

alex · March 5, 2020, 8:43pm

It’s in the database in the log table.