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
- Prepare export of DB on 9.1.x server
mysqldump --single-transaction -h localhost -u ocl_user -pPassWORD owncloud > owncloud-dbbackup-ORIG.bak
- Copy to 10.x server
- 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/…