Update 10.0.8.5 to 10.1.0 failed on shared hoster

[Pingponguin]

Hello, OC is really giving me a hard time every time I try to update it. It would really be a great step forward if update process could be noob-safe.
I found my actual update errors in similar ways on the net, but could not figure out how to solve them in my case. Can anybody help? Thanks and regards, Pingpong

owncloud_log.zip

Steps to reproduce

1. Uploaded new files to oc directory
2. Started update with web browser

Expected behaviour

Update my oc to 10.1.0

Actual behaviour

Update failed and gives error message:

Update vorbereiten
Log-Level auf "debug" gesetzt
Wartungsmodus eingeschaltet
Reperaturwarnung:Market app is unavailable for updating of apps. Please update manually, see https://doc.owncloud.org/server/10.1/go.php?to=admin-marketplace-apps
Das Datenbankschema wird aktualisiert
Doctrine\DBAL\Schema\SchemaException: The table with name 'web143_db3.oc_persistent_locks' already exists.

Server configuration

Operating system:
unknown
Web server:
Apache
Database:
MYSQL
PHP version:
7.2
ownCloud version: (see ownCloud admin page)
10.0.8.5
Where did you install ownCloud from:
https://owncloud.org/download/
The content of config/config.php:

<?php
$CONFIG = array (
  'instanceid' => '***',
  'passwordsalt' => '***Y',
  'secret' => '***',
  'trusted_domains' => 
  array (
    0 => 'www.***.de',
    1 => '***.de',
  ),
  'datadirectory' => '/***/owncloud/data',
  'overwrite.cli.url' => 'http://www.***.de',
  'dbtype' => 'mysql',
  'version' => '10.0.8.5',
  'dbname' => '***',
  'dbhost' => 'localhost',
  'dbtableprefix' => 'oc_',
  'dbuser' => '***',
  'dbpassword' => '***',
  'installed' => true,
  'forcessl' => true,
  'forceSSLforSubdomains' => true,
  'mail_from_address' => '***',
  'mail_smtpmode' => 'php',
  'mail_domain' => '***.de',
  'theme' => '',
  'loglevel' => 0,
  'maintenance' => false,
  'htaccess.RewriteBase' => '/',
  'updater.secret' => '***',
);

Log in to the web-UI with an administrator account and click on
'admin' -> 'Generate Config Report' -> 'Download ownCloud config report'
not possible
List of activated apps:
non
Are you using external storage, if yes which one: local/smb/sftp/...
no
Are you using encryption: yes/no
don't know
Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...
no

Client configuration

Browser
Vivaldi 2.3.1440.48
Operating system:
Win 10 Pro 1809

Logs

Web server error log

