Server: Issue while upgrading to 18 Beta1 (SQL, related to Flow)

[daita]

Steps to reproduce

1. Install Nextcloud 17.0.1
2. Download Nextcloud 18-Beta1 from download.nextcloud.com, and unzip the file
3. Run the upgrade from the webclient.

Expected behaviour

Nextcloud should upgrade to 18-Beta1

Actual behaviour

SQL Issue:

Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'SELECT `o`.`id` FROM `oc_flow_operations` `o` 
LEFT JOIN `oc_flow_operations_scope` `s` ON `o`.`id` = `s`.`operation_id` 
WHERE `s`.`operation_id` IS NULL': 
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud_test.oc_flow_operations_scope' doesn't exist

Server configuration

Operating system:
Linux

Web server:
Apache

Database:
MariaDB

PHP version:
7.3.4

Nextcloud version: (see Nextcloud admin page)
17.0.1 -> 18-beta1

Updated from an older Nextcloud/ownCloud or fresh install:
fresh and clean install

[jospoortvliet]

So my upgrade exploded completely in what looks like the same way...

2019-12-06T16:12:19+00:00 Updated database
2019-12-06T16:12:19+00:00 Updating <workflowengine> ...
2019-12-06T16:12:19+00:00 Repair step: Populating added database structures for workflows
2019-12-06T16:12:19+00:00 Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'UPDATE "oc_flow_operations" SET "entity" = ? WHERE "entity" = ''' with params ["OCA\\WorkflowEngine\\Entity\\File"]:

SQLSTATE[HY000]: General error: 1 no such column: entity
2019-12-06T16:12:19+00:00 Update failed
2019-12-06T16:12:19+00:00 Maintenance mode is kept active
2019-12-06T16:12:19+00:00 Reset log level

💣
I then tried to run the occ maintenance:repair command:

In AbstractSQLiteDriver.php line 70:

  An exception occurred while executing 'UPDATE "oc_flow_operations" SET "entity" = ? WHERE "entity" = ''' with params ["OCA\\WorkflowEngine\\Entity\\File"]:  

  SQLSTATE[HY000]: General error: 1 no such column: entity                                                                                                     

In PDOConnection.php line 63:
  SQLSTATE[HY000]: General error: 1 no such column: entity  

In PDOConnection.php line 61:
  SQLSTATE[HY000]: General error: 1 no such column: entity  

💥
I tried to disable the workflow-engine app:

sudo -u wwwrun php occ app:disable workflowengine
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
workflowengine can't be disabled

Now I'm stuck on a broken instance... Tips would be helpful ;-)

[jospoortvliet]

@blizzz as I see workflow here, I'm going to put the blame on you for now. Feel free to distribute or just blame it on the user :grimacing:

This doesn't always go wrong, clearly, as my desktop at home updated properly, the laptop test instance did not howevah.

[cabaretboy]

I had the same issue. In order to keep using nextcloud, I moved the nextcloud installation to another folder, installed version 17.0.1, and then copied my config from my old nextcloud installation. I then had to reinstall a few apps that weren't included with the basic 17.0.1 installation.
Now that I have a working nextcloud again, I will have a look at the database and see what I can find. I'll report back if I work something out.

Edit: Having looked at my database, I can see that the _oc_flow_operations_ table only includes the columns: '_id, class, name, checks, operation_'.
I added a column named '_entity_' of type _varchar[256]_ with the default value of _NULL_, and the update seemed to work fine.

[jospoortvliet]

@cabaretboy good catch. @rullzer we should make sure this makes it into the next beta to avoid breaking instances...

[hapee]

the above is about oc_flow_operations table but in my case and the first one it is the oc_flow_operations_scope table:

In AbstractMySQLDriver.php line 42:
An exception occurred while executing 'SELECT o.id FROM oc_flow_operations o LEFT JOIN oc_flow_operations_scope s ON o.id = s.operation_id WHERE s.operation_id IS NULL':

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

In PDOConnection.php line 90:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_flow_operations_scope' doesn't exist

In PDOConnection.php line 88:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_flow_operations_scope' doesn't exist

maintenance:repair [--include-expensive]

Any idea how the oc_flow_operations_scope table should look like?

[rullzer]

So I'm not sure how this happens as every test I throw at it it works.

but @daita is running more checks. Hopefully he can find something.

[hapee]

I am also looking into it, installed a clean one on localhost, passed the same message but with a repair it worked and the upgrade as well. With the information on localhost I was able to create the _scope table and than run into the same problem as @cabaretboy had so manually edited the oc_flow_operations table and added the missing entity and events rows. If I run a maintenance:repair everything looks fine but when I do an upgrade, the upgrade deletes the entity and events again and fails and when I do an maintenance:repair again it fails as well:

DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'
Update failed

[rullzer]

It is almost as if for some reason on your systems the Migration is not run.

Could one of your try with a debugger (or just stupidly with a var_dump or something) in https://github.com/nextcloud/server/blob/master/apps/workflowengine/lib/Migration/Version2000Date20190808074233.php#L25

[bhessen]

I have the same problem with v18 RC1.
I tried changing the database table and add a column 'entity' but this gets deleted every time I run the upgrade procedure.

It is almost as if for some reason on your systems the Migration is not run.

Could one of your try with a debugger (or just stupidly with a var_dump or something) in https://github.com/nextcloud/server/blob/master/apps/workflowengine/lib/Migration/Version2000Date20190808074233.php#L25

I looked at the code and noticed that in the function 'changeSchema(...)' after 'createTable('flow_operations')' there are only column entries for 'id', 'name', 'class', 'checks', 'operation'. But no 'entity'...

I tried add this column into the file, but still no column 'entity' in my database if I run it afterwards...

[Sp1l]

Looks like the autoloading of the class doesn't work

# su -m www -c "php ./occ migrations:status workflowengine"
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

In MigrationService.php line 453:

  Migration step 'OCA\WorkflowEngine\Migration\Version2000Date20190808074233' is unknown  


migrations:status <app>

[Sp1l]

Managed to continue after commenting https://github.com/nextcloud/server/blob/c7d5b8fc493520ab40076ddade3632152dbfef00/apps/workflowengine/lib/Migration/PopulateNewlyIntroducedDatabaseFields.php#L60
Database is empty after creation, updater fails on this statement.

[blizzz]

I tried to reproduce it today, but to no avail. I set up latest 17 and did the upgrade routine against a DB without any entries in the flow tables, and another attempt with a flow rule. Both went through flawlessly. Same on upgrades of both the private instances of @juliushaertl and me.

[blizzz]

I looked at the code and noticed that in the function 'changeSchema(...)' after 'createTable('flow_operations')' there are only column entries for 'id', 'name', 'class', 'checks', 'operation'. But no 'entity'...

it's added with ensureEntityColumns which is called afterwards.

[blizzz]

@Sp1l in your case apps weren't loaded as the upgrade procedure was due, therefore the class could not be found indeed. This is expected (like in maintenance mode).

[Sp1l]

@Sp1l in your case apps weren't loaded as the upgrade procedure was due, therefore the class could not be found indeed. This is expected (like in maintenance mode).

1. When upgrading from 17.0.2 to 18 aren't you supposed to run the upgrade procedure?
2. Was there some condition that pushed it into maintenance mode that caused this?
3. Are any additional upgrade steps required?

Just want to prevent the "world" falling on top of me when I update the FreeBSD package...
I can add a message to alert users of additional steps.

[blizzz]

@Sp1l no, when in maintenance mode, or the upgrade procedure is due, the apps are not being loaded, until either is resolved. Therefore, when the upgrade routine is pending, occ migrations:status workflowengine cannot resolve anything that belongs to workflowengine, as it is also implemented as an app.

