search

nextcloud / server

☁️ Nextcloud server, a safe home for all your data
https://nextcloud.com
GNU Affero General Public License v3.0
27.21k stars 4.04k forks source link

Issue while upgrading to 18 Beta1 (SQL, related to Flow) #18265

Closed ArtificialOwl closed 3 years ago

ArtificialOwl commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

@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.

editedweb commented 4 years ago

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_flowoperations 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 commented 4 years ago

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

hapee commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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:

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

rullzer commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

@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 commented 4 years ago

@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 commented 4 years ago

@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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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

ivdok commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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

testos77 commented 4 years ago

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 ... 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 '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 commented 4 years ago

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

testos77 commented 4 years ago

Yes sha256sum 99e6a4762b81a0e4d005b9b020a2c50b878e831593e09caaab9e439d79e79db8 database.xml`

kesselb commented 4 years ago

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 commented 4 years ago

For this installation it's too late now.

kesselb commented 4 years ago

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 commented 4 years ago

Thx a lot server is running v18 now.

HackintoshHD commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

@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 commented 4 years ago

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 "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'" 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: <?xml version="1.0" encoding="ISO-8859-1" ?>

*dbname* true false utf8 *dbprefix*flow_checks id integer 0 true 1 4 class text true 256 operator text true 16 value clob false hash text true 32 flow_unique_hash true hash
*dbprefix*flow_operations id integer 0 true 1 4 class text true 256 name text true 256 checks clob false operation clob false
kesselb commented 4 years ago

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

rubensutter commented 4 years ago

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 
        <field>
            <name>events</name>
            <type>clob</type>
            <notnull>false</notnull>
        </field>
rubensutter commented 4 years ago

<?xml version="1.0" encoding="ISO-8859-1" ?>

*dbname* true false utf8 *dbprefix*flow_checks id integer 0 true 1 4 class text true 256 operator text true 16 value clob false hash text true 32 flow_unique_hash true hash
*dbprefix*flow_operations id integer 0 true 1 4 class text true 256 name text true 256 checks clob false operation clob false entity text true 256 events clob false
kesselb commented 4 years ago

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 commented 4 years ago

@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 commented 4 years ago

@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 commented 4 years ago

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 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

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 commented 4 years ago

@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 commented 4 years ago

Ok I tried again and this is error I get when I update from web page. 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'

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 ... 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 Reset log level

blizzz commented 4 years ago

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 commented 4 years ago

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