Owncloud.oc_filecache error on import from backup

Hi ownCloud team,

I’ve meticulously followed the guides on migrating from our old install to a new one, as our old server would have had needless complexity to upgrade in place. 10.x server stood up, basic config all good. Copy data and config folders over, no issues. Export db, ok. Restore db on new server, error. Essentially there’s an issue with the oc_filecache table, error 150.

ERROR 1005 (HY000) at line 591: Can't create table owncloud.oc_filecache (errno: 150 "Foreign key constraint is incorrectly formed")

How can we get this db restore to work? There is nothing particular in this table as far as I can see.

Here’s the first few rows (sorry if messy!);

MariaDB [owncloud]> select * from oc_filecache limit 10;
+--------+---------+----------------+----------------------------------+--------+----------------+----------+----------+------------+------------+---------------+-----------+------------------+---------------+-------------+----------+
| fileid | storage | path           | path_hash                        | parent | name           | mimetype | mimepart | size       | mtime      | storage_mtime | encrypted | unencrypted_size | etag          | permissions | checksum |
+--------+---------+----------------+----------------------------------+--------+----------------+----------+----------+------------+------------+---------------+-----------+------------------+---------------+-------------+----------+
|      1 |       1 |                | d41d8cd98f00b204e9800998ecf8427e |     -1 |                |        2 |        1 |   12585259 | 1578951875 |
 1572649054 |         0 |                0 | 5e1ce4c31bcf6 |          23 |          |
|      2 |       1 | files          | 45b963397aa40d4a0063e0d85e4fe7a1 |      1 | files          |        2 |        1 |          0 | 1578951875 |
 1578951875 |         0 |                0 | 5e1ce4c31a743 |          31 |          |
|     16 |       1 | cache          | 0fea6a13c52b4d4725368f24b045ca84 |      1 | cache          |        2 |        1 |          0 | 1484090267 |
 1484090267 |         0 |                0 | 58756b9bb9617 |          31 |          |
|     17 |       2 |                | d41d8cd98f00b204e9800998ecf8427e |     -1 |                |        2 |        1 |         -1 | 1484090269 |
 1484090269 |         0 |                0 | 58756b9ddd8aa |          23 |          |
|     18 |       2 | files_external | c270928b685e7946199afdfb898d27ea |     17 | files_external |        2 |        1 |          0 | 1484090269 |
 1484090269 |         0 |                0 | 58756b9ddbd60 |          31 |          |
|     19 |       3 |                | d41d8cd98f00b204e9800998ecf8427e |     -1 |                |        2 |        1 |          0 | 1576107357 |
 1576107357 |         0 |                0 | 5df17d5dcc9ea |          23 |          |
|     20 |       3 | cache          | 0fea6a13c52b4d4725368f24b045ca84 |     19 | cache          |        2 |        1 |          0 | 1487206780 |
 1487206780 |         0 |                0 | 58a4f97c0ea1c |          31 |          |
|     21 |       3 | files          | 45b963397aa40d4a0063e0d85e4fe7a1 |     19 | files          |        2 |        1 |          0 | 1576107352 |
 1576106543 |         0 |                0 | 5df17d5800998 |          31 |          |
|     29 |       4 |                | d41d8cd98f00b204e9800998ecf8427e |     -1 |                |        2 |        1 | 2874572715 | 1579310924 |
 1494350914 |         0 |                0 | 5e225f4c62412 |          23 |          |
|     30 |       4 | cache          | 0fea6a13c52b4d4725368f24b045ca84 |     29 | cache          |        2 |        1 |          0 | 1487207225 |
 1487207225 |         0 |                0 | 58a4fb394b15c |          31 |          |
+--------+---------+----------------+----------------------------------+--------+----------------+----------+----------+------------+------------+---------------+-----------+------------------+---------------+-------------+----------+
10 rows in set (0.00 sec)

Steps to reproduce

  1. Prepare export of DB on 9.1.x server

mysqldump --single-transaction -h localhost -u ocl_user -pPassWORD owncloud > owncloud-dbbackup-ORIG.bak

  1. Copy to 10.x server
  2. Import backup to new 10.x server

mysql -h localhost -u ocl_user -pPassWORD owncloud < owncloud-dbbackup-ORIG.bak

Expected behaviour

Import successful

Actual behaviour

ERROR 1005 (HY000) at line 591: Can’t create table owncloud.oc_filecache (errno: 150 “Foreign key constraint is incorrectly formed”)

Server configuration

Operating system: RHEL7

Web server: Apache2

Database: MariaDB

PHP version: 7.2

ownCloud version: (see ownCloud admin page) 10.2

