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 https://doc.owncloud.org/server/10.3/admin_manual/maintenance/manual_upgrade.html
  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+

** https://doc.owncloud.org/server/10.2/admin_manual/installation/system_requirements.html 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