[gbip]

I just ran into the same issue, upgrading from Nextcloud 17.0.1 on FreeBSD to Nextcloud 18.
I copied the Nextcloud 18 files to my nextcloud folder, and launched the upgrade process which failed with the following error :

Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'SELECT `o`.`id` FROM `oc_flow_operations` `o` LEFT JOIN `oc_flow_operations_scope` `s` ON `o`.`id` = `s`.`operation_id` WHERE `s`.`operation_id` IS NULL':

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_flow_operations_scope' doesn't exist
Update failed

[tgurr]

I just ran into the same like @gbip posted above, I then cleaned my htdocs and only kept config, data and apps that don't come with nextcloud by default in the apps folder, afterwards the occ upgrade went through smooth so I suspect some files that came with the previous version causing the error when still being around.

[tasagore]

Same here, unable to upgrade from 17.0.2 to 18.0.0, error in the oc_flow_operations_scope.

[ivdok]

Ditto with nextcloud-18.0.0 (MD5: 4ce527afe81d08aeaa47c40bae2e1fdd), upgrade 17.0.2 => 18.0.0 fails with following logs on Debian 10 + nginx setup.

[kesselb]

If you have the same issue please use the issue template https://github.com/nextcloud/server/blob/master/.github/ISSUE_TEMPLATE/Bug_report.md and add a comment to this thread.

It's fine to skip expected behaviour, steps to reproduce, etc. but we need the basic system information, configuration export and logs (if they look helpful). I debugged a issue with multiple apps_paths recently. Maybe that's related and the information (multiple apps_paths yes/no) important (at least to me).

cc @ivdok @tasagore If possible please edit your posts and also add it. Thanks :+1:

[ivdok]

If you have the same issue please use the issue template:https://github.com/neaxtcloud/server/blob/master/.github/ISSUE_TEMPLATE/Bug_report.md

It's fine to skip expected behaviour, steps to reproduce, etc. but we need the basic system information, configuration export and logs (if they look helpful). I debugged a issue with multiple apps_paths recently. Maybe that's related and the information (multiple apps_paths yes/no) important (at least to me).

cc @ivdok @tasagore If possible please edit your posts and also add it. Thanks +1

It's 1:1 error, creating another issue will only get it closed "as a duplicate of #18265".

[kesselb]

Yes. That's what I always do ;) I was asking to add the issue template to your post. Not to create a new issue.

[HackintoshHD]

@kesselb There's a typo in your URL, could you remove the "a" from "neaxtcloud"? - Thanks a lot!

[testos77]

Steps to reproduce

Install Nextcloud 17.0.1
Download Nextcloud 18  from download.nextcloud.com, and unzip the file
Run php occ upgrade

Expected behaviour

Nextcloud should upgrade to 18
Actual behaviour

Updated database
Updating ...
DoctrineDBALExceptionTableNotFoundException: An exception occurred while executing 'SELECT o.id FROM oc_flow_operations o LEFT JOIN oc_flow_operations_scope s ON o.id = s.operation_id WHERE s.operation_id IS NULL':

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'owncloud.oc_flow_operations_scope' doesn't exist
Update failed
Maintenance mode is kept active
Reset log level
Server configuration

Operating system:
Linux

Web server:
Nginx

Database:mariadb Ver 15.1 Distrib 10.3.17-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

PHP version:
PHP 7.3.11-1~deb10u1

Nextcloud version: 17.0.1 -> 18

Updated from an older Nextcloud/ownCloud or fresh install:
updated

[kesselb]

apps/workflowengine/appinfo/database.xml do you have this file? :thinking:

[testos77]

Yes
sha256sum
99e6a4762b81a0e4d005b9b020a2c50b878e831593e09caaab9e439d79e79db8 database.xml`

[kesselb]

Ouch. Download the zip and just extract the archive in place. Problem: The old database.xml has priority over the migrations. So only the old tables are created. That's why the updater move the files around.

Delete apps/workflowengine/appinfo/database.xml before running ./occ upgrade

[testos77]

For this installation it's too late now.

[kesselb]

Here is the migration: https://github.com/nextcloud/server/blob/68748d4f85dd23238aaafb787b1c341f0f2f0419/apps/workflowengine/lib/Migration/Version2000Date20190808074233.php

For MySQL 8.x with utf8mb4 enabled it looks like below. You should already have oc_flow_checks and oc_flow_operations. But oc_flow_operations_scope is missing and oc_flow_operations needs entity and events column.

CREATE TABLE `oc_flow_checks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `operator` varchar(16) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `value` longtext COLLATE utf8mb4_bin,
  `hash` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `flow_unique_hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED

CREATE TABLE `oc_flow_operations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `name` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `checks` longtext COLLATE utf8mb4_bin,
  `operation` longtext COLLATE utf8mb4_bin,
  `entity` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `events` longtext COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED

