"Undefined column: 7 ERROR: column c.calendartype does not exist" after upgrade from 16.0.4 to 17.0.0

[gaya4]

Steps to reproduce

1. Upgrade nextcloud via repository
2. Upgrade nextcloud via webapp-config
3. Update via browser with button "start update"

Expected behaviour

The update should run successfully.

Actual behaviour

Upgrade fails with error message (in browser):
Repair step: Clean up orphan event and contact data

Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'SELECT "c"."id" FROM "oc_calendarobjects" "c" LEFT JOIN "oc_calendars" "p" ON "c"."calendarid" = "p"."id" WHERE ("p"."id" IS NULL) AND ("c"."calendartype" = ?)' with params [0]: SQLSTATE[42703]: Undefined column: 7 ERROR: column c.calendartype does not exist LINE 1: ...endarid" = "p"."id" WHERE ("p"."id" IS NULL) AND ("c"."calen... ^

Server configuration

Operating system:
Gentoo
Web server:
Server version: Apache/2.4.41 (Unix)
Server built: Aug 17 2019 06:15:36
Database:
psql (PostgreSQL) 11.5
PHP version:
PHP 7.3.11 (cli) (built: Oct 26 2019 20:02:13) ( ZTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.11, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.11, Copyright (c) 1999-2018, by Zend Technologies
Nextcloud version: (see Nextcloud admin page)
17.0.0
Updated from an older Nextcloud/ownCloud or fresh install:
updated from 16.0.4
Where did you install Nextcloud from:
repository
Signing status:

Signing status

Login as admin user into your Nextcloud and access 
http://example.com/index.php/settings/integrity/failed 
paste the results here.

-> "Nextcloud will be updated to version 17.0.0" -> updater will try to run again.
No login possible.

List of activated apps:

App list

contact, calendar, see below: ``` If you have access to your command line run e.g.: sudo -u www-data php occ app:list from within your Nextcloud installation folder ``` sudo -u apache php occ app:list :( The process control (PCNTL) extensions are required in case you want to interrupt long running commands - see http://php.net/manual/en/book.pcntl.php The current PHP memory limit is below the recommended value of 512MB. Nextcloud or one of the apps require upgrade - only a limited number of commands are available You may use your browser or the occ upgrade command to do the upgrade Enabled: - accessibility: 1.3.0 - activity: 2.10.1 - admin_audit: 1.6.0 - calendar: 1.7.1 - cloud_federation_api: 1.0.0 - contacts: 3.1.4 - dav: 1.9.2 - federatedfilesharing: 1.7.0 - files: 1.12.0 - files_frommail: 0.3.0 - files_pdfviewer: 1.6.0 - files_rightclick: 0.14.2 - files_sharing: 1.8.0 - files_trashbin: 1.6.0 - files_videoplayer: 1.6.0 - gallery: 18.4.0 - lookup_server_connector: 1.5.0 - notifications: 2.4.1 - oauth2: 1.5.0 - provisioning_api: 1.7.0 - theming: 1.7.0 - twofactor_backupcodes: 1.6.0 - updatenotification: 1.7.0 - viewer: 1.1.0 - workflowengine: 1.6.0 Disabled: - comments - encryption - federation - files_external - files_versions - firstrunwizard - logreader - nextcloud_announcements - password_policy - privacy - recommendations - serverinfo - sharebymail - support - survey_client - systemtags - text - user_ldap

Nextcloud configuration:

Config report

If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder
sudo -u apache php occ config:list system
The process control (PCNTL) extensions are required in case you want to interrupt long running commands - see http://php.net/manual/en/book.pcntl.php
The current PHP memory limit is below the recommended value of 512MB.
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "uri-international.de",
            "share.uri-international.de",
            "192.168.202.254"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "16.0.4.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "UTC",
        "installed": true,
        "theme": "",
        "loglevel": 2,
        "maintenance": false,
        "updater.secret": "***REMOVED SENSITIVE VALUE***",
        "updater.release.channel": "stable",
        "overwrite.cli.url": "https:\/\/share.uri-international.de\/",
        "app_install_overwrite": [
            "calendar"
        ]
    }
}
or 

