1.79 to 1.8rc2 upgrade SQL errors

Developer Experience
1

Hi,
My boss is eager to start using the PDF functionality, so he has me upgrading to 1.8 RC2. It’s a standalone install (not docker) on xampp.

I copied files to a new folder, changed the webroot and started the install. I chose to use the existing database and entered the info. the first thing I’m prompted with is:

An error occurred populating the database. Statement number: 74. Error Message = [SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘isSystemNotification’ in ‘field list’]. File = [data.sql]. SQL = [INSERT INTO group (groupID, group, IsUserSpecific, IsEveryone, isSystemNotification) VALUES (1, ‘Users’, 0, 0, 0), (2, ‘Everyone’, 0, 1, 0), (3, ‘xibo_admin’, 1, 0, 1), (4, ‘System Notifications’, 0, 0, 1)].

If I click ‘next’ again, I get

Base table or view already exists: 1050 Table ‘oauth_client_scopes’ already exists

If I choose a fresh DB, I can install, but no library, no layouts, no displays. I would like to keep the old DB so my layouts, displays and other settings are kept.

help?

John

2

You can’t install into the existing database - if you want to upgrade you need to follow the upgrade procedure and copy your old settings.php file into the new installation folder.

What you’ve tried to do is a fresh install on top of an existing one.

3

I reverted the VM to a good state, copied teh folders and settings.xml to the new location and started the upgrade. It ran, showing step one was working and stayed there until the screen timed out. When I logged back in all the steps were not started and nothing in progress. I clicked Start again and got 1091 Can’t DROP ‘scheduleID’; check that column/key exists.

What did I do wrong this time?

upgrade error.PNG990×227 8.23 KB

4

Are you sure its reverted back to a working state? The error would imply that the log table has already been altered from the structure we’d expect in 1.7.9 (i.e there is no log.scheduleID column and there should be).

It may also be wise to truncate your log and stat tables before the upgrade, simply because you are likely to have a lot of data in there, which might cause timeouts.

We don’t do this by default, because its a bit severe :slight_smile:

5

Hi,
the schedule ID error occurred after the the second run of the upgrade. the first run timed out after 15min (when xibo kicks you back to the login page). I’ll backup stuff and truncate the log and stat tables.

6

I was a little nicer to my DB and just deleted rows old than 1 month. Took out about a 100,000 rows.
The upgrade went through step 43 “Upgrade layouts” where I got :SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘xibo.lktagcampaign’ doesn’t exist
the option to skip this Step stops the upgrade at step 44.

7

Welcome to the club :slight_smile: , you should check out this thread: 1.7.8 to 1.8.0-rc2 Upgrade error

8

Thanks, I rolled back again and added that code to the tag.php file. I’ll have to wait a little bit to purge the log files again (I didn’t snapshot after deleting the rows).

9

I got the same error after updating the code, but I found that my original DB didn’t have a lktagcampaign table. Bad upgrade from another version? I don’t know. I used Mysql admin to export that table from my fresh install and imported it to my production database. Voila! all 164 steps completed successfully!
Now I need to fix a few things I had on the old install (phpicalendars, local files put there by scripts, etc).

Thank you all for the help.

10

The actual issue (in case anyone wonders) is that the lktagcampaign table is added in RC2, but an install step prior to that references that table - the code patch says “do not try to get anything from this table unless we are on RC2”.

There is no risk to adding that table early, as the later upgrade statement that does add that table will only do so if it does not yet exist.

I’m pleased you have it working.