Server: Upgrade fails with - Column name "oc_flow_operations.entity" is "NotNull", but has empty string or null as default

[dbtech]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Steps to reproduce

1. start occ upgrade

Expected behaviour

occ upgrade should be completed.

Actual behaviour

InvalidArgumentException: Column name oc_flow_operations.entity is NotNull, but has empty string or null as default.

Server configuration

Operating system:
Ubuntu Linux 20.04

Web server:
N/A (not used)

Database:
postgresql 11

PHP version:
7.4

Nextcloud version: (see Nextcloud admin page)
20.0.0

Updated from an older Nextcloud/ownCloud or fresh install:
Fresh install with manual DB upgrade 17.0.3.1

Where did you install Nextcloud from:
https://download.nextcloud.com/server/releases/nextcloud-20.0.0.zip

Signing status:

Signing status

Results

• core
  
  
  
  • INVALID_HASH
    
    
    
    
    
    • lib/private/DB/MigrationService.php
      
      
      

      REMARK

    
    
    
  
  

In order to proceed with the migration I had to PATCH the script above. It simply reverts the commit #5023084 (Sept. 7, 2020).
I cannot reproduce the original list any more: It must have been empty though.

List of activated apps:

App list

• accessibility: 1.6.0
• activity: 2.13.1
• cloud_federation_api: 1.3.0
• comments: 1.10.0
• contactsinteraction: 1.1.0
• dashboard: 7.0.0
• dav: 1.16.0
• federatedfilesharing: 1.10.1
• federation: 1.10.1
• files: 1.15.0
• files_pdfviewer: 2.0.1
• files_rightclick: 0.17.0
• files_sharing: 1.12.0
• files_trashbin: 1.10.1
• files_versions: 1.13.0
• files_videoplayer: 1.9.0
• firstrunwizard: 2.9.0
• logreader: 2.5.0
• lookup_server_connector: 1.8.0
• nextcloud_announcements: 1.9.0
• notifications: 2.8.0
• oauth2: 1.8.0
• password_policy: 1.10.1
• photos: 1.2.0
• privacy: 1.4.0
• provisioning_api: 1.10.0
• recommendations: 0.8.0
• serverinfo: 1.10.0
• settings: 1.2.0
• sharebymail: 1.10.0
• support: 1.3.0
• survey_client: 1.8.0
• systemtags: 1.10.0
• text: 3.1.0
• theming: 1.11.0
• twofactor_backupcodes: 1.9.0
• updatenotification: 1.10.0
• user_status: 1.0.0
• viewer: 1.4.0
• weather_status: 1.0.0
• workflowengine: 2.2.0
  

Nextcloud configuration:

Config report

{
"system": {
"instanceid": "REMOVED SENSITIVE VALUE",
"passwordsalt": "REMOVED SENSITIVE VALUE",
"secret": "REMOVED SENSITIVE VALUE",
"trusted_domains": [
"REMOVED SENSITIVE VALUE"
],
"datadirectory": "REMOVED SENSITIVE VALUE",
"dbtype": "pgsql",
"version": "20.0.0.9",
"overwrite.cli.url": "REMOVED SENSITIVE VALUE",
"dbname": "REMOVED SENSITIVE VALUE",
"dbhost": "REMOVED SENSITIVE VALUE",
"dbport": "",
"dbtableprefix": "oc_",
"dbuser": "REMOVED SENSITIVE VALUE",
"dbpassword": "REMOVED SENSITIVE VALUE",
"installed": true,
"updater.secret": "REMOVED SENSITIVE VALUE",
"maintenance": false,
"theme": "",
"loglevel": 2
}
}

Are you using external storage, if yes which one: local/smb/sftp/...
no

Are you using encryption: yes/no
no

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...
no

Client configuration

Browser:
N/A

Operating system:
N/A

Logs

Web server error log

Web server error log

N/A (not used)

Nextcloud log (data/nextcloud.log)

Nextcloud log