</details>

**Are you using external storage, if yes which one:** local/smb/sftp/...
None
**Are you using encryption:** yes/no
no
**Are you using an external user-backend, if yes which one:** LDAP/ActiveDirectory/Webdav/...
no
### Client configuration
**Browser:**
firefox 68
**Operating system:**
Ubuntu Linux 18.04 8(Linux Mint)
### Logs
#### Web server error log
<details>
<summary>Web server error log</summary>

Insert your webserver log here
none other than access logs here.

</details>

#### Nextcloud log (data/nextcloud.log)
<details>
<summary>Nextcloud log</summary>

Insert your Nextcloud log here

{"reqId":"XbTOxHo11ef4Xqp0teYOvQAAABI","level":3,"time":"2019-10-26T22:55:00+00:00","remoteAddr":"192.168.202.1","user":"--","app":"jsresourceloader","method":"GET","url":"\/index.php\/settings\/integrity\/failed","message":"Could not find resource js\/config.js to load","userAgent":"Mozilla\/5.0 (X11; Ubuntu; Linux x86_64; rv:69.0) Gecko\/20100101 Firefox\/69.0","version":"16.0.4.1"}
{"reqId":"XbTOxHo11ef4Xqp0teYOvQAAABI","level":3,"time":"2019-10-26T22:55:00+00:00","remoteAddr":"192.168.202.1","user":"--","app":"no app in context","method":"GET","url":"\/index.php\/settings\/integrity\/failed","message":{"Exception":"Symfony\\Component\\Routing\\Exception\\RouteNotFoundException","Message":"Unable to generate a URL for the named route \"theming.Theming.getImage\" as such route does not exist.","Code":0,"Trace":[{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/private\/Route\/Router.php","line":337,"function":"generate","class":"Symfony\\Component\\Routing\\Generator\\UrlGenerator","type":"->","args":["theming.Theming.getImage",{"key":"logo","useSvg":true,"v":"10"},1]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/private\/URLGenerator.php","line":79,"function":"generate","class":"OC\\Route\\Router","type":"->","args":["theming.Theming.getImage",{"key":"logo","useSvg":true,"v":"10"}]},{"file":"\/var\/www\/share.uri-international.de\/share\/apps\/theming\/lib\/ThemingDefaults.php","line":241,"function":"linkToRoute","class":"OC\\URLGenerator","type":"->","args":["theming.Theming.getImage",{"key":"logo","useSvg":true,"v":"10"}]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/public\/Defaults.php","line":187,"function":"getLogo","class":"OCA\\Theming\\ThemingDefaults","type":"->","args":[true]},{"file":"\/var\/www\/share.uri-international.de\/share\/core\/templates\/layout.guest.php","line":42,"function":"getLogo","class":"OCP\\Defaults","type":"->","args":[]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/private\/Template\/Base.php","line":178,"args":["\/var\/www\/share.uri-international.de\/share\/core\/templates\/layout.guest.php"],"function":"include"},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/private\/Template\/Base.php","line":150,"function":"load","class":"OC\\Template\\Base","type":"->","args":["\/var\/www\/share.uri-international.de\/share\/core\/templates\/layout.guest.php",null]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/private\/legacy\/template.php","line":180,"function":"fetchPage","class":"OC\\Template\\Base","type":"->","args":[null]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/private\/legacy\/template.php","line":211,"function":"fetchPage","class":"OC_Template","type":"->","args":[null]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/private\/Template\/Base.php","line":131,"function":"fetchPage","class":"OC_Template","type":"->","args":[]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/base.php","line":399,"function":"printPage","class":"OC\\Template\\Base","type":"->","args":[]},{"file":"\/var\/www\/share.uri-international.de\/share\/lib\/base.php","line":955,"function":"printUpgradePage","class":"OC","type":"::","args":[{"__class__":"OC\\SystemConfig"}]},{"file":"\/var\/www\/share.uri-international.de\/share\/index.php","line":42,"function":"handleRequest","class":"OC","type":"::","args":[]}],"File":"\/var\/www\/share.uri-international.de\/share\/3rdparty\/symfony\/routing\/Generator\/UrlGenerator.php","Line":130,"CustomMessage":"--"},"userAgent":"Mozilla\/5.0 (X11; Ubuntu; Linux x86_64; rv:69.0) Gecko\/20100101 Firefox\/69.0","version":"16.0.4.1"}
</details>

#### Browser log
<details>
<summary>Browser log</summary>

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

</details>

#### Additional information:
With upgrade from 11 (unknown) to next step the calendar created errors already and it was ignored as have another parallel installation the worked fine so far (and upgraded from 16.0.4->17.0.0 fine.). Subsequent upgrades from this cloud worked fine for some time over versions 13,14,15 and 16 (with unknown minor versions). Calendar was not used and had problems , calendars could not be created.

[NilixMaster]

Hi,
the same with me as with gaya4.

Exception that I use MySql instead of PostgreSQL.

The attempt to add the field: "oc_calendartype" to the table fails because the field is deleted from the table every time I try to upgrade again.

Alter Table:
ALTER Table MyDataBase.oc_calendarobjects add column calendartype int(11) default 0 not null;
commit;

After changing the table, it is filled with the field and the value 0. After a new upgrade attempt, the field is removed from the table and runs on the above error.

[kesselb]

Wrong. calendartype should be present.

calendartype is Nextcloud 17+ and unfortunately we backported a fix for a cleanup script to Nextcloud 16 which depends on calendartype.

Index: apps/dav/lib/Migration/RemoveOrphanEventsAndContacts.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- apps/dav/lib/Migration/RemoveOrphanEventsAndContacts.php	(revision 83af640780fd1532316b07bdfc68fd570c487a2c)
+++ apps/dav/lib/Migration/RemoveOrphanEventsAndContacts.php	(date 1572183708446)
@@ -76,11 +76,6 @@
 			->leftJoin('c', $parentTable, 'p', $qb->expr()->eq('c.' . $parentId, 'p.id'))
 			->where($qb->expr()->isNull('p.id'));
 
-		if (\in_array($parentTable, ['calendars', 'calendarsubscriptions'], true)) {
-			$calendarType = $parentTable === 'calendarsubscriptions' ? CalDavBackend::CALENDAR_TYPE_SUBSCRIPTION : CalDavBackend::CALENDAR_TYPE_CALENDAR;
-			$qb->andWhere($qb->expr()->eq('c.calendartype', $qb->createNamedParameter($calendarType, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT));
-		}
-
 		$result = $qb->execute();
 
 		$orphanItems = array();

It's probably enough to apply the above patch (that removes the where condition for calendartype) to continue with the update. But please backup your database as always.

cc @nickvergessen @tcitworld

[tcitworld]

Odd, calendartype is definitely added by https://github.com/nextcloud/server/blob/stable16/apps/dav/lib/Migration/Version1006Date20180628111625.php#L59 in stable16 (and even 15!).
Isn't it related to #12410 ?

[kesselb]

Odd, calendartype is definitely added by https://github.com/nextcloud/server/blob/stable16/apps/dav/lib/Migration/Version1006Date20180628111625.php#L59 in stable16 (and even 15!).
Isn't it related to #12410 ?

Ouch 🙈 c.calendartype => calendarobjects.calendartype

After changing the table, it is filled with the field and the value 0. After a new upgrade attempt, the field is removed from the table and runs on the above error.

Scary. Have not found any code that deletes this column 🤔

[gaya4]

mind, that in my opening comment I mentioned that I have 2 domains. The other updated fine. The faulty one only had an issue with the calendars in the past. Not sure that is related with NilixMaster.

[andreas-p]

Just had the same problem upgrading from 15.x to 16.0.5 using the downoaded zip.
After rm -r apps core lib updater 3rdparty oc_ calendarobjects.calendartype didn't get removed any more, and the update went fine. So apparently some old left-over files were harmful.

[gaya4]

@andreas-p that worked well as it seems.
After deleting the files and then re-running the webapp-config -U updater the upgrade went through fine. For what it is worth:
Following apps have been disabled: calendar (incompatible), contacts (incompatible), files_frommail (incompatible)
Detailed logs
Preparing update
Set log level to debug
Turned on maintenance mode
Repair step: Repair MySQL collation
Repair info: Not a mysql database -> nothing to do
Repair step: Repair SQLite autoincrement
Repair step: Copy data from accounts table when migrating from ownCloud
Repair step: Drop account terms table when migrating from ownCloud
Updating database schema
Updated database
Repair step: Fix component of birthday calendars
Repair info: 3 birthday calendars updated.
Repair step: Regenerating birthday calendars to use new icons and fix old birthday events without year
Repair info: Repair step already executed
Repair step: Fix broken values of calendar objects
[0 / 0]: Fix broken values of calendar objects
Repair step: Registering building of calendar search index as background job
Repair info: Repair step already executed
Repair step: Registering background jobs to update cache for webcal calendars
Repair info: Added 0 background jobs to update webcal calendars
Repair step: Registering building of calendar reminder index as background job
Repair info: Repair step already executed
Repair step: Clean up orphan event and contact data
Repair info: 0 events without a calendar have been cleaned up
Repair info: 0 properties without an events have been cleaned up
Repair info: 0 changes without a calendar have been cleaned up
Repair info: 0 cached events without a calendar subscription have been cleaned up
Repair info: 0 changes without a calendar subscription have been cleaned up
Repair info: 0 contacts without an addressbook have been cleaned up
Repair info: 0 properties without a contact have been cleaned up
Repair info: 0 changes without an addressbook have been cleaned up
Repair step: Remove activity entries of private events
Repair info: Removed 0 activity entries
Updated "dav" to 1.13.0
Repair step: Fix the share type of guest shares when migrating from ownCloud
Repair step: Copy the share password into the dedicated column
Updated "files_sharing" to 1.9.0
Updated "files_trashbin" to 1.7.0
Updated "workflowengine" to 1.7.0
Updated "admin_audit" to 1.7.0
Updated "notifications" to 2.5.0
Updated "theming" to 1.8.0
Checking for update of app "accessibility" in appstore
Checked for update of app "accessibility" in appstore
Checking for update of app "activity" in appstore
Checked for update of app "activity" in appstore
Checking for update of app "admin_audit" in appstore
Checked for update of app "admin_audit" in appstore
Checking for update of app "cloud_federation_api" in appstore
Checked for update of app "cloud_federation_api" in appstore
Checking for update of app "dav" in appstore
Checked for update of app "dav" in appstore
Checking for update of app "federatedfilesharing" in appstore
Checked for update of app "federatedfilesharing" in appstore
Checking for update of app "files" in appstore
Checked for update of app "files" in appstore
Checking for update of app "files_pdfviewer" in appstore
Checked for update of app "files_pdfviewer" in appstore
Checking for update of app "files_rightclick" in appstore
Update app "files_rightclick" from appstore
Checked for update of app "files_rightclick" in appstore
Checking for update of app "files_sharing" in appstore
Checked for update of app "files_sharing" in appstore
Checking for update of app "files_trashbin" in appstore
Checked for update of app "files_trashbin" in appstore
Checking for update of app "files_videoplayer" in appstore
Checked for update of app "files_videoplayer" in appstore
Checking for update of app "gallery" in appstore
Checked for update of app "gallery" in appstore
Checking for update of app "lookup_server_connector" in appstore
Checked for update of app "lookup_server_connector" in appstore
Checking for update of app "notifications" in appstore
Checked for update of app "notifications" in appstore
Checking for update of app "oauth2" in appstore
Checked for update of app "oauth2" in appstore
Checking for update of app "provisioning_api" in appstore
Checked for update of app "provisioning_api" in appstore
Checking for update of app "theming" in appstore
Checked for update of app "theming" in appstore
Checking for update of app "twofactor_backupcodes" in appstore
Checked for update of app "twofactor_backupcodes" in appstore
Checking for update of app "updatenotification" in appstore
Checked for update of app "updatenotification" in appstore
Checking for update of app "viewer" in appstore
Checked for update of app "viewer" in appstore
Checking for update of app "workflowengine" in appstore
Checked for update of app "workflowengine" in appstore
Checking for update of app "calendar" in appstore
Checked for update of app "calendar" in appstore
Checking for update of app "contacts" in appstore
Checked for update of app "contacts" in appstore
Checking for update of app "files_frommail" in appstore
Checked for update of app "files_frommail" in appstore
Repair step: Repair MySQL collation
Repair info: Not a mysql database -> nothing to do
Repair step: Repair mime types
Repair step: Clean tags and favorites
Repair info: 0 tags of deleted users have been removed.
Repair info: 0 tags for delete files have been removed.
Repair info: 0 tag entries for deleted tags have been removed.
Repair info: 0 tags with no entries have been removed.
Repair step: Repair invalid shares
Repair step: Move .step file of updater to backup location
Repair step: Fix potential broken mount points
Repair info: No mounts updated
Repair step: Add log rotate job
Repair step: Clear frontend caches
Repair info: Image cache cleared
Repair info: SCSS cache cleared
Repair info: JS cache cleared
Repair step: Clear every generated avatar on major updates
Repair step: Add preview background cleanup job
Repair step: Queue a one-time job to cleanup old backups of the updater
Repair step: Cleanup invalid photocache files for carddav
Repair step: Add background job to cleanup login flow v2 tokens
Repair step: Remove potentially over exposing share links
Repair info: No need to remove link shares.
Repair step: Clear access cache of projects
Repair step: Cleanup cypress files from viewer app
Repair step: Switches from deprecated "production" to "stable" update channel
Repair step: Sets the enterprise logo
Starting code integrity check
Finished code integrity check
Turned off maintenance mode
Reset log level
Following apps have been disabled:
calendar (incompatible),
contacts (incompatible),
files_frommail (incompatible)

Now I'm logged in and indeed this even repaired my calendar on that cloud.
As per opening comment, the calendar didn't appear anymore and threw up errors on client (no calendar reaction on GUI) after upgrading somewhere about nextcloud 12+ (unknown). Now that very same calendar is visible again.
When checking the timeline for the nextcloud releases it must be Nextcloud 14 that made something with my calendars. @andreas-p (rm -r apps core lib updater 3rdparty) did fix that and the old calendar is accessible again.

The incompatible apps of calendar and contacts might have caused it? I am now logged in and can view the calendar without any particular calendar app installed, other than default.

Thank you.

Either this is worth improving the code or if you need something, I'll try to supply it. Or close it, if no need to work on it.

[girascal]

After deleting the files and then re-running the webapp-config -U updater the upgrade went through fine. For what it is worth:

Hi I got the same issue while updating from 16.0.4 to 16.0.5 and I don't know how to resolve it
I saw that you where able to do it but I don't really understand the step you did so could you explain to me wich file you deleted so that I can get throught this update?

[ChipwizBen]

I also have the same issue with the column deleting itself after adding it in manually. This is an upgrade from 16.0.4 to 17.0.0.

I've had issues with various missing columns in the past that I've had to add manually to resolve problems like this, though this is the first where is hasn't worked (since it deletes it!). Is there a maintenance script that can be run to check each table for the correct columns and column types?