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

[ArtificialOwl]

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 😬

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

[editedweb]

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:

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]

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