{"reqId":"rLfAg75MvoDInc4Ag522","level":3,"time":"2020-10-04T11:01:02+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","message":{"Exception":"InvalidArgumentException","Message":"Column name \"oc_flow_operations\".\"entity\" is NotNull, but has empty string or null as default.","Code":0,"Trace":[{"file":"/var/www/nextcloud/lib/private/DB/MigrationService.php","line":484,"function":"ensureOracleIdentifierLengthLimit","class":"OC\DB\MigrationService","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/MigrationService.php","line":414,"function":"executeStep","class":"OC\DB\MigrationService","type":"->"},{"file":"/var/www/nextcloud/lib/private/legacy/OC_App.php","line":976,"function":"migrate","class":"OC\DB\MigrationService","type":"->"},{"file":"/var/www/nextcloud/lib/private/Updater.php","line":375,"function":"updateApp","class":"OC_App","type":"::","args":["* sensitive parameters replaced *"]},{"file":"/var/www/nextcloud/lib/private/Updater.php","line":254,"function":"doAppUpgrade","class":"OC\Updater","type":"->"},{"file":"/var/www/nextcloud/lib/private/Updater.php","line":130,"function":"doUpgrade","class":"OC\Updater","type":"->"},{"file":"/var/www/nextcloud/core/Command/Upgrade.php","line":255,"function":"upgrade","class":"OC\Updater","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Command/Command.php","line":255,"function":"execute","class":"OC\Core\Command\Upgrade","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Application.php","line":1000,"function":"run","class":"Symfony\Component\Console\Command\Command","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Application.php","line":271,"function":"doRunCommand","class":"Symfony\Component\Console\Application","type":"->"},{"file":"/var/www/nextcloud/3rdparty/symfony/console/Application.php","line":147,"function":"doRun","class":"Symfony\Component\Console\Application","type":"->"},{"file":"/var/www/nextcloud/lib/private/Console/Application.php","line":215,"function":"run","class":"Symfony\Component\Console\Application","type":"->"},{"file":"/var/www/nextcloud/console.php","line":100,"function":"run","class":"OC\Console\Application","type":"->"},{"file":"/var/www/nextcloud/occ","line":11,"args":["/var/www/nextcloud/console.php"],"function":"require_once"}],"File":"/var/www/nextcloud/lib/private/DB/MigrationService.php","Line":519,"CustomMessage":"--"},"userAgent":"--","version":"20.0.0"}

Browser log

Browser log

N/A (not used)

[darian-heede]

I solved this issue. First off, my setup is probably different than @dbtech as I'm running nextcloud and the postgres database in docker containers. As the described issue lies within a table in the postgres database, the fix should work for every kind of setup though.

The error message implies that there is an issue with the default of column entity in table oc_flow_operations. It turns out the column entity was missing from the table entirely. Hence, I just added the column to the table. The following steps solved the issue:

1. Log into the database container running psql:
   sudo docker exec -it <nextcloud-database> psql -U <database-user>
   This step is obviously specific to my type of setup but should be easily adapted for any other.
2. Connect to the nextcloud database (should be default):
   \c <database-name>
3. Check if the column is available:
   SELECT entity FROM oc_flow_operations;
   If this command returns
   ERROR: column "entity" does not exist
4. Add the column to the table

ALTER TABLE oc_flow_operations
ADD COLUMN entity VARCHAR NOT NULL;

1. Run the Update on the nextcloud webpage.

I hope this helps.

[dbtech]

Sorry @darian-heede, you misunderstand the issue. The error occurs after the "entity" column has been added with a default value of the empty string, i.e >''<. This is part of the database migration. The lib/private/DB/MigrationService.php script will verify if the default value is not null and not empty. This cannot succeed and an exception is raised. The column definition and the verification logic contradict each other and at least one of them should be corrected.

[kbuc]

I just got this error while upgrading from 17.0.10 to 18.0.10. Also with me, the column entity did not exist (but the error message was the same). Added it with:

alter table oc_flow_operations add column entity character varying(256) not null;

then occ update finished the job.

[dbtech]

Please be aware that the reported error is unrelated to any versions below 20.0.0. The error may occur only after Sep 7, 2020, due to the commit 5023084. For this reason I have strong doubts that @kbuc could have experienced the very same error in the versions 17 and 18.

[kbuc]

@dbtech Yes, I experienced it today when updating from 17.0.9. It first updated to 17.0.10, then it wanted to update to 18.0.10 and this happened.
As it seems, 17.0.10 release has today's date, so it might contain the mentioned commit?

[Cybso]