CREATE TABLE `oc_flow_operations_scope` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `operation_id` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL DEFAULT '0',
  `value` varchar(64) COLLATE utf8mb4_bin DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `flow_unique_scope` (`operation_id`,`type`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED

[testos77]

Thx a lot server is running v18 now.

[HackintoshHD]

Upgrade 17.0.2 => 18.0.0 leaves Nextcloud instance broken, fails with SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cloud.oc_flow_operations_scope' doesn't exist

Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'SELECT `o`.`id` FROM `oc_flow_operations` `o` LEFT JOIN `oc_flow_operations_scope` `s` ON `o`.`id` = `s`.`operation_id` WHERE `s`.`operation_id` IS NULL':

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cloud.oc_flow_operations_scope' doesn't exist
Update failed

Steps to reproduce

1. wget https://download.nextcloud.com/server/releases/nextcloud-18.0.0.tar.bz2
2. tar xvf nextcloud-18.0.0.tar.bz2
3. sudo -u DedicatedUser /srv/www/NextcloudDirectory/occ maintenance:mode --on
4. systemctl stop apache2
5. rm -rf various old files/folders including /core, /3rdparty, /l10n, /ocs-provider, /ocs, /lib, /resources, /themes, /settings, /updater ...
6. cp -p -R /path/to/decompressed/nextcloud-18.0.0.tar.bz2-files /srv/www/NextcloudDirectory
7. sudo -u DedicatedUser /srv/www/NextcloudDirectory/occ upgrade

Expected behaviour

occ should normally upgrade the Nextcloud 17 instance to Nextcloud 18.

Actual behaviour

The upgrade fails with

Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'SELECT `o`.`id` FROM `oc_flow_operations` `o` LEFT JOIN `oc_flow_operations_scope` `s` ON `o`.`id` = `s`.`operation_id` WHERE `s`.`operation_id` IS NULL':

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cloud.oc_flow_operations_scope' doesn't exist
Update failed

Entire output:

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
Set log level to debug
Updating database schema
Updated database
Disabled incompatible app: calendar
Disabled incompatible app: checksum
Disabled incompatible app: contacts
Disabled incompatible app: files_fulltextsearch
Disabled incompatible app: fulltextsearch
Disabled incompatible app: gallery
Disabled incompatible app: spreed
Updating <accessibility> ...
Migrate old user config

 Done
   16/16 [============================] 100%
Updated <accessibility> to 1.4.0
Updating <federatedfilesharing> ...
Updated <federatedfilesharing> to 1.8.0
Updating <files_pdfviewer> ...
Updated <files_pdfviewer> to 1.7.0
Updating <files_rightclick> ...
Updated <files_rightclick> to 0.15.2
Updating <files_videoplayer> ...
Updated <files_videoplayer> to 1.7.0
Updating <logreader> ...
Updated <logreader> to 2.3.0
Updating <password_policy> ...
Updated <password_policy> to 1.8.0
Updating <privacy> ...
Updated <privacy> to 1.2.0
Updating <provisioning_api> ...
Updated <provisioning_api> to 1.8.0
Updating <recommendations> ...
Updated <recommendations> to 0.6.0
Updating <serverinfo> ...
Updated <serverinfo> to 1.8.0
Updating <support> ...
Updated <support> to 1.1.0
Updating <survey_client> ...
Updated <survey_client> to 1.6.0
Updating <text> ...
Updated <text> to 2.0.0
Updating <twofactor_backupcodes> ...
Updated <twofactor_backupcodes> to 1.7.0
Updating <updatenotification> ...
Updated <updatenotification> to 1.8.0
Updating <federation> ...
Updated <federation> to 1.8.0
Updating <lookup_server_connector> ...
Updated <lookup_server_connector> to 1.6.0
Updating <oauth2> ...
Updated <oauth2> to 1.6.0
Updating <files> ...
Updated <files> to 1.13.1
Updating <activity> ...
Updated <activity> to 2.11.0
Updating <cloud_federation_api> ...
Updated <cloud_federation_api> to 1.1.0
Updating <dav> ...
Fix broken values of calendar objects
 Done
    0/0 [>---------------------------]   0%
Updated <dav> to 1.14.0
Updating <files_external> ...
Updated <files_external> to 1.9.0
Updating <files_sharing> ...
Updated <files_sharing> to 1.10.1
Updating <files_trashbin> ...
Updated <files_trashbin> to 1.8.0
Updating <files_versions> ...
Updated <files_versions> to 1.11.0
Updating <sharebymail> ...
Updated <sharebymail> to 1.8.0
Updating <workflowengine> ...
Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'SELECT `o`.`id` FROM `oc_flow_operations` `o` LEFT JOIN `oc_flow_operations_scope` `s` ON `o`.`id` = `s`.`operation_id` WHERE `s`.`operation_id` IS NULL':

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cloud.oc_flow_operations_scope' doesn't exist
Update failed
Maintenance mode is kept active
Reset log level

Server configuration

Operating system: Debian 10.2 Buster (amd64)

Web server: Apache2 2.4.38 with mod_fcgid and suEXEC

Database: 10.3.18-MariaDB-0+deb10u1 Debian 10

PHP version: PHP 7.3.11-1~deb10u1 (cgi)

Nextcloud version: (see Nextcloud admin page) 17.0.2

Updated from an older Nextcloud/ownCloud or fresh install: Updated from an older Nextcloud install

Where did you install Nextcloud from: Published .tar.bz2 archive, MD5: 672a5af3bb292ee8152c587a37abbea6

List of activated apps:

App list

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.4.0
  - activity: 2.11.0
  - admin_audit: 1.7.0
  - apporder: 0.9.0
  - cloud_federation_api: 1.1.0
  - comments: 1.7.0
  - dav: 1.14.0
  - federatedfilesharing: 1.8.0
  - federation: 1.8.0
  - files: 1.13.1
  - files_external: 1.9.0
  - files_pdfviewer: 1.7.0
  - files_rightclick: 0.15.2
  - files_sharing: 1.10.1
  - files_trashbin: 1.8.0
  - files_versions: 1.11.0
  - files_videoplayer: 1.7.0
  - firstrunwizard: 2.6.0
  - logreader: 2.3.0
  - lookup_server_connector: 1.6.0
  - nextcloud_announcements: 1.6.0
  - notifications: 2.5.0
  - oauth2: 1.6.0
  - password_policy: 1.8.0
  - privacy: 1.2.0
  - provisioning_api: 1.8.0
  - recommendations: 0.6.0
  - serverinfo: 1.8.0
  - settings: true
  - sharebymail: 1.8.0
  - support: 1.1.0
  - survey_client: 1.6.0
  - systemtags: 1.7.0
  - tasks: 0.11.3
  - text: 2.0.0
  - theming: 1.8.0
  - twofactor_backupcodes: 1.7.0
  - twofactor_totp: 4.1.2
  - updatenotification: 1.8.0
  - viewer: 1.2.0
  - workflowengine: 1.7.0
Disabled:
  - calendar
  - camerarawpreviews
  - checksum
  - contacts
  - encryption
  - files_fulltextsearch
  - files_texteditor
  - fulltextsearch
  - gallery
  - photos
  - polls
  - spreed
  - user_ldap

Nextcloud configuration:

Config report

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***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "tempdirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "17.0.2.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "maintenance": true,
        "forcessl": true,
        "theme": "",
        "overwritewebroot": "",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "log_type": "owncloud",
        "logfile": "***REMOVED SENSITIVE VALUE***",
        "loglevel": "1",
        "logdateformat": "F d, Y H:i:s",
        "logtimezone": "***REMOVED SENSITIVE VALUE***",
        "log_query": false,
        "cron_log": true,
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "tls",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "587",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "forceSSLforSubdomains": true,
        "filelocking.enabled": "true",
        "updater.release.channel": "stable",
        "mysql.utf8mb4": true,
        "app_install_overwrite": [
            "apporder"
        ]
    }
}

Are you using external storage, if yes which one: no

Are you using encryption: no

Are you using an external user-backend, if yes which one: no

[kesselb]

rm -rf various old files/folders including /core, /3rdparty, /l10n, /ocs-provider, /ocs, /lib, /resources, /themes, /settings, /updater ...

You need to delete the shipped apps too :disappointed:
accessibility activity admin_audit cloud_federation_api comments dav encryption federatedfilesharing federation files files_external files_pdfviewer files_rightclick files_sharing files_trashbin files_versions files_videoplayer firstrunwizard logreader lookup_server_connector nextcloud_announcements notifications oauth2 password_policy photos privacy provisioning_api recommendations serverinfo settings sharebymail support survey_client systemtags text theming twofactor_backupcodes updatenotification user_ldap viewer workflowengine

[HackintoshHD]

You need to delete the shipped apps too disappointed

Okay, thank you @kesselb. I see that in Nextcloud's upgrade documentation. My scenario is looking quite similar to @testos77 's above - apps/workflowengine/appinfo/database.xml is present with an sha256sum of
99e6a4762b81a0e4d005b9b020a2c50b878e831593e09caaab9e439d79e79db8.

In case you can answer that: Am I right to assume that a simple rm -rf /srv/www/path/to/nextcloud/apps and a re-run of sudo -u DedicatedUser /srv/www/NextcloudDirectory/occ upgrade would result in a finally successful upgrade?

