Upgrade from 10.0.10.4 to 10.2.1.4 - foreign key error

Server
1

Steps to reproduce

1.Upgrade package on Ubuntu 18.04 10.0.10.4 to 10.2.1.4
2.Run roc upgrade
3.

Expected behaviour

Upgrade my inplace OC install to the 10.2.1.4 version

Actual behavior

Preparing update
Set log level to debug
Turned on maintenance mode
Updating database schema
Doctrine\DBAL\Exception\DriverException: 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[HY000]: General error: 1005 Can’t create table owncloud.#sql-523_24b (errno: 150 “Foreign key constraint is incorrectly formed”)

Server configuration

Operating system: Ubuntu 18.04

Web server: Apache

Database: Mysql

PHP version: 7.2

ownCloud version: 10.0.10.4

Updated from an older ownCloud or fresh install:

fresh install

Where did you install ownCloud from:

Owncloud repository via ubuntu

Signing status (ownCloud 9.0 and above):

unable to access page

The content of config/config.php:

<?php

$CONFIG = array (

’updatechecker’ => false,

’instanceid’ => **'ocREMOVED

’trusted_domains’ =>

array (

0 => ‘localhost’ ,

1 => REMOVED

2 => ‘owncloud’ ,

),

’passwordsalt’ => REMOVED

’secret’ => REMOVED

’datadirectory’ => ‘/var/www/owncloud/data’ ,

’overwrite.cli.url’ => REMOVED

’dbtype’ => ‘mysql’ ,

’version’ => ‘10.0.10.4’ ,

’dbname’ => ‘owncloud’ ,

’dbhost’ => ‘localhost’ ,

’dbtableprefix’ => ‘oc_’ ,

’dbuser’ => REMOVED

’dbpassword’ => REMOVED

’logtimezone’ => ‘UTC’ ,

’installed’ => true,

’memcache.local’ => ‘\OC\Memcache\APCu’ ,

’memcache.locking’ => ‘\OC\Memcache\APCu’ ,

’filelocking.enabled’ => true,

’mail_smtpmode’ => ‘smtp’ ,

’mail_from_address’ => REMOVED

’mail_domain’ => REMOVED

’mail_smtpauthtype’ => ‘LOGIN’ ,

’mail_smtpauth’ => 1,

’mail_smtphost’ => REMOVED

’mail_smtpname’ => REMOVED

’mail_smtppassword’ => REMOVED

’mail_smtpport’ => ‘25’ ,

’maintenance’ => false,

’loglevel’ => 2,

’ldapIgnoreNamingRules’ => false,

’singleuser’ => false,

’theme’ => ‘’ ,

);

List of activated apps:

Enabled:

  • activity: 2.5.0
  • comments: 0.3.0
  • configreport: 0.1.1
  • dav: 0.4.0
  • federatedfilesharing: 0.3.1
  • federation: 0.1.0
  • files: 1.5.2
  • files_antivirus: 0.15.1
  • files_external: 0.7.1
  • files_pdfviewer: 0.11.0
  • files_sharing: 0.11.0
  • files_texteditor: 2.3.0
  • files_trashbin: 0.9.1
  • files_versions: 1.3.0
  • files_videoplayer: 0.9.8
  • firstrunwizard: 1.1
  • gallery: 16.1.1
  • market: 0.4.0
  • notifications: 0.3.5
  • provisioning_api: 0.5.0
  • systemtags: 0.3.0
  • templateeditor: 0.4.0
  • updatenotification: 0.2.1
    Disabled:
  • encryption
  • external
  • user_external

Are you using encryption: no

ownCloud log (data/owncloud.log)