Confirmed for upgrading from 17.0.10 to 18.0.10. Adding the column manually resolved the issue.

[dbtech]

FYI: today's version of 17.0.10 does NOT contain the offending commit. Actually it would be weird if it did. I assume that the release was extracted today with the correct tag. Of course, I do not doubt that there might be similar errors in earlier releases. The reported error is brand new.

[Cybso]

@dbtech I did the updates a few hours ago. First from 17.0.2 to 17.0.10, which went fine, and then to 18.0.10, which produced that error.

[dbtech]

OK, I see a long series of comments above. Two of them refer "this" or "that" error during a migration to release 17.0.10. What was the error message exactly?

[Cybso]

I've closed the console window already, but I've googled for:

"InvalidArgumentException: Column name "oc_flow_operations"."entity" is NotNull, but has empty string or null as default"

EDIT: Oh, and it was a migration FROM 17.0.10 to 18.0.10. The migration TO 17.0.10 was fine.

[mtapoja]

Confirmed for upgrading from 17.0.10 to 18.0.10. Adding the column manually resolved the issue.

Same here, adding the column seems to fix the issue and next manual upgrade succeeded.

[tobyguelly]

"Confirmed for upgrading from 17.0.10 to 18.0.10. Adding the column manually resolved the issue."
Same here, adding the column seems to fix the issue and next manual upgrade succeeded.

How can I add the column manually?

[Cybso]

How can I add the column manually?

Connect to the database via mysql and enter:

alter table oc_flow_operations add column entity character varying(256) not null;

[tobyguelly]

How can I add the column manually?

Connect to the database via mysql and enter:

alter table oc_flow_operations add column entity character varying(256) not null;

Thank you, I think this has fixed the issue completely. I was just wondering, because it did not work before, but this command made it work...

[dbtech]

"The error may occur only after Sep 7, 2020, due to the commit #5023084.". Based on your feedback I have found some unexpected consequences of the error. I has turned out that the commit had been merged to the stable branches stable18 and stable19 as well. This has backported the error in earlier stable releases. It seems to be a more serious handling error than the original. I should say: unprecedented. So I need to rephrase the limitations: The error occurs in releases 18, 19 and 20 if, and only if, the release was installed after Sept 7, 2020.

[loganto]

Same issue with sqlite DB migrating 17.10 -> 18.0.10.
I was able to fix it by opening my sqlite db:

sqlite3 owncloud.db

and running this query:
ALTER TABLE oc_flow_operations ADD COLUMN entity VARCHAR;

[iKf8ui]

Hello everyone,
I have the same problem, but I am a bit of a newby and I'm stuck.
I'm running nextcloudpi and the error also occured today during upgrade from 17.0.10 to 18.0.10. The original error message:

InvalidArgumentException: Column name "oc_flow_operations"."entity" is NotNull, but has empty string or null as default.

In mysql (mariadb) I entered the suggested command:

alter table oc_flow_operations add column entity character varying(256) not null;

This returning the following:

Error 1046 (3D000): No dadatbase selected

What do I need to do?

[tobyguelly]

Select the right database first.

msql -u nameofyourdb -p
at the next line your db password

Then use the command:

alter table oc_flow_operations add column entity character varying(256) not null;

This fixes the issue when upgrading from 17.0.10 to 18.0.10 completely.

[davidus05]

How can I add the column manually?

Connect to the database via mysql and enter:

alter table oc_flow_operations add column entity character varying(256) not null;

Thanks, this fixed it for me!

[SteveinPhx]

I hit this same bug when upgrading from 17.0.10 to 18.0.10 today.
This fixed it for me also, thank you.

[iKf8ui]

Thanks, it finally worked!

[Bodenseematze]

I have the same problem when trying to update from 17.0.10 to 18.0.10 (using mariadb).
But the problem was _not_ solved after I manually added the missing column as suggested.

After that I got a new error:

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 'db3.oc_flow_operations_scope' doesn't exist

Initially I executed the update process through the web GUI - but after getting the above error and after altering the SQL table oc_flow_operations I did an "./occ maintenance:repair" - is that correct?

EDIT: no, it's not correct - I now did an "./occ upgrade" and that worked... ;-)

[AceSlash]

Same error from NC 17.0.10 to 18.0.10 with postgresql.

