Update to 20.0.2 failed: oc_activity_mq not empty - solved

Hi everyone,

I’m new to this forum and rather new to unix so please excuse if there is information missing. Please let me know if you need further details.

The webupdater shows:

Aktualisierung auf 20.0.2

Doctrine\DBAL\Exception\NotNullConstraintViolationException: An exception occurred while executing ‘INSERT INTO oc_activity_mq (mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams, object_type, object_id) SELECT mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams, object_type, object_id FROM __temp__oc_activity_mq’: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: oc_activity_mq.amq_subjectparams2

Detaillierte Protokollmeldungen

Update vorbereiten

Log-Level auf “debug” gesetzt

Wartungsmodus eingeschaltet

Reparaturschritt: Repair MySQL collation

Reparaturinformation: Not a mysql database -> nothing to do

Reparaturschritt: Repair SQLite autoincrement

Reparaturschritt: Copy data from accounts table when migrating from ownCloud

Reparaturschritt: Drop account terms table when migrating from ownCloud

Das Datenbankschema wird aktualisiert

Datenbank aktualisiert

Doctrine\DBAL\Exception\NotNullConstraintViolationException: An exception occurred while executing ‘INSERT INTO oc_activity_mq (mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams, object_type, object_id) SELECT mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams, object_type, object_id FROM __temp__oc_activity_mq’: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: oc_activity_mq.amq_subjectparams2

Das Update ist fehlgeschlagen. Bitte melde dieses Problem an die Nextcloud Community.

Thank you for your support
Patrick

[/details]