{“reqId”:“88sd6zq5ogyKSjVhpuh0”,“level”:3,“time”:“2019-07-17T15:29:21+00:00”,“remoteAddr”:"",“user”:"–",“app”:“PHP”,“method”:"–",“url”:"–",“message”:“Undefined variable: self at /var/www/owncloud/core/Command/Upgrade.php#245”}

{“reqId”:“88sd6zq5ogyKSjVhpuh0”,“level”:0,“time”:“2019-07-17T15:29:21+00:00”,“remoteAddr”:"",“user”:"–",“app”:“core”,“method”:"–",“url”:"–",“message”:“starting upgrade from 10.0.10.4 to 10.2.1.4”}

{“reqId”:“88sd6zq5ogyKSjVhpuh0”,“level”:3,“time”:“2019-07-17T15:29:24+00:00”,“remoteAddr”:"",“user”:"–",“app”:“PHP”,“method”:"–",“url”:"–",“message”:“file_put_contents(/var/www/owncloud/data/.htaccess): failed to open stream: Permission denied at /var/www/owncloud/lib/private/Setup.php#541”}

{“reqId”:“88sd6zq5ogyKSjVhpuh0”,“level”:0,“time”:“2019-07-17T15:29:24+00:00”,“remoteAddr”:"",“user”:"–",“app”:“core”,“method”:"–",“url”:"–",“message”:“Migrations: starting 20180607072706 from app core”}

{“reqId”:“88sd6zq5ogyKSjVhpuh0”,“level”:3,“time”:“2019-07-17T15:29:24+00:00”,“remoteAddr”:"",“user”:"–",“app”:“core”,“method”:"–",“url”:"–",“message”:“Exception: {“Exception”:“Doctrine\\DBAL\\Exception\\DriverException”,“Message”:“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’:\n\nSQLSTATE[HY000]: General error: 1005 Can’t create table owncloud.#sql-523_25c (errno: 150 \“Foreign key constraint is incorrectly formed\”)”,“Code”:0,“Trace”:”#0 \/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php(128): Doctrine\\DBAL\\Driver\\AbstractMySQLDriver->convertException(‘An exception oc…’, Object(Doctrine\\DBAL\\Driver\\PDOException))\n#1 \/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php(964): Doctrine\\DBAL\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Object(Doctrine\\DBAL\\Driver\\PDOException), ‘ALTER TABLE oc_…’)\n#2 \/var\/www\/owncloud\/lib\/private\/DB\/Migrator.php(198): Doctrine\\DBAL\\Connection->query(‘ALTER TABLE oc_…’)\n#3 \/var\/www\/owncloud\/lib\/private\/DB\/Migrator.php(80): OC\\DB\\Migrator->applySchema(Object(Doctrine\\DBAL\\Schema\\Schema))\n#4 \/var\/www\/owncloud\/lib\/private\/DB\/Connection.php(434): OC\\DB\\Migrator->migrate(Object(Doctrine\\DBAL\\Schema\\Schema))\n#5 \/var\/www\/owncloud\/lib\/private\/DB\/MigrationService.php(412): OC\\DB\\Connection->migrateToSchema(Object(Doctrine\\DBAL\\Schema\\Schema))\n#6 \/var\/www\/owncloud\/lib\/private\/DB\/MigrationService.php(366): OC\\DB\\MigrationService->executeStep(20180607072706)\n#7 \/var\/www\/owncloud\/lib\/private\/Updater.php(307): OC\\DB\\MigrationService->migrate()\n#8 \/var\/www\/owncloud\/lib\/private\/Updater.php(261): OC\\Updater->doCoreUpgrade()\n#9 \/var\/www\/owncloud\/lib\/private\/Updater.php(112): OC\\Updater->doUpgrade(‘10.2.1.4’, ‘10.0.10.4’)\n#10 \/var\/www\/owncloud\/core\/Command\/Upgrade.php(261): OC\\Updater->upgrade()\n#11 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Command\/Command.php(255): OC\\Core\\Command\\Upgrade->execute(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#12 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(960): Symfony\\Component\\Console\\Command\\Command->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#13 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(255): Symfony\\Component\\Console\\Application->doRunCommand(Object(OC\\Core\\Command\\Upgrade), Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#14 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(148): Symfony\\Component\\Console\\Application->doRun(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#15 \/var\/www\/owncloud\/lib\/private\/Console\/Application.php(161): Symfony\\Component\\Console\\Application->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#16 \/var\/www\/owncloud\/console.php(106): OC\\Console\\Application->run()\n#17 \/var\/www\/owncloud\/occ(11): require_once(’\/var\/www\/ownclo…’)\n#18 {main}",“File”:"\/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/AbstractMySQLDriver.php",“Line”:115}"}

{“reqId”:“88sd6zq5ogyKSjVhpuh0”,“level”:3,“time”:“2019-07-17T15:29:24+00:00”,“remoteAddr”:"",“user”:"–",“app”:“core”,“method”:"–",“url”:"–",“message”:“Caused by: {“Exception”:“Doctrine\\DBAL\\Driver\\PDOException”,“Message”:“SQLSTATE[HY000]: General error: 1005 Can’t create table owncloud.#sql-523_25c (errno: 150 \“Foreign key constraint is incorrectly formed\”)”,“Code”:“HY000”,“Trace”:”#0 \/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php(954): Doctrine\\DBAL\\Driver\\PDOConnection->query(‘ALTER TABLE oc_…’)\n#1 \/var\/www\/owncloud\/lib\/private\/DB\/Migrator.php(198): Doctrine\\DBAL\\Connection->query(‘ALTER TABLE oc_…’)\n#2 \/var\/www\/owncloud\/lib\/private\/DB\/Migrator.php(80): OC\\DB\\Migrator->applySchema(Object(Doctrine\\DBAL\\Schema\\Schema))\n#3 \/var\/www\/owncloud\/lib\/private\/DB\/Connection.php(434): OC\\DB\\Migrator->migrate(Object(Doctrine\\DBAL\\Schema\\Schema))\n#4 \/var\/www\/owncloud\/lib\/private\/DB\/MigrationService.php(412): OC\\DB\\Connection->migrateToSchema(Object(Doctrine\\DBAL\\Schema\\Schema))\n#5 \/var\/www\/owncloud\/lib\/private\/DB\/MigrationService.php(366): OC\\DB\\MigrationService->executeStep(20180607072706)\n#6 \/var\/www\/owncloud\/lib\/private\/Updater.php(307): OC\\DB\\MigrationService->migrate()\n#7 \/var\/www\/owncloud\/lib\/private\/Updater.php(261): OC\\Updater->doCoreUpgrade()\n#8 \/var\/www\/owncloud\/lib\/private\/Updater.php(112): OC\\Updater->doUpgrade(‘10.2.1.4’, ‘10.0.10.4’)\n#9 \/var\/www\/owncloud\/core\/Command\/Upgrade.php(261): OC\\Updater->upgrade()\n#10 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Command\/Command.php(255): OC\\Core\\Command\\Upgrade->execute(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#11 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(960): Symfony\\Component\\Console\\Command\\Command->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#12 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(255): Symfony\\Component\\Console\\Application->doRunCommand(Object(OC\\Core\\Command\\Upgrade), Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#13 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(148): Symfony\\Component\\Console\\Application->doRun(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#14 \/var\/www\/owncloud\/lib\/private\/Console\/Application.php(161): Symfony\\Component\\Console\\Application->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#15 \/var\/www\/owncloud\/console.php(106): OC\\Console\\Application->run()\n#16 \/var\/www\/owncloud\/occ(11): require_once(’\/var\/www\/ownclo…’)\n#17 {main}",“File”:"\/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php",“Line”:106}"}

{“reqId”:“88sd6zq5ogyKSjVhpuh0”,“level”:3,“time”:“2019-07-17T15:29:24+00:00”,“remoteAddr”:"",“user”:"–",“app”:“core”,“method”:"–",“url”:"–",“message”:“Caused by: {“Exception”:“PDOException”,“Message”:“SQLSTATE[HY000]: General error: 1005 Can’t create table owncloud.#sql-523_25c (errno: 150 \“Foreign key constraint is incorrectly formed\”)”,“Code”:“HY000”,“Trace”:”#0 \/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php(104): PDO->query(‘ALTER TABLE oc_…’)\n#1 \/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php(954): Doctrine\\DBAL\\Driver\\PDOConnection->query(‘ALTER TABLE oc_…’)\n#2 \/var\/www\/owncloud\/lib\/private\/DB\/Migrator.php(198): Doctrine\\DBAL\\Connection->query(‘ALTER TABLE oc_…’)\n#3 \/var\/www\/owncloud\/lib\/private\/DB\/Migrator.php(80): OC\\DB\\Migrator->applySchema(Object(Doctrine\\DBAL\\Schema\\Schema))\n#4 \/var\/www\/owncloud\/lib\/private\/DB\/Connection.php(434): OC\\DB\\Migrator->migrate(Object(Doctrine\\DBAL\\Schema\\Schema))\n#5 \/var\/www\/owncloud\/lib\/private\/DB\/MigrationService.php(412): OC\\DB\\Connection->migrateToSchema(Object(Doctrine\\DBAL\\Schema\\Schema))\n#6 \/var\/www\/owncloud\/lib\/private\/DB\/MigrationService.php(366): OC\\DB\\MigrationService->executeStep(20180607072706)\n#7 \/var\/www\/owncloud\/lib\/private\/Updater.php(307): OC\\DB\\MigrationService->migrate()\n#8 \/var\/www\/owncloud\/lib\/private\/Updater.php(261): OC\\Updater->doCoreUpgrade()\n#9 \/var\/www\/owncloud\/lib\/private\/Updater.php(112): OC\\Updater->doUpgrade(‘10.2.1.4’, ‘10.0.10.4’)\n#10 \/var\/www\/owncloud\/core\/Command\/Upgrade.php(261): OC\\Updater->upgrade()\n#11 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Command\/Command.php(255): OC\\Core\\Command\\Upgrade->execute(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#12 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(960): Symfony\\Component\\Console\\Command\\Command->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#13 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(255): Symfony\\Component\\Console\\Application->doRunCommand(Object(OC\\Core\\Command\\Upgrade), Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#14 \/var\/www\/owncloud\/lib\/composer\/symfony\/console\/Application.php(148): Symfony\\Component\\Console\\Application->doRun(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#15 \/var\/www\/owncloud\/lib\/private\/Console\/Application.php(161): Symfony\\Component\\Console\\Application->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#16 \/var\/www\/owncloud\/console.php(106): OC\\Console\\Application->run()\n#17 \/var\/www\/owncloud\/occ(11): require_once(’\/var\/www\/ownclo…’)\n#18 {main}",“File”:"\/var\/www\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php",“Line”:104}"}

2

I did find something similar on gitHub issue 34572 where a user reported a similar experience and provided the following as the solution:

  1. Change the engine: ALTER TABLE oc_filecache ENGINE = InnoDB;
  2. Delete the table oc_persistent_locks (DROP TABLE oc_persistent_locks;)
  3. start the upgrade again

I still receive the same error as the original post.

However, this does not solve the problem even though it was reported as solving a problem for at least one person.

I have also found that if I leave the oc_persistent)locks table in place without dropping the table, I get this message when I try to upgrade again:

Preparing update
Set log level to debug
Turned on maintenance mode
Updating database schema
Doctrine\DBAL\Schema\SchemaException: The table with name ‘owncloud.oc_persistent_locks’ already exists.

3

I have a similar error with the oc Docker image.

My running oc Docker instance is 10.0.x with MariaDB

Have attempted to upgrade from 10.0.x to 10.1.x and again in the last week from
10.0.x to 10.2.x

but the same error on oc_persistent_locks occurs with both versions

oc_owncloud | 2019-07-08T05:03:58+00:00 Updating database schema
oc_owncloud | 2019-07-08T05:04:00+00:00 Doctrine\DBAL\Exception\DriverException: An exception occurred while executing ‘ALTER TABLE oc_persistent_locks ADD CONSTRAINT FK_F0C3D55BC901C6FF FOREIGN KEY (owner_account_id) REFERENCES oc_accounts (id) ON DELETE CASCADE’:
oc_owncloud |
oc_owncloud | SQLSTATE[HY000]: General error: 1005 Can’t create table owncloud.oc_persistent_locks (errno: 150 “Foreign key constraint is incorrectly formed”)
oc_owncloud | 2019-07-08T05:04:00+00:00 Update failed

The DB table oc_persistent_locks gets created but the upgrade process fails when the DDL for the second foreign key gets run.
FK_F0C3D55B93CB796C is created successfully but
FK_F0C3D55BC901C6FF is not

I have also deleted the table but this doesn’t help.

My understanding of the function of oc_persistent_locks is something to do with file handling for Microsoft Office Online Server.


Not something that I care about but would like a solution to the upgrade.

4

Just a quick update.

I’ve had a fiddle with the DDL for the creation of the oc_persistent_locks table.

I can get the table to create with both Foreign Keys defined by altering the data type for owner_account_id

It appears that In the oc upgrade, oc_persistent_locks.owner_account_id is defined with bigint(20) unsigned.

If I change this to just bigint(20), then the failing FK gets created .

My suspicion is an error in the DDL for this table.

5

Hey,

i’m not sure but maybe you need to raise a bugreport to the ownCloud developers to make them aware of possible issues with foreign keys?

6

It is looking like I will need to submit a bug report because I’m not going to be able to fiddle with the DLLs to get the field correct. Thank you for the input.

1 Like
7

So the migration fails in this file,
core/Migrations/Version20180607072706.php

Looks like a table type issue with that particular key.
If you comment out the foreign constraint it will run … but as to what consequence?
$table->addForeignKeyConstraint(
“{$prefix}filecache”,
[‘file_id’],
[‘fileid’],
[‘onDelete’ => ‘CASCADE’]
);

Detailed error from mysql, which tends to suggest that maybe mysql database is too old (bit about InnoDB-4.1.12)

200325 21:10:30 Error in foreign key constraint of table owncloud/#sql-9d5_87d5:
FOREIGN KEY (file_id) REFERENCES oc_filecache (fileid) ON DELETE CASCADE:

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.