Running ALTER TABLE oc_flow_operations ADD COLUMN entity VARCHAR NOT NULL; after the error and then rerunning the occ upgrade did fix it.

Since this is the latest release of NC 17, you can expect many people to perform this update, I think a minor release to correct this would be a good idea.

[AceSlash]

I just realized that this is issue is closed (!!!), why is that? Does the Nextcloud team expect all users to manually run an undocumented SQL command when migrating from NC17 to NC18?

This has to be a mistake...

[davidus05]

I just realized that this is issue is closed (!!!), why is that? Does the Nextcloud team expect all users to manually run an undocumented SQL command when migrating from NC17 to NC18?

This has to be a mistake...

+1 this should be reopened, as this issue is still existing!

[Cybso]

@Dennis1993 The comment from @iKf8ui three days ago was related to the workaround. The problem still exists and breaks updates to 18.0.10.

[redtux]

Just here to confirm the issue when upgrading from 17.0.10 to 18.0.10, and to confirm the workaround. :heart:

[nmeyerhans]

Another "+1". I upgraded from 17.0.10.1 to 18.0.10.2 today and encountered the following:

# sudo -u www-data 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                                   
Updating database schema                            
Updated database                                      
Updating <workflowengine> ...                        
InvalidArgumentException: Column name "oc_flow_operations"."entity" is NotNull, but has empty string or null as default.
Update failed                                        
Maintenance mode is kept active                        
Reset log level                                         

I fixed it by running
alter table oc_flow_operations add column entity character varying(256) not null;
as suggested earlier in this issue.

[dbtech]

All! Please understand that this thread is a place for bug reports. It is definitely not a self aid forum where workarounds are welcome. The "fix" in some comments above does not eliminate the bug and the frustration of all future users. If you have a solution then create a pull request where the code is corrected.

[stadeli]

I had the same error when upgrading from nextcloud v17 to v18 through the webinterface.
I used virtualmin > Edit Database > select db > Manage Database > under Edit Table select oc_flow_operations > next to "Add Field of type" select varchar, then click green button > Field name: entity, Type width 256 click Create.
That did the trick. Just reload the Nextcloud webpage with the error message and the installation will go through.

[hcanning]

For what it's worth ALTER TABLE oc_flow_operations ADD COLUMN entity VARCHAR NULL; worked for me for a 17.10 to 18.10 upgrade

alter table oc_flow_operations add column entity character varying(256) not null; ...did not work
ALTER TABLE oc_flow_operations ADD COLUMN entity VARCHAR NOT NULL; ..did not work

[kthoma2]

I hit the same error as others mentioned during 17.0.10.1 -> 18.0.10.2

"File":"/config/www/nextcloud/lib/private/DB/MigrationService.php","Line":519
Updater::failure: InvalidArgumentException: Column name \"oc_flow_operations\".\"entity\" is NotNull, but has empty string or null as default."

when adding a column the validation method does not consider the case of an empty table. which should allow a null default & NonNull scenario
if ($thing->getNotnull() && $thing->getDefault() === '' && $sourceTable instanceof Table && !$sourceTable->hasColumn($thing->getName())) {
throw new \InvalidArgumentException('Column name "' . $table->getName() . '"."' . $thing->getName() . '" is NotNull, but has empty string or null as default.');
}

So I modified /config/www/nextcloud/apps/workflowengine/lib/Migration/Version2000Date20190808074233.php
to set the default value of the entity column to space ' ' instead of blank ''; in lieu modifying MigrationService

Then re-ran with occ upgrade and success.

I figured its better than allowing nulls into that column, but the integrity check on that file flags in the GUI post-migration.

[astie85]

I have the same issue with my NextcloudPi. But i cannot connect to my database using mysql because i dont know what username and password i should use. Can somebody point me in the right direction?

My Nextcloud is stuck because he wants to finish the upgrade.

[astie85]

I have the same issue with my NextcloudPi. But i cannot connect to my database using mysql because i dont know what username and password i should use. Can somebody point me in the right direction?

My Nextcloud is stuck because he wants to finish the upgrade.

Nobody can help me?

[nmeyerhans]

I have the same issue with my NextcloudPi. But i cannot connect to my database using mysql because i dont know what username and password i should use. Can somebody point me in the right direction?
My Nextcloud is stuck because he wants to finish the upgrade.