And, by the way, is there a (semi-)automatic way to preserve the information which apps were installed before the upgrade and to even reinstall them automatically after a successful upgrade (I assume an rm -rf of the app directory will normally require re-installing all apps manually, won't it?)?

[kesselb]

I see that in Nextcloud's upgrade documentation.

I was also surprised by this :rofl:

In case you can answer that: Am I right to assume that a simple rm -rf /srv/www/path/to/nextcloud/apps and a re-run of sudo -u DedicatedUser /srv/www/NextcloudDirectory/occ upgrade would result in a finally successful upgrade?

Deleting apps/workflowengine/appinfo/database.xml and occ upgrade should be enough.

And, by the way, is there a (semi-)automatic way to preserve the information which apps were installed before the upgrade and to even reinstall them automatically after a successful upgrade

I think nextcloud/vm had a script for that. A alternative way could be to use https://docs.nextcloud.com/server/18/admin_manual/apps_management.html#using-custom-app-directories.

[HackintoshHD]

I see that in Nextcloud's upgrade documentation.

I was also surprised by this :rofl:

... to be really honest, I even found an old comment of mine from probably a few upgrades ago in my bash scripted Nextcloud upgrade afterwards: :grimacing:

# Delete old directories
#
# commented out to preserve Nextcloud app installations:
# rm -rf /srv/www/NextcloudDirectory/apps

Deleting apps/workflowengine/appinfo/database.xml and occ upgrade should be enough.

Thank you very much! The server is now running Nextcloud 18 smoothly and I can confirm deleting apps/workflowengine/appinfo/database.xml and an occ upgrade were enough to resolve my 'broken instance' scenario from above.

[hydro-b]

Ouch. Download the zip and just extract the archive in place. Problem: The old database.xml has priority over the migrations. So only the old tables are created. That's why the updater move the files around.

Delete apps/workflowengine/appinfo/database.xml before running ./occ upgrade

^^ This worked for me

[kesselb]

@HackintoshHD If you are still looking for a way how to improve your update script. A list of shipped apps is available in core/shipped.json. It possible to parse the file with jq. E.g jq '.shippedApps' core/shipped.json to list only the shipped apps.

[rubensutter]

I tried the fix above, dropped the oc_flow? tables and added the CREATE TABLE like oc_flow_operations and got all oc_flow database tables with the neccessary columns, oc_flow_operations with "entity".

Restarting/Reloading the Webbrowser/Updater now results in
"DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'"
because the updater script unfixes the fix by changing oc_flow_operations back to "id, class, name, checks, operation" without the required entity column and stops again.

It seems like I still get a very strange database.xml file like:


dbname
true
false
utf8

<table>
    <name>*dbprefix*flow_checks</name>
    <declaration>
        <field>
            <name>id</name>
            <type>integer</type>
            <default>0</default>
            <notnull>true</notnull>
            <autoincrement>1</autoincrement>
            <length>4</length>
        </field>

        <field>
            <name>class</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>256</length>
        </field>
        <field>
            <name>operator</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>16</length>
        </field>
        <field>
            <name>value</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>
        <field>
            <name>hash</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>32</length>
        </field>

        <index>
            <name>flow_unique_hash</name>
            <unique>true</unique>
            <field>
                <name>hash</name>
            </field>
        </index>
    </declaration>
</table>

<table>
    <name>*dbprefix*flow_operations</name>
    <declaration>
        <field>
            <name>id</name>
            <type>integer</type>
            <default>0</default>
            <notnull>true</notnull>
            <autoincrement>1</autoincrement>
            <length>4</length>
        </field>

        <field>
            <name>class</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>256</length>
        </field>
        <field>
            <name>name</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>256</length>
        </field>
        <field>
            <name>checks</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>
        <field>
            <name>operation</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>
    </declaration>
</table>

[kesselb]

@rubensutter https://github.com/nextcloud/server/issues/18265#issuecomment-575810844

[rubensutter]

Fixed, up and running Version 18.

I just edited the database.xml file manually and it solved the problem adding the following to dbprefixflow_operations declaration at the bottom:

entity
text
true
256


events
clob
false

[rubensutter]

dbname
true
false
utf8

<table>
    <name>*dbprefix*flow_checks</name>
    <declaration>
        <field>
            <name>id</name>
            <type>integer</type>
            <default>0</default>
            <notnull>true</notnull>
            <autoincrement>1</autoincrement>
            <length>4</length>
        </field>

        <field>
            <name>class</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>256</length>
        </field>
        <field>
            <name>operator</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>16</length>
        </field>
        <field>
            <name>value</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>
        <field>
            <name>hash</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>32</length>
        </field>

        <index>
            <name>flow_unique_hash</name>
            <unique>true</unique>
            <field>
                <name>hash</name>
            </field>
        </index>
    </declaration>
</table>

<table>
    <name>*dbprefix*flow_operations</name>
    <declaration>
        <field>
            <name>id</name>
            <type>integer</type>
            <default>0</default>
            <notnull>true</notnull>
            <autoincrement>1</autoincrement>
            <length>4</length>
        </field>

        <field>
            <name>class</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>256</length>
        </field>
        <field>
            <name>name</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>256</length>
        </field>
        <field>
            <name>checks</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>
        <field>
            <name>operation</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>
        <field>
            <name>entity</name>
            <type>text</type>
            <notnull>true</notnull>
            <length>256</length>                
        </field>
        <field>
            <name>events</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>                
    </declaration>
</table>

[kesselb]

Thanks @rubensutter for sharing your solution. I minimized the comments because this way is not supported and will lead to unexpected results. Delete apps/workflowengine/appinfo/database.xml and occ upgrade is the right way to fix it.

[Sp1l]

@kesselb Does this mean that for me, as the FreeBSD packager, I must make sure the appinfo/database.xml is always removed prior to instructing the user to ./occ upgrade?

[Sp1l]

@kesselb Does this mean that for me, as the FreeBSD packager, I must make sure the appinfo/database.xml is always removed prior to instructing the user to ./occ upgrade?

The file apps/workflowengine/appinfo/databases.xml is not part of the 18.0.0 tarball. Thus it will be deleted during a regular pkg upgrade (first uninstalls old, then installs new).

The issue I encountered is that I used pkg add -f with a hand-rolled package on my system which foregoes the uninstall step.

[Blisk]

Thanks @rubensutter for sharing your solution. I minimized the comments because this way is not supported and will lead to unexpected results. Delete apps/workflowengine/appinfo/database.xml and occ upgrade is the right way to fix it.

No, it doesn't work I just tested it.
I didn't have apps/workflowengine/appinfo/database.xml
and when I try php occ upgrade
I get
DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'
Update failed

and I also already have these tables.
CREATE TABLE oc_flow_checks (
id int(11) NOT NULL AUTO_INCREMENT,
class varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
operator varchar(16) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
value longtext COLLATE utf8mb4_bin,
hash varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY flow_unique_hash (hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED

CREATE TABLE oc_flow_operations (
id int(11) NOT NULL AUTO_INCREMENT,
class varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
name varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
checks longtext COLLATE utf8mb4_bin,
operation longtext COLLATE utf8mb4_bin,
entity varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
events longtext COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED

CREATE TABLE oc_flow_operations_scope (
id bigint(20) NOT NULL AUTO_INCREMENT,
operation_id int(11) NOT NULL DEFAULT '0',
type int(11) NOT NULL DEFAULT '0',
value varchar(64) COLLATE utf8mb4_bin DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY flow_unique_scope (operation_id,type,value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED

[blizzz]

@Blisk something is not saying the truth. In your DB structure output, entity is present in the oc_flow_operations table. Perhaps the log was from before?

[Blisk]

Ok I tried again and this is error I get when I update from web page.
DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'

and here is a fresh log
http://zerobin.povej.net/?c1575b85e5640f77#rjdygVj3DvJ3+F7yyz4geqzRFWXGGRLPJFViH81WzOI=

and manually updating
occ upgrade
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
Set log level to debug
Updating database schema
Updated database
Updating ...
DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'
Update failed
Maintenance mode is kept active
Reset log level

[blizzz]

then, is the structure from https://github.com/nextcloud/server/issues/18265#issuecomment-578633715 of a different database perhaps? What happens if you run SELECT * from oc_flow_operations WHERE entity = '' against it?

[Blisk]

SELECT * from oc_flow_operations WHERE entity = ''

this is result
Poizvedba SQL: Dokumentacija

SELECT * from oc_flow_operations WHERE entity = ''
LIMIT 0, 25

MySQL je vrnil: Dokumentacija

1054 - Unknown column 'entity' in 'where clause'

If I put back files from 17.0.2 it works ok, but not on 18.0

[rubensutter]

If you run the browser updater in version 17.0.2 does a new file appear in apps/workflowengine/appinfo/ with the name "database.xml"?

[Blisk]

If you run the browser updater in version 17.0.2 does a new file appear in apps/workflowengine/appinfo/ with the name "database.xml"?

YES.
than I rename folder nextcloud to nextcloud17 and folder nextcloud18 to nextcloud and open again web browser and try to update and it doesn't work and there is no file apps/workflowengine/appinfo/ with the name "database.xml" in nextcloud 18 folder.

[Blisk]

Any idea why nextcloud 18 doesn't work? How to solve this problem?

[blizzz]

@Blisk do you override paths for app folders in the config.php?

[Blisk]

@Blisk do you override paths for app folders in the config.php?

NO.
Here is my config.php
http://zerobin.povej.net/?b4e082d68fe1edfd#6NyNg+THKxpdx+AopNxcgcrkacxOxUI91aZez8Y+lNA=

[blizzz]

Then, is PHP opcache configured correctly?

[Blisk]

Then, is PHP opcache configured correctly?

what do you mean?
I have deleted opcache from config file and it is the same also I think version 17 will not work.
Before I had version 16 and upgrade to 17 and all was ok, until I upgrade to 18 and not working.

[blizzz]

opcache is configured in the PHP configuration, not within nextcloud. See https://docs.nextcloud.com/server/18/admin_manual/installation/server_tuning.html?highlight=opcache#enable-php-opcache

But if it is not, you should see warnings in your admin settings.

[rubensutter]

I managed to update from 17.0.2 and somehow work myself through all the update failed loops. I'm running version 18 (updated) and for comparison a new installation of version 18 on the same machine.

I noticed that the updated version still has some issues eg with settings and apps that can be solved running the integrity rescan and taking care of "integrity failed" files, adjusting general settings (Php), verifying the correct permissions (folders+files) and security settings (OS). The apps are now shown as supposed and can be updated in the updated version. There are still database issues like oc_calendarobjects_props and oc_schedulingobjects, some big int issues and PHP-memory things for me to figure out to get everything working smoothly again. Looks like there is still more additional bug fixing left.

[Blisk]

opcache is configured in the PHP configuration, not within nextcloud. See https://docs.nextcloud.com/server/18/admin_manual/installation/server_tuning.html?highlight=opcache#enable-php-opcache

But if it is not, you should see warnings in your admin settings.
I have enabled opcache in php 7.3

and I have the same when I try to update to nextcloud 18
it looks like it is too buggy and I need to wait.

DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'

[blizzz]

@Blisk on 18, what does php occ migrations:status workflowengine yield?

[Blisk]

php occ migrations:status workflowengine
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

App: workflowengine
Version Table Name: oc_migrations
Migrations Namespace: OCAWorkflowEngineMigration
Migrations Directory: /var/www/html/nextcloud/apps/workflowengine/lib/Migration
Previous Version: 0
Current Version: 2000Date20190808074233
Next Version: Already at latest migration step
Latest Version: 2000Date20190808074233
Executed Migrations: 1
Executed Unavailable Migrations: 1
Available Migrations: 1
New Migrations: 1
Pending Migrations: None

php occ upgrade
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
Set log level to debug
Turned on maintenance mode
Updating database schema
Updated database
Updating ...
DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'
Update failed
Maintenance mode is kept active
Reset log level

and this is latest log
http://zerobin.povej.net/?8e1f6f599a6f6edb#F+3S44SWKeER7TPXf1L7b+YIBWockiWNpwwjK/RGrgI=

[blizzz]

can you try to run php occ migrations:execute workflowengine 2000Date20190808074233?

[Blisk]

can you try to run php occ migrations:execute workflowengine 2000Date20190808074233?

this solve a problem.
first I run this and after that I can upgrade in my web browser.
When I check settings in nextcloud it say there are missing some tables and after
occ db:add-missing-indices
it fixed all and it looks like now everything is OK in nextcloud 18
thank you

[Toakan]

To in-place fix this issue you can do the following.

Add the below to apps/workflowengine/lib/Migration/Version2000Date20190808074233.php around line 62. -- https://github.com/nextcloud/server/blob/0b710e582bc5615093c169270e1161dfdf37b0bf/apps/workflowengine/lib/Migration/Version2000Date20190808074233.php#L62

$table->addColumn('entity', Type::STRING, [
                                'notnull' => true,
                                'length' => 256,
                                'default' => '',
                        ]);

Add the below to apps/workflowengine/appinfo/database.xml under the flow*operations section.

 <field>
                                <name>entity</name>
                                <type>text</type>
                                <notnull>false</notnull>
                                <length>256</length>
          </field>

Then run the following commands to clean up.

sudo -u www-data php occ migrations:execute workflowengine 2000Date20190808074233
sudo -u www-data php occ db:add-missing-indices

This should then allow you to get through the upgrade process refreshing your web browser.

_Note, this may not apply to you.
I did run into further issues whereby I was missing a further "events" field, which was fixed via running the following SQL._

alter table nextcloud.oc_flow_operations  
    ADD events varchar(256) null;

[blizzz]

There must be no apps/workflowengine/appinfo/database.xml. If there is after replacing the files, you are upgrading wrongly, as was pointed out earlier in this issue (and as is documented).

[andresmoya]

what is the fix for this, stuck in the middle of an upgrade

[rubensutter]

Do you get an error?
What error message is shown?

You could have a look into your database and check if there is a database table called oc_flow_operations_scope and if not create it as shown above ´|`. Perhaps the occ upgrade then completes without further issues.

[javierllorente]

I get _update failed_ when upgrading from 17.0.2 to 18.0.1. Nevertheless, I can continue the upgrade on the Web interface.

Updating <workflowengine> ... Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'SELECTo.idFROMoc_flow_operationsoLEFT JOINoc_flow_operations_scopesONo.id=s.operation_idWHEREs.operation_id` IS NULL':

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_flow_operations_scope' doesn't exist
Update failed

[Erdloch]

I did just broke my Nextcloud installation while upgrading from 17.0.3 to 18.0.1 via the Web Updater.
It seems like the table oc_flow_operations exists, at least I can find it using the command

mysqlshow nextcloud

I'm getting the following error:

DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'

Any ideas on this?

[andresmoya]

what i did to by pass the error is add the column entity to the table oc_flow_operations manually, then run the upgrade again and it will succeed

[therealbstern]

I have a column entity but my files app is completely empty

apps/workflowengine/appinfo/app.php fails

{
 "reqId":"...",
 "level":3,
 "time":"...",
 "remoteAddr":"...",
 "user":"...",
 "app":"no app in context",
 "method":"GET",
 "url":"/nextcloud/index.php","
 message":{
  "Exception": "Error",
  "Message": "Call to undefined method OCA\\WorkflowEngine\\AppInfo\\Application::registerRuleListeners()",
  "Code":0,
  "Trace": "..."

I have the entity column in my oc_flow_operations table.

Rolling back to 17.0.2 was successful.

[KaiFra]

My NC installation also broke while upgrading from 17.0.3 to 18.0.1 with the webupdater, but it said the update succeeded.
I just have webspace with an url, so I don't really have access to the server (Just with ftp). My url gives me HTTP Error 500 (Page unreachable).
What do I do now? :)

[kesselb]

@therealbstern that's a different story but has probably the same reason. The "undefined" method registerRuleListeners has been added with Nextcloud 18. If you see the above in the logs a outdated version of the workflow app is installed. That usually happens if the archive with the nextcloud release is extracted into the existing folder. The correct way to do a manual upgrade is described here: https://github.com/nextcloud/documentation/blob/master/admin_manual/maintenance/manual_upgrade.rst.

@KaiFra https://help.nextcloud.com. Feel free to join this issue again if you see the same error in your nextcloud.log.

[therealbstern]

@kesselb Thanks, but I did follow the manual upgrade directions and didn't unpack over the old one, which definitely made rolling back easier. I noticed errors with a bunch of other apps after I upgraded to 18, so after playing whack-a-mole disabling apps for a long time, I finally gave up when I got an error about OCR not working even though I'd disabled it.

Rolling back to 17 worked.

If this is entirely due to apps not working with 18, then I guess I'll wait a while longer for people to update their apps before I try 18 again.

[kesselb]

@therealbstern Most people fail at step 9. Please make sure to not replace the workflowengine app with the new one.

[Erdloch]

what i did to by pass the error is add the column entity to the table oc_flow_operations manually, then run the upgrade again and it will succeed

That was the (obvious) solution, thanks a lot!

[KaiFra]

@KaiFra https://help.nextcloud.com. Feel free to join this issue again if you see the same error in your nextcloud.log.

I have 1950 matches for registerRuleListeners in nextcloud.log
I took a look at the manual upgrade instructions, but since I cannot run any commands, it is useless for me unfortunately

[kesselb]

All right. Please open a new issue for registerRuleListeners then. It probably has a similar root but this issue is about the missing database fields and is already to crowded.

[chkpnt]

Just crushed into the same issue. So what's the official solution?

As this issue is known since Dec., I'm wondering there isn't a warning in the release notes that a upgrade can (or will?) break the system.

[kesselb]

@chkpnt That's the recommended solution: https://help.nextcloud.com/t/read-this-before-you-update-to-nextcloud-18/68389

If you run into the missing column problem: The current status is that this issue appears if an unsupported way of upgrading your instance is used.

For registerRuleListeners warning: https://github.com/nextcloud/server/issues/19818

[chkpnt]

@kesselb Thank you, that indeed worked.

[therealbstern]

@kesselb Thanks, I added a +1 to that issue.

[vladaman]

I am having same issue. I suspect some of the table updates were not performed during previous upgrades.
I was updating from version 16, then 17 and then 18 one after the other, without even running nextcloud. So I suspect something was not done during those updates.

Funny thing is, that when I go to MySQL table directly and I add column entity manually and then I execute Update procedure, this column gets dropped. And the updater will throw an error that column entity is missing.

ALTER TABLE `oc_flow_operations` ADD `entity` VARCHAR(255) NOT NULL ;

This relates to upgrade to 18.0.2

[tonyshangrila]

@chkpnt That's the recommended solution: https://help.nextcloud.com/t/read-this-before-you-update-to-nextcloud-18/68389

If you run into the missing column problem: The current status is that this issue appears if an unsupported way of upgrading your instance is used.

For registerRuleListeners warning: #19818

I was grateful to find this thread, but for me, running a manual upgrade threw the exact same error:
DoctrineDBALExceptionInvalidFieldNameException: An exception occurred while executing 'UPDATE oc_flow_operations SET entity = ? WHERE entity = ''' with params ["OCA\WorkflowEngine\Entity\File"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'
Update failed

Going back to restore my 17.0.4 instance...

EDIT: Despite noting in this thread and others that manually adding the missing column may not work, gave it a go anyway, per MagelanM below. This workaround allowed me to upgrade, and with the web interface, no less.

[MagelanM]

I had the same problem while update to Nextcloud 18.0.2.
The update of the app workflowengine hangs.
The solution for me was to log into the database an add the column manually.
Steps for MariaDB:

• Connect over ssh with user root
  ssh <IP-Adress> -l root
• Open database
  mysql -uroot -p (use PW of user root)
• Open database:
  connect nextcloud
• Check table oc_flow_operations, if column entity is present or not
  describe oc_flow_operations;
• If not present, add the column
  alter table oc_flow_operations add entity varchar(256) default NULL;
  After adding the column, rerun the completition of the update.

[therealbstern]

@therealbstern Most people fail at step 9. Please make sure to not replace the workflowengine app with the new one.

I'm pretty sure when I did the manual update, occ went ahead and downloaded the new version automatically, so I didn't copy anything over. It's been long enough that I don't remember for sure, so I'll try an update to 18 shortly and report back.

[blizzz]

@MagelanM @vladaman @tonyshangrila did you all follow the upgrade instructions and ensured that the new files were not simply copied over the existing ones?

[tonyshangrila]

@MagelanM @vladaman @tonyshangrila did you all follow the upgrade instructions and ensured that the new files were not simply copied over the existing ones?

Yes, I did.

[blizzz]

@tonyshangrila can you provide your nextcloud.log from your upgrade attempt?

[tonyshangrila]

@blizzz - I'm sorry to say that I can't. :(

Upon taking a look at my logs, I noticed an entirely different SQL error that been thrown 2 or 3 times a second since I upgraded to 18. My log effectively gets flushed at 200 MB, and I don't have anything dated prior to 3/24 now.

Off to chase down that issue!

[MichaIng]

Just to mention it, I had an error during NC18 upgrade about the same database table: #18327
But it didn't lead to any broken instance or followup issues until now, so not sure if those are related at some point.

[therealbstern]

@kesselb I just tried again, 17.0.6 -> 18.0.7 in preparation for upgrading to 19, and the exact same thing happened. I followed step 9 in https://docs.nextcloud.com/server/18/admin_manual/maintenance/manual_upgrade.html to a T (i.e., didn't update the workflowengine myself).

This time, once I issued:

ALTER TABLE oc_flow_operations ADD COLUMN entity CHARACTER VARYING(256) NOT NULL DEFAULT ''

and re-running php occ upgrade, everything appeared to work (though my server stayed in maintenance mode).

So something must have changed in either 17 or 18 between now and then, but at least there is an upgrade path, even if it's a little rocky.

(ETA: I'm running Postgres.)

[arno01]

Here is my experience with upgrading Nextcloud from 17.0.9.2 to 18.0.9.1 (in docker). But with Postgresql 12.
It went almost smoothly.

# grep image: /srv/docker-compose.yml | grep nextcloud
    image: nextcloud:17-apache

# docker-compose stop nextcloud
# docker-compose exec -u postgres postgres12 pg_dump nextcloud > nextcloud-17.0.9.db
# rsync -avHx --progress /srv/data/nextcloud /srv/data/nextcloud-17.0.9.bkp

# vim /srv/docker-compose.yml

# grep image: /srv/docker-compose.yml | grep nextcloud
    image: nextcloud:18-apache

# docker-compose pull nextcloud
# docker-compose up -d nextcloud

# docker-compose logs -f --tail=222 nextcloud
Attaching to srv_nextcloud_1
nextcloud_1         | Initializing nextcloud 18.0.9.1 ...
nextcloud_1         | Upgrading nextcloud from 17.0.9.2 ...
nextcloud_1         | Initializing finished
nextcloud_1         | Nextcloud or one of the apps require upgrade - only a limited number of commands are available
nextcloud_1         | You may use your browser or the occ upgrade command to do the upgrade
nextcloud_1         | Set log level to debug
nextcloud_1         | Turned on maintenance mode
nextcloud_1         | Updating database schema
nextcloud_1         | Updated database
nextcloud_1         | Disabled incompatible app: gallery
nextcloud_1         | Updating <accessibility> ...
nextcloud_1         | Migrate old user config
nextcloud_1         | 
nextcloud_1         |  Starting ...
nextcloud_1         |     0/0 [>---------------------------]   0%
nextcloud_1         |  Starting ...
nextcloud_1         | 
nextcloud_1         | Updated <accessibility> to 1.4.0
nextcloud_1         | Updating <federatedfilesharing> ...
nextcloud_1         | Updated <federatedfilesharing> to 1.8.0
nextcloud_1         | Updating <files_pdfviewer> ...
nextcloud_1         | Updated <files_pdfviewer> to 1.7.0
nextcloud_1         | Updating <files_rightclick> ...
nextcloud_1         | Updated <files_rightclick> to 0.15.2
nextcloud_1         | Updating <files_videoplayer> ...
nextcloud_1         | Updated <files_videoplayer> to 1.7.0
nextcloud_1         | Updating <logreader> ...
nextcloud_1         | Updated <logreader> to 2.3.0
nextcloud_1         | Updating <password_policy> ...
nextcloud_1         | Updated <password_policy> to 1.8.0
nextcloud_1         | Updating <privacy> ...
nextcloud_1         | Updated <privacy> to 1.2.0
nextcloud_1         | Updating <provisioning_api> ...
nextcloud_1         | Updated <provisioning_api> to 1.8.0
nextcloud_1         | Updating <recommendations> ...
nextcloud_1         | Updated <recommendations> to 0.6.0
nextcloud_1         | Updating <serverinfo> ...
nextcloud_1         | Updated <serverinfo> to 1.8.0
nextcloud_1         | Updating <support> ...
nextcloud_1         | Updated <support> to 1.1.1
nextcloud_1         | Updating <survey_client> ...
nextcloud_1         | Updated <survey_client> to 1.6.0
nextcloud_1         | Updating <text> ...
nextcloud_1         | Updated <text> to 2.0.0
nextcloud_1         | Updating <twofactor_backupcodes> ...
nextcloud_1         | Updated <twofactor_backupcodes> to 1.7.0
nextcloud_1         | Updating <updatenotification> ...
nextcloud_1         | Updated <updatenotification> to 1.8.0
nextcloud_1         | Updating <federation> ...
nextcloud_1         | Updated <federation> to 1.8.0
nextcloud_1         | Updating <lookup_server_connector> ...
nextcloud_1         | Updated <lookup_server_connector> to 1.6.0
nextcloud_1         | Updating <oauth2> ...
nextcloud_1         | Updated <oauth2> to 1.6.0
nextcloud_1         | Updating <files> ...
nextcloud_1         | Updated <files> to 1.13.1
nextcloud_1         | Updating <activity> ...
nextcloud_1         | Updated <activity> to 2.11.0
nextcloud_1         | Updating <cloud_federation_api> ...
nextcloud_1         | Updated <cloud_federation_api> to 1.1.0
nextcloud_1         | Updating <dav> ...
nextcloud_1         | Fix broken values of calendar objects
nextcloud_1         | 
nextcloud_1         |     1/0 [->--------------------------]   0% Starting ...
nextcloud_1         | 
nextcloud_1         | Updated <dav> to 1.14.0
nextcloud_1         | Updating <files_external> ...
nextcloud_1         | Updated <files_external> to 1.9.0
nextcloud_1         | Updating <files_sharing> ...
nextcloud_1         | Updated <files_sharing> to 1.10.1
nextcloud_1         | Updating <files_trashbin> ...
nextcloud_1         | Updated <files_trashbin> to 1.8.0
nextcloud_1         | Updating <files_versions> ...
nextcloud_1         | Updated <files_versions> to 1.11.0
nextcloud_1         | Updating <sharebymail> ...
nextcloud_1         | Updated <sharebymail> to 1.8.0
nextcloud_1         | Updating <workflowengine> ...
nextcloud_1         | Updated <workflowengine> to 2.0.0
nextcloud_1         | Updating <comments> ...
nextcloud_1         | Updated <comments> to 1.8.0
nextcloud_1         | Updating <firstrunwizard> ...
nextcloud_1         | Updated <firstrunwizard> to 2.7.0
nextcloud_1         | Updating <nextcloud_announcements> ...
nextcloud_1         | Updated <nextcloud_announcements> to 1.7.0
nextcloud_1         | Updating <notifications> ...
nextcloud_1         | Updated <notifications> to 2.6.0
nextcloud_1         | Updating <systemtags> ...
nextcloud_1         | Updated <systemtags> to 1.8.0
nextcloud_1         | Updating <theming> ...
nextcloud_1         | Updated <theming> to 1.9.0
nextcloud_1         | Checking for update of app accessibility in appstore
nextcloud_1         | Checked for update of app "accessibility" in appstore 
nextcloud_1         | Checking for update of app activity in appstore
nextcloud_1         | Checked for update of app "activity" in appstore 
nextcloud_1         | Checking for update of app bruteforcesettings in appstore
nextcloud_1         | Update app bruteforcesettings from appstore
nextcloud_1         | Checked for update of app "bruteforcesettings" in appstore 
nextcloud_1         | Checking for update of app calendar in appstore
nextcloud_1         | Checked for update of app "calendar" in appstore 
nextcloud_1         | Checking for update of app cloud_federation_api in appstore
nextcloud_1         | Checked for update of app "cloud_federation_api" in appstore 
nextcloud_1         | Checking for update of app comments in appstore
nextcloud_1         | Checked for update of app "comments" in appstore 
nextcloud_1         | Checking for update of app contacts in appstore
nextcloud_1         | Update app contacts from appstore
nextcloud_1         | Checked for update of app "contacts" in appstore 
nextcloud_1         | Checking for update of app dav in appstore
nextcloud_1         | Checked for update of app "dav" in appstore 
nextcloud_1         | Checking for update of app federatedfilesharing in appstore
nextcloud_1         | Checked for update of app "federatedfilesharing" in appstore 
nextcloud_1         | Checking for update of app federation in appstore
nextcloud_1         | Checked for update of app "federation" in appstore 
nextcloud_1         | Checking for update of app files in appstore
nextcloud_1         | Checked for update of app "files" in appstore 
nextcloud_1         | Checking for update of app files_external in appstore
nextcloud_1         | Checked for update of app "files_external" in appstore 
nextcloud_1         | Checking for update of app files_pdfviewer in appstore
nextcloud_1         | Checked for update of app "files_pdfviewer" in appstore 
nextcloud_1         | Checking for update of app files_rightclick in appstore
nextcloud_1         | Checked for update of app "files_rightclick" in appstore 
nextcloud_1         | Checking for update of app files_sharing in appstore
nextcloud_1         | Checked for update of app "files_sharing" in appstore 
nextcloud_1         | Checking for update of app files_trashbin in appstore
nextcloud_1         | Checked for update of app "files_trashbin" in appstore 
nextcloud_1         | Checking for update of app files_versions in appstore
nextcloud_1         | Checked for update of app "files_versions" in appstore 
nextcloud_1         | Checking for update of app files_videoplayer in appstore
nextcloud_1         | Checked for update of app "files_videoplayer" in appstore 
nextcloud_1         | Checking for update of app firstrunwizard in appstore
nextcloud_1         | Checked for update of app "firstrunwizard" in appstore 
nextcloud_1         | Checking for update of app logreader in appstore
nextcloud_1         | Checked for update of app "logreader" in appstore 
nextcloud_1         | Checking for update of app lookup_server_connector in appstore
nextcloud_1         | Checked for update of app "lookup_server_connector" in appstore 
nextcloud_1         | Checking for update of app nextcloud_announcements in appstore
nextcloud_1         | Checked for update of app "nextcloud_announcements" in appstore 
nextcloud_1         | Checking for update of app notifications in appstore
nextcloud_1         | Checked for update of app "notifications" in appstore 
nextcloud_1         | Checking for update of app oauth2 in appstore
nextcloud_1         | Checked for update of app "oauth2" in appstore 
nextcloud_1         | Checking for update of app password_policy in appstore
nextcloud_1         | Checked for update of app "password_policy" in appstore 
nextcloud_1         | Checking for update of app privacy in appstore
nextcloud_1         | Checked for update of app "privacy" in appstore 
nextcloud_1         | Checking for update of app provisioning_api in appstore
nextcloud_1         | Checked for update of app "provisioning_api" in appstore 
nextcloud_1         | Checking for update of app recommendations in appstore
nextcloud_1         | Checked for update of app "recommendations" in appstore 
nextcloud_1         | Checking for update of app serverinfo in appstore
nextcloud_1         | Checked for update of app "serverinfo" in appstore 
nextcloud_1         | Checking for update of app settings in appstore
nextcloud_1         | Checked for update of app "settings" in appstore 
nextcloud_1         | Checking for update of app sharebymail in appstore
nextcloud_1         | Checked for update of app "sharebymail" in appstore 
nextcloud_1         | Checking for update of app support in appstore
nextcloud_1         | Checked for update of app "support" in appstore 
nextcloud_1         | Checking for update of app survey_client in appstore
nextcloud_1         | Checked for update of app "survey_client" in appstore 
nextcloud_1         | Checking for update of app systemtags in appstore
nextcloud_1         | Checked for update of app "systemtags" in appstore 
nextcloud_1         | Checking for update of app text in appstore
nextcloud_1         | Checked for update of app "text" in appstore 
nextcloud_1         | Checking for update of app theming in appstore
nextcloud_1         | Checked for update of app "theming" in appstore 
nextcloud_1         | Checking for update of app twofactor_backupcodes in appstore
nextcloud_1         | Checked for update of app "twofactor_backupcodes" in appstore 
nextcloud_1         | Checking for update of app twofactor_totp in appstore
nextcloud_1         | Update app twofactor_totp from appstore
nextcloud_1         | Checked for update of app "twofactor_totp" in appstore 
nextcloud_1         | Checking for update of app updatenotification in appstore
nextcloud_1         | Checked for update of app "updatenotification" in appstore 
nextcloud_1         | Checking for update of app viewer in appstore
nextcloud_1         | Checked for update of app "viewer" in appstore 
nextcloud_1         | Checking for update of app workflowengine in appstore
nextcloud_1         | Checked for update of app "workflowengine" in appstore 
nextcloud_1         | Checking for update of app gallery in appstore
nextcloud_1         | Checked for update of app "gallery" in appstore 
nextcloud_1         | Starting code integrity check...
nextcloud_1         | Finished code integrity check
nextcloud_1         | Update successful
nextcloud_1         | Turned off maintenance mode
nextcloud_1         | Reset log level
nextcloud_1         | The following apps have been disabled:
nextcloud_1         |  accessibility
nextcloud_1         |  activity
nextcloud_1         |  bruteforcesettings
nextcloud_1         |  cloud_federation_api
nextcloud_1         |  comments
nextcloud_1         |  contacts
nextcloud_1         |  dav
nextcloud_1         |  federatedfilesharing
nextcloud_1         |  federation
nextcloud_1         |  files
nextcloud_1         |  files_external
nextcloud_1         |  files_pdfviewer
nextcloud_1         |  files_rightclick
nextcloud_1         |  files_sharing
nextcloud_1         |  files_trashbin
nextcloud_1         |  files_versions
nextcloud_1         |  files_videoplayer
nextcloud_1         |  firstrunwizard
nextcloud_1         |  gallery
nextcloud_1         |  logreader
nextcloud_1         |  lookup_server_connector
nextcloud_1         |  nextcloud_announcements
nextcloud_1         |  notifications
nextcloud_1         |  oauth2
nextcloud_1         |  password_policy
nextcloud_1         |  privacy
nextcloud_1         |  provisioning_api
nextcloud_1         |  recommendations
nextcloud_1         |  serverinfo
nextcloud_1         |  sharebymail
nextcloud_1         |  support
nextcloud_1         |  survey_client
nextcloud_1         |  systemtags
nextcloud_1         |  text
nextcloud_1         |  theming
nextcloud_1         |  twofactor_backupcodes
nextcloud_1         |  twofactor_totp
nextcloud_1         |  updatenotification
nextcloud_1         |  workflowengine
nextcloud_1         |     0/0 [>---------------------------]   0%

Go to https://mynextcloudhost/settings/apps and make sure the Calendar & Contact apps are enabled & updated.

Then look at the Administration tab for the Security & setup warnings https://mynextcloudhost/settings/admin

The database is missing some indexes. Due to the fact that adding indexes on big tables could take some time they were not added automatically. By running "occ db:add-missing-indices" those missing indexes could be added manually while the instance keeps running. Once the indexes are added queries to those tables are usually much faster.

- Missing index "calendarobject_calid_index" in table "oc_calendarobjects_props".
- Missing index "schedulobj_principuri_index" in table "oc_schedulingobjects".

Fix:

# docker-compose exec -u www-data nextcloud php occ db:add-missing-indices
Check indices of the share table.
Check indices of the filecache table.
Check indices of the twofactor_providers table.
Check indices of the login_flow_v2 table.
Check indices of the whats_new table.
Check indices of the cards table.
Check indices of the cards_properties table.
Check indices of the calendarobjects_props table.
Adding calendarobject_calid_index index to the calendarobjects_props table, this can take some time...
calendarobjects_props table updated successfully.
Check indices of the schedulingobjects table.
Adding schedulobj_principuri_index index to the schedulingobjects table, this can take some time...
schedulingobjects table updated successfully.

[MichaIng]

Jep that is expected. The admin panel would have explained that missing indices as well as missing columns are not added automatically by the updater, as this can be very time consuming. So the following two steps can actually always be appended, when the possible additional offline/maintenance time is okay:

occ db:add-missing-columns
occ db:add-missing-indices

_including the sudo/docker/PHP prefix of course._

[Maingron]

https://github.com/nextcloud/server/issues/18265#issuecomment-562903648 this one worked for me, but I had to create varchar(256) (not varchar[256]) using SQlite.

ALTER TABLE oc_flow_operations
ADD entity varchar(256) DEFAULT NULL;

[Tie-fighter]

I have the same issue upgrading from 19.0.4.2 to 20.0.0.9

I have the column

dbadmin@localhost [nextcloud]> DESCRIBE oc_flow_operations;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| class     | varchar(256) | NO   |     | NULL    |                |
| name      | varchar(256) | NO   |     | NULL    |                |
| checks    | longtext     | YES  |     | NULL    |                |
| operation | longtext     | YES  |     | NULL    |                |
| entity    | varchar(256) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

but I still receive the error :/

Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'UPDATE `oc_flow_operations` SET `entity` = ? WHERE `entity` = ''' with params ["OCA\\WorkflowEngine\\Entity\\File"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity' in 'where clause'
Update failed
Maintenance mode is kept active
Resetting log level
root@nextcloud:~ #

PS: This https://github.com/nextcloud/server/issues/19058#issuecomment-576765983 and https://github.com/nextcloud/server/issues/19058#issuecomment-576824120 resolved my issues.

[nordurljosahvida]

Hi, I too have had to manually add the entity column in mariadb, now however I get multiple of these errors a second:

Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'SELECT `class`, `entity`, `events` FROM `oc_flow_operations` WHERE `events` <> ? GROUP BY `class`, `entity`, `events`' with params ["[]"]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'events' in 'field list'

Already ran repair, add-missing-indices and add-missing-columns.

Everything seems to be working, I can only see the error from the logs.

Thanks

[MichaIng]

Is apps/workflowengine/appinfo/databases.xml present in your case? If so, remove it.
Although the "events" field should be there, at least it is in my case. Can you check your database for it?

mysql -e 'describe nextcloud.oc_flow_operations'

-u root p might be required for authentication.