Updated from an older ownCloud or fresh install: Fresh Install (migration)

Where did you install ownCloud from: Repo

Signing status (ownCloud 9.0 and above):

The content of config/config.php:

<?php
$CONFIG = array (
  'updatechecker' => false,
  'instanceid' => '**********',
  'passwordsalt' => '**********************',
  'secret' => '**************',
  'trusted_domains' =>
  array (
    0 => '***********',
    1 => '*************',
    2 => '*********',
  ),
  'datadirectory' => '/var/www/html/owncloud/data',
  'overwrite.cli.url' => 'http://*********.com/owncloud',
  'dbtype' => 'mysql',
  'version' => '10.2.1.4',
  'dbname' => 'owncloud',
  'dbhost' => 'localhost',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'ocl_user',
  'dbpassword' => '**********',
  'logtimezone' => 'UTC',
  'installed' => true,
  'loglevel' => 2,
  'mail_smtpmode' => 'smtp',
  'mail_smtphost' => '1.1.1.1',
  'mail_smtpport' => '25',
  'mail_domain' => '*********.com',
  'mail_from_address' => 'noreply-drive',
  'maintenance' => false,
  'theme' => '',
  'apps_paths' =>
  array (
    0 =>
    array (
      'path' => '/var/www/html/owncloud/apps',
      'url' => '/apps',
      'writable' => false,
    ),
    1 =>
    array (
      'path' => '/var/www/html/owncloud/apps-external',
      'url' => '/apps-external',
      'writable' => true,
    ),
  ),
);

ownCloud 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

{
    "system": {
        "updatechecker": false,
        "instanceid": "***********",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***********",
            "***********",
            "***********"
        ],
        "datadirectory": "\/var\/www\/html\/owncloud\/data",
        "overwrite.cli.url": "http:\/\/***********.com\/owncloud",
        "dbtype": "mysql",
        "version": "10.2.1.4",
        "dbname": "owncloud",
        "dbhost": "localhost",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "UTC",
        "installed": true,
        "loglevel": 2,
        "mail_smtpmode": "smtp",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "maintenance": false,
        "theme": "",
        "apps_paths": [
            {
                "path": "\/var\/www\/html\/owncloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/html\/owncloud\/apps-external",
                "url": "\/apps-external",
                "writable": true
            }
        ]
    }
}

List of activated apps:

ownCloud 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
Enabled:

  • comments: 0.3.0
  • configreport: 0.1.1
  • dav: 0.2.7
  • federatedfilesharing: 0.3.0
  • files: 1.5.2
  • files_external: true
  • files_sharing: 0.10.0
  • files_trashbin: 0.9.0
  • files_versions: 1.3.0
  • files_videoplayer: 0.9.8
  • notifications: 0.3.0
  • updatenotification: 0.2.1
    Disabled:
  • encryption
  • external
  • federation
  • firstrunwizard
  • market
  • provisioning_api
  • systemtags
  • user_external

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

Are you using encryption: yes/no

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

Just had a very similar sounding error in an upgrade from 10.0.10 to 10.1.0:

SQLSTATE[HY000]: General error: 1005 Can't create table 'DB_NAME_REMOVED.#sql-613_1e671' (errno: 150)

The root cause was that the DB was still using MyISAM instead of InnoDB. You should be able to check that by grepping for ENGINE in your DB export.

Also, it sounds like you set up a new server. I doubt old and new server have the same MySQL version. As far as I know, you can’t just import any old dump into your latest MySQL/MariaDB installation:

What was your old MySQL version, and what is your new version?

1 Like

Wait, am I reading this right? Are you trying to import an ownCloud 9 DB into an ownCloud 10 server?
If yes, this does not work how you think it does.

Updates between versions of ownCloud have to change stuff in the DB. An ownCloud 9 DB dump is incomprehensible to an ownCloud 10 installation. You have two options:

  1. Run the upgrade on the old machine. Migrate after.
  2. Migrate first, and then run the upgrade on the new machine.

I usually prefer option 1, because I prefer a new machine as clean as possible. However sometimes you can get into a pickle with different PHP versions only being available on certain OS versions…

I recommend you read the following documentation article carefully:
https://doc.owncloud.org/server/10.4/admin_manual/maintenance/manual_upgrade.html

1 Like

Interesting - migration documentation makes no reference to version numbers. Also, as a test, I imported all other tables, which came in totally fine. The only one that failed was oc_filecache.

Interestingly enough, the db import is just fine with the offending table dropped. I saw somewhere that if I were to modify the foreign key constraint, it would work, but I’m not sure what effects would be seen downstream.

original server
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

new server
mysql Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1