Nobody can help me?

Your issue is unrelated to the described here. You provided the database credentials when you set up nextcloud. Please follow up on the nextcloud forums for help recovering access to your database.

[astie85]

I have the same issue with my NextcloudPi. But i cannot connect to my database using mysql because i dont know what username and password i should use. Can somebody point me in the right direction?
My Nextcloud is stuck because he wants to finish the upgrade.

Nobody can help me?

Your issue is unrelated to the described here. You provided the database credentials when you set up nextcloud. Please follow up on the nextcloud forums for help recovering access to your database.

Thank you for pointing me in the right direction. Nextcloud is up and running again!

[romichg]

Just hit the exact same issue doing an upgrade from the 17.0.10 to 18.0.10. Thanks to https://github.com/nextcloud/server/issues/23174#issuecomment-705682472 I was able to fix it.
Hopeful that this would be fixed soon, its been over a month since the original report....

[seregasoft]

postgresql
Unfortunately not working for me:

# SELECT entity FROM oc_flow_operations;

ERROR:  column "entity" does not exist
LINE 1: SELECT entity FROM oc_flow_operations;
               ^
# ALTER TABLE oc_flow_operations ADD COLUMN entity VARCHAR NOT NULL;

ERROR:  column "entity" contains null values

Just make
ALTER TABLE oc_flow_operations ADD COLUMN entity VARCHAR;

[Ektorus]

Same here, got the error while updating from 17.0.10 to 18.0.10:
InvalidArgumentException: Column name "oc_flow_operations"."entity" is NotNull, but has empty string or null as default. Update failed

Thanks to https://github.com/nextcloud/server/issues/23174#issuecomment-705551052 I was able to resolve the issue.

[fermulator]

Sigh another "me too" - this needs to be fixed in the 17.0.xx release branch (even though it is unsupported) so that the DB can be primed properly for the upgrade path.

[grisu48]

Also affected by this bug but could fix it by adding the missing column manually as suggested:

 ALTER TABLE oc_flow_operations ADD COLUMN `entity` varchar(256) NOT NULL;

[CaptPicardFR]

ALTER TABLE oc_flow_operations ADD COLUMN entity VARCHAR; > error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

alter table oc_flow_operations add column entity character varying(256) not null; > OK !!!! MANY THANKS UPGRADE ok

[Kinamo]

Also affected by this bug but could fix it by adding the missing column manually as suggested:

 ALTER TABLE oc_flow_operations ADD COLUMN `entity` varchar(256) NOT NULL;

Works perfectly for MySQL / MariaDB!

[ChadGnad]

I hit the same error as others mentioned during 17.0.10.1 -> 18.0.10.2

"File":"/config/www/nextcloud/lib/private/DB/MigrationService.php","Line":519
Updater::failure: InvalidArgumentException: Column name "oc_flow_operations"."entity" is NotNull, but has empty string or null as default."

when adding a column the validation method does not consider the case of an empty table. which should allow a null default & NonNull scenario
if ($thing->getNotnull() && $thing->getDefault() === '' && $sourceTable instanceof Table && !$sourceTable->hasColumn($thing->getName())) {
throw new \InvalidArgumentException('Column name "' . $table->getName() . '"."' . $thing->getName() . '" is NotNull, but has empty string or null as default.');
}

So I modified /config/www/nextcloud/apps/workflowengine/lib/Migration/Version2000Date20190808074233.php
to set the default value of the entity column to space ' ' instead of blank ''; in lieu modifying MigrationService

Then re-ran with occ upgrade and success.

I figured its better than allowing nulls into that column, but the integrity check on that file flags in the GUI post-migration.

This...this worked for me...it looks like someone removed the " and added ' ' but there was no space between. so I found and did

[Gaertjen]

I'm sorry, I'm new here and do not understand very well, how you fixed it. I can't enter any commands? I just got the web interface to update the nextcloudsoftware, when I update to 18.0.11 it shows

InvalidArgumentException: Column name "nc_5047_flow_operations"."entity" is NotNull, but has empty string or null as default.

But I don't understand how to fix it. I'm really sorry, can anybody help me?

[blizzz]

apply the changes from https://github.com/nextcloud/server/pull/24315/files and rerun the upgrade