Nextcloud version 20.0.2):
Operating system and version Arch:
Apache or nginx version (Apache/2.4.46 (Unix):
PHP version PHP 7.4.12:

The issue you are facing:
Upgrade to 20.0.2 fails with message
“Doctrine\DBAL\Exception\NotNullConstraintViolationException: An exception occurred while executing ‘INSERT INTO oc_activity_mq (mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams, object_type, object_id) SELECT mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams, object_type, object_id FROM __temp__oc_activity_mq’: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: oc_activity_mq.amq_subjectparams2”

Is this the first time you’ve seen this error? Y:

Steps to replicate it:

  1. performed full system upgrade but can be reproduced by executing occ upgrade or after disabling the maintenance mode in the webupdater

The output of your Nextcloud log in Admin > Logging:

{"reqId":"CDA8VNxI21wJAcQKJicr","level":1,"time":"2020-11-21T20:10:30+00:00","remoteAddr":"2.204.122.194","user":"--","app":"no app in context","method":"GET","url":"/nextcloud/remote.php/dav/avatars/patbla/128.png","message":{"Exception":"Symfony\\Component\\Routing\\Exception\\RouteNotFoundException","Message":"Unable to generate a URL for the named route \"photos.page.index\" as such route does not exist.","Code":0,"Trace":[{"file":"/usr/share/webapps/nextcloud/lib/private/Route/Router.php","line":370,"function":"generate","class":"Symfony\\Component\\Routing\\Generator\\UrlGenerator","type":"->","args":["photos.page.index",[],1]},{"file":"/usr/share/webapps/nextcloud/lib/private/Route/CachingRouter.php","line":60,"function":"generate","class":"OC\\Route\\Router","type":"->","args":["photos.page.index",[],false]},{"file":"/usr/share/webapps/nextcloud/lib/private/URLGenerator.php","line":83,"function":"generate","class":"OC\\Route\\CachingRouter","type":"->","args":["photos.page.index",[]]},{"file":"/usr/share/webapps/nextcloud/lib/private/NavigationManager.php","line":282,"function":"linkToRoute","class":"OC\\URLGenerator","type":"->","args":["photos.page.index"]},{"file":"/usr/share/webapps/nextcloud/lib/private/NavigationManager.php","line":108,"function":"init","class":"OC\\NavigationManager","type":"->","args":[]},{"file":"/usr/share/webapps/nextcloud/apps/theming/lib/ThemingDefaults.php","line":183,"function":"getAll","class":"OC\\NavigationManager","type":"->","args":["guest"]},{"file":"/usr/share/webapps/nextcloud/lib/private/legacy/OC_Defaults.php","line":263,"function":"getShortFooter","class":"OCA\\Theming\\ThemingDefaults","type":"->","args":[]},{"file":"/usr/share/webapps/nextcloud/lib/public/Defaults.php","line":169,"function":"getLongFooter","class":"OC_Defaults","type":"->","args":[]},{"file":"/usr/share/webapps/nextcloud/core/templates/layout.guest.php","line":55,"function":"getLongFooter","class":"OCP\\Defaults","type":"->","args":[]},{"file":"/usr/share/webapps/nextcloud/lib/private/Template/Base.php","line":178,"args":["/usr/share/webapps/nextcloud/core/templates/layout.guest.php"],"function":"include"},{"file":"/usr/share/webapps/nextcloud/lib/private/Template/Base.php","line":150,"function":"load","class":"OC\\Template\\Base","type":"->","args":["/usr/share/webapps/nextcloud/core/templates/layout.guest.php",null]},{"file":"/usr/share/webapps/nextcloud/lib/private/legacy/OC_Template.php","line":180,"function":"fetchPage","class":"OC\\Template\\Base","type":"->","args":[null]},{"file":"/usr/share/webapps/nextcloud/lib/private/legacy/OC_Template.php","line":211,"function":"fetchPage","class":"OC_Template","type":"->","args":[null]},{"file":"/usr/share/webapps/nextcloud/lib/private/Template/Base.php","line":132,"function":"fetchPage","class":"OC_Template","type":"->","args":[]},{"file":"/usr/share/webapps/nextcloud/lib/private/legacy/OC_Template.php","line":299,"function":"printPage","class":"OC\\Template\\Base","type":"->","args":[]},{"file":"/usr/share/webapps/nextcloud/remote.php","line":84,"function":"printErrorPage","class":"OC_Template","type":"::","args":["Service unavailable","",503]},{"file":"/usr/share/webapps/nextcloud/remote.php","line":169,"function":"handleException","args":[{"__class__":"RemoteException"}]}],"File":"/usr/share/webapps/nextcloud/3rdparty/symfony/routing/Generator/UrlGenerator.php","Line":143,"CustomMessage":"--"},"userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"CDA8VNxI21wJAcQKJicr","level":3,"time":"2020-11-21T20:10:30+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"GET","url":"/nextcloud/remote.php/dav/avatars/patbla/128.png","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"ZHWpoEjvep2CmIieJMb8","level":3,"time":"2020-11-21T20:10:43+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"PROPFIND","url":"/nextcloud/remote.php/dav/files/patbla/","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"SSqtYyudMjY6OrZ3Ezit","level":3,"time":"2020-11-21T20:10:44+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"PROPFIND","url":"/nextcloud/remote.php/dav/files/tinhel/","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"9Ts68YlSabhyjGSt9imT","level":3,"time":"2020-11-21T20:10:50+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"GET","url":"/nextcloud/status.php","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"rh10lkhlFUpbiZOEqv40","level":3,"time":"2020-11-21T20:10:50+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"GET","url":"/nextcloud/status.php","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"u0jUGBoeedUMWjrZVU1K","level":3,"time":"2020-11-21T20:10:50+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"GET","url":"/nextcloud/status.php","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"5U0zJ1vj6ycaI5itfl7p","level":3,"time":"2020-11-21T20:10:50+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"PROPFIND","url":"/nextcloud/remote.php/dav/files/digicam/","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"U5N3rGLi51JNWDSUbdvN","level":3,"time":"2020-11-21T20:10:50+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"PROPFIND","url":"/nextcloud/remote.php/dav/files/DPSG/","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}
{"reqId":"y0jlGgF3PzTCmCaKKCjM","level":3,"time":"2020-11-21T20:10:50+00:00","remoteAddr":"2.204.122.194","user":"--","app":"PHP","method":"PROPFIND","url":"/nextcloud/remote.php/dav/files/wolke/","message":"You are using a fallback implementation of the intl extension. Installing the native one is highly recommended instead. at /usr/share/webapps/nextcloud/3rdparty/patchwork/utf8/src/Patchwork/Utf8/Bootup/intl.php#18","userAgent":"Mozilla/5.0 (Linux) mirall/3.0.3git (Nextcloud)","version":"19.0.3.1"}

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'instanceid' => 'ocur2xtw0wz6',
  'passwordsalt' => '*',
  'secret' => '*',
  'trusted_domains' => 
  array (
    0 => '192.168.178.20',
    1 => '*',
    2 => '*',
  ),
  'datadirectory' => '/mnt/data/nextcloud_data',
  'overwrite.cli.url' => 'http://192.168.178.20/nextcloud',
  'dbtype' => 'sqlite3',
  'version' => '19.0.3.1',
  'logtimezone' => 'UTC',
  'installed' => true,
  'theme' => '',
  'loglevel' => 0,
  'maintenance' => false,
  'updater.secret' => '*',
  'app_install_overwrite' => 
  array (
    0 => 'calendar',
    1 => 'cms_pico',
  ),
  'memcache.local' => '\\OC\\Memcache\\APCu',
);

The output of your Apache/nginx/system log in /var/log/____:

[Sat Nov 21 13:26:18.279267 2020] [ssl:warn] [pid 274] AH01873: Init: Session Cache is not configured [hint: SSLSessionCache]
[Sat Nov 21 13:26:19.488543 2020] [mpm_prefork:notice] [pid 274] AH00163: Apache/2.4.46 (Unix) OpenSSL/1.1.1h PHP/7.4.12 configured -- resuming normal operations
[Sat Nov 21 13:26:19.488983 2020] [core:notice] [pid 274] AH00094: Command line: '/usr/bin/httpd -D FOREGROUND'
Sat Nov 21 13:31:14 2020 (289): Error Cannot kill process 292: Success!
[Sat Nov 21 14:59:12.780949 2020] [php7:error] [pid 283] [client 91.241.19.84:42892] script '/mnt/data/http/index.php' not found or unable to stat
[Sat Nov 21 18:43:02.607759 2020] [negotiation:error] [pid 686] [client 195.37.190.77:52574] AH00690: no acceptable variant: /usr/share/httpd/error/HTTP_NOT_FOUND.html.var
[Sat Nov 21 19:16:07.605202 2020] [mpm_prefork:notice] [pid 274] AH00170: caught SIGWINCH, shutting down gracefully
[Sat Nov 21 19:16:10.617167 2020] [ssl:warn] [pid 269] AH01873: Init: Session Cache is not configured [hint: SSLSessionCache]
[Sat Nov 21 19:16:12.044198 2020] [mpm_prefork:notice] [pid 269] AH00163: Apache/2.4.46 (Unix) OpenSSL/1.1.1h PHP/7.4.12 configured -- resuming normal operations
[Sat Nov 21 19:16:12.055997 2020] [core:notice] [pid 269] AH00094: Command line: '/usr/bin/httpd -D FOREGROUND'

I just got this error on one of my instances as well.
Running occ upgrade I get the following:

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
Setting log level to debug
Updating database schema
Updated database
Updating <activity> ...
Doctrine\DBAL\Exception\NotNullConstraintViolationException: An exception occurred while executing 'ALTER TABLE activity_mq ADD amq_subjectparams2 TEXT NOT NULL':

SQLSTATE[23502]: Not null violation: 7 ERROR:  column "amq_subjectparams2" contains null values
Update failed
Maintenance mode is kept active
Resetting log level

The database is postgresql and everything is running inside docker containers using a slightly customised version of the docker-compose file found in the examples folder on github.

Any clues would be highly appreciated!

I did some digging and found that the error was caused by the table activity_mq was not empty, thus adding the new NULL column named amq_subjectparams2 failed.

I deleted the two rows sitting in the table and ran the upgrade again. I hope that won’t cause other major issues though :slight_smile:

Hi Graffen,

I received the same hint from a friend and resolved it that way:
stopped apache: sudo systemctl stop httpd
stopped all processes potentially accessing the database: sudo killall httpd
changed into the directory where the database is
created a backup
started sqlite: sudo sqlite3
opened db: .open owncloud.db
checked entries in faulty table: SELECT * FROM oc_activity_mq;
which was not zero
deleted entry: DELETE FROM oc_activity_mq;
checked that it’s now empty: SELECT * FROM oc_activity_mq;
which it was
exited sqlite: .quit
started apache: sudo systemctl start httpd
ran webinstaller - and it succeeded!

issue is resolved

There is a bug report: https://github.com/nextcloud/server/issues/24326