Upgrading 10.0.10 to 10.3.2 leads to foreign key error

Server
1

Steps to reproduce

  1. start with an installation of OC 10.0.10
  2. follow all the instructions in ownCloud Documentation Overview
  3. the error occurs when running “occ upgrade”

Expected behaviour

occ upgrade should do its work…

Actual behaviour

occ upgrade fails with this error message:

2020-01-02T12:56:13+00:00 Updating database schema
2020-01-02T12:56:14+00:00 Doctrine\DBAL\Exception\ConnectionException: An exception occurred while executing 'ALTER TABLE oc_persistent_locks ADD CONSTRAINT FK_F0C3D55B93CB796C FOREIGN KEY (file_id) REFERENCES oc_filecache (fileid) ON DELETE CASCADE':

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'web207_1'@'localhost' for table 'oc_filecache'
2020-01-02T12:56:14+00:00 Update failed
2020-01-02T12:56:14+00:00 Maintenance mode is kept active
2020-01-02T12:56:14+00:00 Reset log level

Server configuration

Operating system:
Linux 4.9.0-0.bpo.3-amd64 #1 SMP Debian 4.9.30-2+deb9u5~bpo8+1 (2017-09-28) x86_64 GNU/Linux

Web server: Apache 2.4

Database: MySQL
SELECT version()
5.7.25-log

PHP version: PHP7.1

ownCloud version: updading from 10.0.10 to 10.3.2

Updated from an older ownCloud or fresh install: Updating

Where did you install ownCloud from: tarball

Signing status (ownCloud 9.0 and above):

Login as admin user into your ownCloud and access 
http://example.com/index.php/settings/integrity/failed 
paste the results into https://gist.github.com/ and puth the link here.

No way, OC is down.

The content of config/config.php:

ownCloud is in maintenance mode - no app have been loaded

{
    "system": {
        "instanceid": "ochk76ickmv0",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "www.smyy.de",
            "smyy.de",
            "mycloud.smyy.de"
        ],
        "datadirectory": "\/home\/DATA\/smycloud\/data",
        "overwrite.cli.url": "http:\/\/mycloud.smyy.de",
        "dbtype": "mysql",
        "version": "10.0.10.4",
        "dbname": "web207_db1",
        "dbhost": "127.0.0.1:3306",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "UTC",
        "installed": true,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "loglevel": 2,
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpauth": 1,
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpsecure": "ssl",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "maintenance": true,
        "htaccess.RewriteBase": "\/",
        "singleuser": false,
        "theme": "",
        "updater.secret": "***REMOVED SENSITIVE VALUE***"
    }
}

List of activated apps:

php7.1 ./occ app:list
ownCloud is in maintenance mode - no app have been loaded

Enabled:
  - comments: 0.3.0
  - dav: 0.4.0
  - federatedfilesharing: 0.3.1
  - federation: 0.1.0
  - files: 1.5.1
  - files_external: 0.7.1
  - files_sharing: 0.11.0
  - files_trashbin: 0.9.1
  - files_versions: 1.3.0
  - firstrunwizard: 1.1
  - notifications: 0.3.5
  - provisioning_api: 0.5.0
  - systemtags: 0.3.0
  - updatenotification: 0.2.1
Disabled:
  - activity
  - audioplayer
  - calendar
  - configreport
  - encryption
  - external
  - files_pdfviewer
  - files_texteditor
  - files_videoplayer
  - gallery
  - market
  - music
  - notes
  - ownbackup
  - templateeditor
  - user_external

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

Client configuration n/a

Logs

Web server error log

n/a

ownCloud log (data/owncloud.log)

providing update.log instead:

data> cat update.log
[info] Execution of upgrade:detect command started
[info] ownCloud 10.0.9.5 found
[info] Execution of upgrade:detect command stopped. Exit code is 0
[info] Execution of upgrade:checkSystem command started
[info] Execution of upgrade:checkSystem command stopped. Exit code is 0
[info] Execution of upgrade:checkpoint command started
[info] Execution of upgrade:checkpoint command stopped. Exit code is 0
[info] Execution of upgrade:detect command started
[info] ownCloud 10.0.9.5 found
[info] Execution of upgrade:detect command stopped. Exit code is 0
[info] Execution of upgrade:executeCoreUpgradeScripts command started
[debug] Replacing 3rdparty
[debug] Replacing config
[debug] Replacing core
[debug] Replacing l10n
[debug] Replacing lib
[debug] Replacing ocs
[debug] Replacing ocs-provider
[debug] Replacing resources
[debug] Replacing settings
[debug] Replacing .htaccess
[debug] Replacing .mailmap
[debug] Replacing .tag
[debug] Replacing .user.ini
[debug] Replacing AUTHORS
[debug] Replacing CHANGELOG.md
[debug] Replacing console.php
[debug] Replacing COPYING
[debug] Replacing cron.php
[debug] Replacing db_structure.xml
[debug] Replacing index.html
[debug] Replacing index.php
[debug] Replacing indie.json
[debug] Replacing occ
[debug] Replacing public.php
[debug] Replacing remote.php
[debug] Replacing robots.txt
[debug] Replacing status.php
[debug] Replacing version.php
[info] Execution of upgrade:executeCoreUpgradeScripts command stopped. Exit code is 0
[info] Execution of upgrade:executeCoreUpgradeScripts command started
[debug] Replacing 3rdparty
[debug] Replacing config
[debug] Replacing core
[debug] Replacing l10n
[debug] Replacing lib
[debug] Replacing ocs
[debug] Replacing ocs-provider
[debug] Replacing resources
[debug] Replacing settings
[debug] Replacing .htaccess
[debug] Replacing .mailmap
[debug] Replacing .tag
[debug] Replacing .user.ini
[debug] Replacing AUTHORS
[debug] Replacing CHANGELOG.md
[debug] Replacing console.php
[debug] Replacing COPYING
[debug] Replacing cron.php
[debug] Replacing db_structure.xml
[debug] Replacing index.html
[debug] Replacing index.php
[debug] Replacing indie.json
[debug] Replacing occ
[debug] Replacing public.php
[debug] Replacing remote.php
[debug] Replacing robots.txt
[debug] Replacing status.php
[debug] Replacing version.php
[info] Execution of upgrade:executeCoreUpgradeScripts command stopped. Exit code is 0
[info] Execution of upgrade:restartWebServer command started
[info] Execution of upgrade:restartWebServer command stopped. Exit code is 0
[info] Execution of upgrade:postUpgradeCleanup command started
[debug] Moving updater/app
[debug] Moving updater/application.php
[debug] Moving updater/box.json
[debug] Moving updater/composer.json
[debug] Moving updater/composer.lock
[debug] Moving updater/CONTRIBUTING.md
[debug] Moving updater/COPYING-AGPL
[debug] Moving updater/index.php
[debug] Moving updater/pub
[debug] Moving updater/src
[debug] Moving updater/vendor
[debug] Moving updater/README.md
[debug] Moving updater/.travis.yml
[debug] Moving updater/.scrutinizer.yml
[debug] Moving updater/nbproject
[info] Execution of upgrade:postUpgradeCleanup command stopped. Exit code is 0
[info] Execution of upgrade:detect command started
[info] ownCloud 10.0.10.4 found
[info] Execution of upgrade:detect command stopped. Exit code is 0
[info] Execution of upgrade:detect command started
[info] ownCloud 10.0.10.4 found
[info] Execution of upgrade:detect command stopped. Exit code is 0

Additional note:

MySQL or MariaDB 5.5+

** ownCloud Documentation Overview however says:

MySQL 8+ or MariaDB 10+ (RECOMMENDED)

  • This is not mentioned in the release notes!
  • However, the behavior of ALTER TABLE with FOREIGN KEY Constraints does not seem to have changed significantly between MySQL 5.6 and MySQL 8.0
2

Hey,

this sounds to me that the database user which is assigned to ownCloud don’t have enough permissions to run this required database command.

Maybe you can do a web search for this error to see how to assign the permission to execute this command to your database user?

2 Likes