[Tue Feb 19 13:09:15 2019] [error] [client AH02032: Hostname www..de: provided via SNI and hostname www..de provided via HTTP are different
[Thu Feb 21 23:47:18 2019] [error] [client AH02032: Hostname www..de:*** provided via SNI and hostname www..de provided via HTTP are different
[Fri Feb 22 18:44:48 2019] [error] [client 188.193.149.19:2443] AH01630: client denied by server configuration: //owncloud/data/htaccesstest.txt
[Fri Feb 22 18:45:59 2019] [error] [client 188.193.149.19:2458] AH01630: client denied by server configuration: //owncloud/data/htaccesstest.txt
[Fri Feb 22 19:36:30 2019] [error] [client 188.193.149.19:2645] AH01630: client denied by server configuration: //owncloud/data/htaccesstest.txt
[Fri Feb 22 20:58:51 2019] [error] [client 188.193.149.19:3999] script '/***/owncloud/index.php' not found or unable to stat

ownCloud log (data/owncloud.log)

see attached file owncloud_log.zip

[ownclouders]

GitMate.io thinks the contributors most likely able to help are @ownclouders, and @PVince81.

Possibly related issues are #18350 (DB-Schema update fails, 8.1.0 to 8.1.1), #17768 ( 8.1.0　update Failed), #18032 (massive/strange db updates after upgrade to 8.1.0), #14677 (Upgrade failing 7.0.5 to 8.0.1 when keeping apps folder), and #4857 (Updater fails to update to 5.0.11 (unable to move ...)).

[Pingponguin]

Unfortunately those links are not helpful for me.

[Pingponguin]

I tried again to solve this part of the issue:

Reperaturwarnung:Market app is unavailable for updating of apps. Please update manually, see https://doc.owncloud.org/server/10.1/go.php?to=admin-marketplace-apps

Since I do not know what I'm doing, its hard for me to figure out how to adapt the instructions for my situation.

Any command I try with Putty gives me the same error back. For example:

sudo -u www-data php occ app:upgrade market

Result:

sudo: unable to stat /etc/sudoers: No such file or directory
sudo: no valid sudoers sources found, quitting
sudo: unable to initialize policy plugin

The only expression I could find with help is -u, but this did not help.

How can I update market and why do I have to update it anyway, since the apps are all contained in the new version?

[PVince81]

hmm this was observed before but could not be reproduced: #33963

it failed on the same table, so there's likely something that was similar in your env compared to that other env

[PVince81]

from what I see there was an error before the migration even ran:

{"reqId":"XHBKjCUR4C0AAEM0-nEAAAAF","level":0,"time":"2019-02-22T19:16:28+00:00","remoteAddr":"***","user":"--","app":"core","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=UCAHHyIjIWleBHtjcT50GyMIPDJ9MhgfeSwbEwUOCw4%3D%3Aimm4hAN0iCP93y8%2FhLKZ%2BvOO6EBkttLvkwedzwzymZw%3D","message":"Migrations: starting 20180607072706 from app core"}
{"reqId":"XHBKjCUR4C0AAEM0-nEAAAAF","level":3,"time":"2019-02-22T19:16:29+00:00","remoteAddr":"***","user":"--","app":"core","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=UCAHHyIjIWleBHtjcT50GyMIPDJ9MhgfeSwbEwUOCw4%3D%3Aimm4hAN0iCP93y8%2FhLKZ%2BvOO6EBkttLvkwedzwzymZw%3D","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\ConnectionException\",\"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[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'web143_3'@'localhost' for table 'oc_filecache'\",\"Code\":0,\"Trace\":\"
#0 \***\/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 \***\/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 \***\/owncloud\\\/lib\\\/private\\\/DB\\\/Migrator.php(198): Doctrine\\\\DBAL\\\\Connection->query('ALTER TABLE oc_...')\\n
#3 \***\/owncloud\\\/lib\\\/private\\\/DB\\\/Migrator.php(80): OC\\\\DB\\\\Migrator->applySchema(Object(Doctrine\\\\DBAL\\\\Schema\\\\Schema))\\n
#4 \***\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(434): OC\\\\DB\\\\Migrator->migrate(Object(Doctrine\\\\DBAL\\\\Schema\\\\Schema))\\n
#5 \***\/owncloud\\\/lib\\\/private\\\/DB\\\/MigrationService.php(412): OC\\\\DB\\\\Connection->migrateToSchema(Object(Doctrine\\\\DBAL\\\\Schema\\\\Schema))\\n
#6 \***\/owncloud\\\/lib\\\/private\\\/DB\\\/MigrationService.php(366): OC\\\\DB\\\\MigrationService->executeStep(20180607072706)\\n
#7 \***\/owncloud\\\/lib\\\/private\\\/Updater.php(301): OC\\\\DB\\\\MigrationService->migrate()\\n
#8 \***\/owncloud\\\/lib\\\/private\\\/Updater.php(255): OC\\\\Updater->doCoreUpgrade()\\n
#9 \***\/owncloud\\\/lib\\\/private\\\/Updater.php(112): OC\\\\Updater->doUpgrade('10.1.0.4', '10.0.8.5')\\n
#10 \***\/owncloud\\\/core\\\/ajax\\\/update.php(189): OC\\\\Updater->upgrade()\\n
#11 {main}\",\"File\":\"\***\/owncloud\\\/lib\\\/composer\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":103}"}
{"reqId":"XHBK9yUR4C0AAGNL0nwAAAAE","level":3,"time":"2019-02-22T19:18:15+00:00","remoteAddr":"***","user":"--","app":"index","method":"GET","url":"\/core\/js\/oc.js?v=dca6c4ff9b1c577139f248b0e13a8895","message":"Exception: {\"Exception\":\"OC\\\\NeedsUpdateException\",\"Message\":\"\",\"Code\":0,\"Trace\":\"#0 \***\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(127): OC_App::loadApp('dav')\\n#1 \***\/owncloud\\\/lib\\\/private\\\/legacy\\\/util.php(147): OC_App::loadApps(Array)\\n#2 \***\/owncloud\\\/lib\\\/base.php(908): OC_Util::setupFS()\\n#3 \***\/owncloud\\\/index.php(54): OC::handleRequest()\\n#4 {main}\",\"File\":\"\***\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php\",\"Line\":191}"}
{"reqId":"XHBLBCUR4C0AAAkFVR0AAAAI","level":3,"time":"2019-02-22T19:18:28+00:00","remoteAddr":"***","user":"--","app":"index","method":"GET","url":"\/core\/js\/oc.js?v=dca6c4ff9b1c577139f248b0e13a8895","message":"Exception: {\"Exception\":\"OC\\\\NeedsUpdateException\",\"Message\":\"\",\"Code\":0,\"Trace\":\"#0 \***\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(127): OC_App::loadApp('dav')\\n#1 \***\/owncloud\\\/lib\\\/private\\\/legacy\\\/util.php(147): OC_App::loadApps(Array)\\n#2 \***\/owncloud\\\/lib\\\/base.php(908): OC_Util::setupFS()\\n#3 \***\/owncloud\\\/index.php(54): OC::handleRequest()\\n#4 {main}\",\"File\":\"\***\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php\",\"Line\":191}"}
{"reqId":"XHBLCCUR4C0AAEuBUjIAAAAD","level":0,"time":"2019-02-22T19:18:32+00:00","remoteAddr":"***","user":"--","app":"core","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=UX8fAD4hHShmC14oGg0PAz0qOgUxE2QKOUYPTEM9ElM%3D%3Ah2u%2BtCrqQLurXJC7vnMmgW3Zv%2FV42GU%2BGxEv2dLcYyc%3D","message":"starting upgrade from 10.0.8.5 to 10.1.0.4"}

It looks like your user has no permissions to create foreign keys Syntax error or access violation: 1142 REFERENCES command denied to user 'web143_3'@'localhost' for table oc_filecache` but somehow this didn't stop the upgrade and it went through.

From what I see you re-started the failed upgrade a second time:

{"reqId":"XHBLCCUR4C0AAEuBUjIAAAAD","level":0,"time":"2019-02-22T19:18:32+00:00","remoteAddr":"***","user":"--","app":"core","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=UX8fAD4hHShmC14oGg0PAz0qOgUxE2QKOUYPTEM9ElM%3D%3Ah2u%2BtCrqQLurXJC7vnMmgW3Zv%2FV42GU%2BGxEv2dLcYyc%3D","message":"Migrations: starting 20180607072706 from app core"}
{"reqId":"XHBLCCUR4C0AAEuBUjIAAAAD","level":3,"time":"2019-02-22T19:18:32+00:00","remoteAddr":"***","user":"--","app":"core","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=UX8fAD4hHShmC14oGg0PAz0qOgUxE2QKOUYPTEM9ElM%3D%3Ah2u%2BtCrqQLurXJC7vnMmgW3Zv%2FV42GU%2BGxEv2dLcYyc%3D","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Schema\\\\SchemaException\",\"Message\":\"The table with name 'web143_db3.oc_persistent_locks' already exists.\",\"Code\":20,\"Trace\":\"#0 \***\/owncloud\\\/lib\\\/composer\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Schema\\\/Schema.php(132): Doctrine\\\\DBAL\\\\Schema\\\\SchemaException::tableAlreadyExists('web143_db3.oc_p...')\\n#1 \***\/owncloud\\\/lib\\\/composer\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Schema\\\/Schema.php(340): Doctrine\\\\DBAL\\\\Schema\\\\Schema->_addTable(Object(Doctrine\\\\DBAL\\\\Schema\\\\Table))\\n#2 \***\/owncloud\\\/core\\\/Migrations\\\/Version20180607072706.php(15): Doctrine\\\\DBAL\\\\Schema\\\\Schema->createTable('oc_persistent_l...')\\n#3 \***\/owncloud\\\/lib\\\/private\\\/DB\\\/MigrationService.php(411): OC\\\\Migrations\\\\Version20180607072706->changeSchema(Object(Doctrine\\\\DBAL\\\\Schema\\\\Schema), Array)\\n#4 \***\/owncloud\\\/lib\\\/private\\\/DB\\\/MigrationService.php(366): OC\\\\DB\\\\MigrationService->executeStep(20180607072706)\\n#5 \***\/owncloud\\\/lib\\\/private\\\/Updater.php(301): OC\\\\DB\\\\MigrationService->migrate()\\n#6 \***\/owncloud\\\/lib\\\/private\\\/Updater.php(255): OC\\\\Updater->doCoreUpgrade()\\n#7 \***\/owncloud\\\/lib\\\/private\\\/Updater.php(112): OC\\\\Updater->doUpgrade('10.1.0.4', '10.0.8.5')\\n#8 \***\/owncloud\\\/core\\\/ajax\\\/update.php(189): OC\\\\Updater->upgrade()\\n#9 {main}\",\"File\":\"\***\/owncloud\\\/lib\\\/composer\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Schema\\\/SchemaException.php\",\"Line\":109}"}
{"reqId":"XHBLJiUR4C0AAGNL0n4AAAAE","level":3,"time":"2019-02-22T19:19:02+00:00","remoteAddr":"***","user":"--","app":"index","method":"GET","url":"\/core\/js\/oc.js?v=dca6c4ff9b1c577139f248b0e13a8895","message":"Exception: {\"Exception\":\"OC\\\\NeedsUpdateException\",\"Message\":\"\",\"Code\":0,\"Trace\":\"#0 \***\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(127): OC_App::loadApp('dav')\\n#1 \***\/owncloud\\\/lib\\\/private\\\/legacy\\\/util.php(147): OC_App::loadApps(Array)\\n#2 \***\/owncloud\\\/lib\\\/base.php(908): OC_Util::setupFS()\\n#3 \***\/owncloud\\\/index.php(54): OC::handleRequest()\\n#4 {main}\",\"File\":\"\***\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php\",\"Line\":191}"}

So the first upgrade did create the table but failed due to the permissions issue. Likely you are running OC on a shared hoster ?

And the second run tried to recreate the failed table.

Normally to retry an upgrade one needs to restore from backup to have the former database state.

@DeepDiver1975 seems that migrations aren't atomic ? I'd expect the migration to roll back all operations (structure changes) that were done within the migration if one of the operations fail.

We could try and mitigate this by adding a table existence check. However this wouldn't solve the foreign key issue.

[Pingponguin]

You*ve got the point of my real problem. I could fix the error messages stated above by activating market apps and deleting web143_db3.oc_persistent_locks by shell, but now I'm stuck at:

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 '***'@'localhost' for table 'oc_filecache'
Update failed

This is obviously the external key problem. And I have no idea what you are talking about.

[PVince81]

@Pingponguin did you install your ownCloud on a shared web hoster or on a local machine where you have root / admin access ?

many things in your report seem to give hints that this might be a shared hoster.

otherwise if you have admin access for the whole database server you should grant the "web143_db3" all privileges.

[Pingponguin]

@PVince81 I'm on a shared web hoster. I read about the foreign key constraint and understand the problem in general, but not really why being with a shared hoster is a problem.

Is my oc installation and all the data lost now?

[patrickjahns]

@Pingponguin
Would you be able to provide some information on the tables in the database.
Can you issue a describe oc_filecache; and describe oc_accounts; - or show from your mysql interface the row names and types?

Is my oc installation and all the data lost now?

The data is preserved on the server - the database we will work towards finding a solution

[Pingponguin]

@patrickjahns

I have lost the content of table oc_filecache. I emptied it because of its size, but when I tried to restore the backup was corrupt. I hope this is not an additional problem.

[VicDeo]

@Pingponguin
could you ask you hoster whether it is possible to grant REFERENCES permission for the DB user from your config.php on this DB?
Basically they need to run something like this:
GRANT REFERENCES ON YOUR_DB_NAME.* TO 'YOUR_DB_USER'@'YOUR_DB_HOST';
and then
FLUSH PRIVILEGES;

or check if it is possible on your own via cPanel https://www.youtube.com/watch?v=lXnvDD9j6Hs

[VicDeo]

also could be related to the different engines used for these two tables, like in #34572

The parent and child tables must use the same storage engine.

From here https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

[Pingponguin]

@Pingponguin
could you ask you hoster whether it is possible to grant REFERENCES permission for the DB user from your config.php on this DB?
Basically they need to run something like this:
GRANT REFERENCES ON YOUR_DB_NAME.* TO 'YOUR_DB_USER'@'YOUR_DB_HOST';
and then
FLUSH PRIVILEGES;

or check if it is possible on your own via cPanel https://www.youtube.com/watch?v=lXnvDD9j6Hs

Sorry for late reply but this issue really made me upset and I needed a break.

My hoster just said that he is not familiar with OC and I can use these commands for update:

chmod u+x occ 
/usr/bin/php7.2 occ [command for update]

Of course this did not work.

I tried a new installation with OC 10.1.1. and copied config.php and /data to it, but still I get the error:

Doctrine\DBAL\Schema\SchemaException: The table with name '***.oc_persistent_locks' already exists.

[Pingponguin]

Since my issue has referenced to other threads with the same issue I assume that owncloud cannot be run safely on a hosted server. At least not by noobs like me.

The update process was from the beginning on a challange, never knowing if it will work or not. Now my installation is broken and lost. Since there are no alternatives to owncloud I will have to go back to dropbox or what ever.

[loxiran]

Any updates? I still have the same error when i upgrade to version 10.2.1.

Doctrine\DBAL\Schema\SchemaException: The table with name 'owncloud.oc_persistent_locks' already exists

[yger]

Any updates? I have the same error, which is fact is happening in two steps. First time you launch the occ upgrade, I have the error

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: 1215 Cannot add foreign key constraint

Then if you relaunch, the table is already created

Doctrine\DBAL\Schema\SchemaException: The table with name 'cloud.oc_persistent_locks' already exists.

Manually deleting the table persistent_lock is not solving the problem, and the upgrade can not be made

[loxiran]

Any updates? I still have the same error when i upgrade to version 10.2.1.

Doctrine\DBAL\Schema\SchemaException: The table with name 'owncloud.oc_persistent_locks' already exists

I did it! The user of the owncloud MySQL database does not have GRANT REFERENCES permission to create a FOREIGN KEY. The update worked well after the fix.

[VicDeo]

yep. #34598 (comment)