MySQL Clusters prohibits use of DML commands on tables without an explicit Primary Key

[maxolasersquad]

Steps to reproduce

1. Try to upgrade from Nextcloud 16.0.0 to 16.3 on a Percona MySQL Galera cluster

Expected behaviour

Upgrade should succeed.

Actual behaviour

During the update I receive the following error.

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'DELETE FROM oc_collres_accesscache': SQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

Server configuration

Operating system: Ubuntu 18.04

Web server: Apache

Database: MySQL 5.7 clustered with 3 nodes using Galera and PerconaXtra-Db

PHP version: 7.2

Nextcloud version: (see Nextcloud admin page) 16.0.0 ➡ 16.0.3

Updated from an older Nextcloud/ownCloud or fresh install: Updated

Where did you install Nextcloud from: Source download

Signing status:

Signing status

``` Login as admin user into your Nextcloud and access http://example.com/index.php/settings/integrity/failed paste the results here. ```

List of activated apps:

App list

``` Enabled: - accessibility: 1.2.0 - activity: 2.9.1 - admin_audit: 1.6.0 - cloud_federation_api: 0.2.0 - comments: 1.6.0 - dav: 1.9.2 - federatedfilesharing: 1.6.0 - federation: 1.6.0 - files: 1.11.0 - files_external: 1.7.0 - files_fulltextsearch: 1.3.2 - files_markdown: 2.0.6 - files_pdfviewer: 1.5.0 - files_rightclick: 0.13.0 - files_sharing: 1.8.0 - files_texteditor: 2.8.0 - files_trashbin: 1.6.0 - files_versions: 1.9.0 - files_videoplayer: 1.5.0 - firstrunwizard: 2.5.0 - fulltextsearch: 1.3.4 - groupfolders: 4.0.3 - logreader: 2.1.0 - lookup_server_connector: 1.4.0 - metadata: 0.9.0 - nextcloud_announcements: 1.5.0 - notifications: 2.4.1 - oauth2: 1.4.2 - password_policy: 1.6.0 - previewgenerator: 2.1.0 - privacy: 1.0.0 - provisioning_api: 1.6.0 - recommendations: 0.4.0 - serverinfo: 1.6.0 - sharebymail: 1.6.0 - support: 1.0.0 - survey_client: 1.4.0 - systemtags: 1.6.0 - theming: 1.7.0 - twofactor_backupcodes: 1.5.0 - updatenotification: 1.6.0 - viewer: 1.0.0 - workflowengine: 1.6.0 Disabled: - encryption - gallery - user_ldap ```

Nextcloud configuration:

Config report

``` { "system": { "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "nextcloud.bloomintelligence.com" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "16.0.0.9", "overwrite.cli.url": "https:\/\/nextcloud.bloomintelligence.com", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "", "dbtableprefix": "oc_", "mysql.utf8mb4": true, "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "instanceid": "***REMOVED SENSITIVE VALUE***", "memcache.local": "\\OC\\Memcache\\APCu", "memcache.locking": "\\OC\\Memcache\\Redis", "redis": { "host": "***REMOVED SENSITIVE VALUE***", "password": "***REMOVED SENSITIVE VALUE***", "port": 6379 }, "mail_smtpmode": "smtp", "mail_smtpsecure": "ssl", "mail_from_address": "***REMOVED SENSITIVE VALUE***", "mail_domain": "***REMOVED SENSITIVE VALUE***", "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_smtpport": "587", "mail_smtpauth": 1, "mail_smtpname": "***REMOVED SENSITIVE VALUE***", "mail_smtppassword": "***REMOVED SENSITIVE VALUE***", "overwriteprotocol": "https", "enabledPreviewProviders": [ "OC\\Preview\\PNG", "OC\\Preview\\JPEG", "OC\\Preview\\GIF", "OC\\Preview\\HEIC", "OC\\Preview\\BMP", "OC\\Preview\\XBitmap", "OC\\Preview\\MP3", "OC\\Preview\\TXT", "OC\\Preview\\MarkDown", "OC\\Preview\\Illustrator", "OC\\Preview\\Movie", "OC\\Preview\\MSOffice2003", "OC\\Preview\\MSOffice2007", ```

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

Are you using encryption: yes/no No

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

Client configuration

Browser: Chrome

Operating system: Ubuntu 18.04

Logs

Web server error log

Web server error log

``` [Tue Jul 09 03:40:46.844014 2019] [authz_core:error] [pid 4798] [client 192.168.255.93:60260] AH01630: client denied by server configuration: /home/jenny/domains/nextcloud/config [Tue Jul 09 11:46:34.909057 2019] [authz_core:error] [pid 10230] [client 192.168.255.93:53466] AH01630: client denied by server configuration: /home/jenny/domains/nextcloud/config [Tue Jul 09 13:11:48.329351 2019] [authz_core:error] [pid 32460] [client 192.168.255.93:40552] AH01630: client denied by server configuration: /home/jenny/domains/nextcloud/data/.ocdata ```

Nextcloud log (data/nextcloud.log)

Nextcloud log

``` {"reqId":"khU8W08tfvieZyqPsASH","level":3,"time":"2019-07-09T13:19:12+00:00","remoteAddr":"192.168.255.93","user":"--","app":"core","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=T8VbP1Tvrd%2FQ2NkXM7Zp974n1xBxAjibuXkpBguQ%2B%2Bw%3D%3AOY1tCzen3ayWvIlEWfQFpY5ChEYZYV3T%2Fkh4Y2aos4s%3D","message":{"Exception":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing 'DELETE FROM `oc_collres_accesscache`':\n\nSQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER","Code":0,"Trace":[{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php","line":184,"function":"convertException","class":"Doctrine\\DBAL\\Driver\\AbstractMySQLDriver","type":"->","args":["An exception occurred while executing 'DELETE FROM `oc_collres_accesscache`':\n\nSQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER",{"errorInfo":["HY000",1105,"Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"}]},{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php","line":158,"function":"wrapException","class":"Doctrine\\DBAL\\DBALException","type":"::","args":[{"__class__":"Doctrine\\DBAL\\Driver\\PDOMySql\\Driver"},{"errorInfo":["HY000",1105,"Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"},"An exception occurred while executing 'DELETE FROM `oc_collres_accesscache`':\n\nSQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER"]},{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php","line":1088,"function":"driverExceptionDuringQuery","class":"Doctrine\\DBAL\\DBALException","type":"::","args":[{"__class__":"Doctrine\\DBAL\\Driver\\PDOMySql\\Driver"},{"errorInfo":["HY000",1105,"Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"},"DELETE FROM `oc_collres_accesscache`",[]]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/Connection.php","line":216,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->","args":["DELETE FROM `oc_collres_accesscache`",[],[]]},{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Query\/QueryBuilder.php","line":222,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->","args":["DELETE FROM `oc_collres_accesscache`",[],[]]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/QueryBuilder\/QueryBuilder.php","line":214,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/core\/Migrations\/Version16000Date20190427105638.php","line":50,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/MigrationService.php","line":470,"function":"preSchemaChange","class":"OC\\Core\\Migrations\\Version16000Date20190427105638","type":"->","args":[{"__class__":"OC\\Migration\\SimpleOutput"},{"__class__":"Closure"},{"tablePrefix":"oc_"}]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/MigrationService.php","line":411,"function":"executeStep","class":"OC\\DB\\MigrationService","type":"->","args":["16000Date20190427105638",false]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":294,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":241,"function":"doCoreUpgrade","class":"OC\\Updater","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":128,"function":"doUpgrade","class":"OC\\Updater","type":"->","args":["16.0.3.0","16.0.0.9"]},{"file":"\/home\/jenny\/domains\/nextcloud\/core\/ajax\/update.php","line":212,"function":"upgrade","class":"OC\\Updater","type":"->","args":[]}],"File":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/AbstractMySQLDriver.php","Line":125,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDOException","Message":"SQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER","Code":"HY000","Trace":[{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php","line":1085,"function":"exec","class":"Doctrine\\DBAL\\Driver\\PDOConnection","type":"->","args":["DELETE FROM `oc_collres_accesscache`"]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/Connection.php","line":216,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->","args":["DELETE FROM `oc_collres_accesscache`",[],[]]},{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Query\/QueryBuilder.php","line":222,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->","args":["DELETE FROM `oc_collres_accesscache`",[],[]]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/QueryBuilder\/QueryBuilder.php","line":214,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/core\/Migrations\/Version16000Date20190427105638.php","line":50,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/MigrationService.php","line":470,"function":"preSchemaChange","class":"OC\\Core\\Migrations\\Version16000Date20190427105638","type":"->","args":[{"__class__":"OC\\Migration\\SimpleOutput"},{"__class__":"Closure"},{"tablePrefix":"oc_"}]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/MigrationService.php","line":411,"function":"executeStep","class":"OC\\DB\\MigrationService","type":"->","args":["16000Date20190427105638",false]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":294,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":241,"function":"doCoreUpgrade","class":"OC\\Updater","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":128,"function":"doUpgrade","class":"OC\\Updater","type":"->","args":["16.0.3.0","16.0.0.9"]},{"file":"\/home\/jenny\/domains\/nextcloud\/core\/ajax\/update.php","line":212,"function":"upgrade","class":"OC\\Updater","type":"->","args":[]}],"File":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php","Line":62,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER","Code":"HY000","Trace":[{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php","line":60,"function":"exec","class":"PDO","type":"->","args":["DELETE FROM `oc_collres_accesscache`"]},{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php","line":1085,"function":"exec","class":"Doctrine\\DBAL\\Driver\\PDOConnection","type":"->","args":["DELETE FROM `oc_collres_accesscache`"]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/Connection.php","line":216,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->","args":["DELETE FROM `oc_collres_accesscache`",[],[]]},{"file":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Query\/QueryBuilder.php","line":222,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->","args":["DELETE FROM `oc_collres_accesscache`",[],[]]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/QueryBuilder\/QueryBuilder.php","line":214,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/core\/Migrations\/Version16000Date20190427105638.php","line":50,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/MigrationService.php","line":470,"function":"preSchemaChange","class":"OC\\Core\\Migrations\\Version16000Date20190427105638","type":"->","args":[{"__class__":"OC\\Migration\\SimpleOutput"},{"__class__":"Closure"},{"tablePrefix":"oc_"}]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/DB\/MigrationService.php","line":411,"function":"executeStep","class":"OC\\DB\\MigrationService","type":"->","args":["16000Date20190427105638",false]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":294,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":241,"function":"doCoreUpgrade","class":"OC\\Updater","type":"->","args":[]},{"file":"\/home\/jenny\/domains\/nextcloud\/lib\/private\/Updater.php","line":128,"function":"doUpgrade","class":"OC\\Updater","type":"->","args":["16.0.3.0","16.0.0.9"]},{"file":"\/home\/jenny\/domains\/nextcloud\/core\/ajax\/update.php","line":212,"function":"upgrade","class":"OC\\Updater","type":"->","args":[]}],"File":"\/home\/jenny\/domains\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php","Line":60}},"CustomMessage":"--"},"userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/75.0.3770.100 Safari\/537.36","version":"16.0.0.9"} {"reqId":"khU8W08tfvieZyqPsASH","level":3,"time":"2019-07-09T13:19:12+00:00","remoteAddr":"192.168.255.93","user":"--","app":"updater","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=T8VbP1Tvrd%2FQ2NkXM7Zp974n1xBxAjibuXkpBguQ%2B%2Bw%3D%3AOY1tCzen3ayWvIlEWfQFpY5ChEYZYV3T%2Fkh4Y2aos4s%3D","message":"\\OC\\Updater::failure: Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'DELETE FROM `oc_collres_accesscache`':\n\nSQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER","userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/75.0.3770.100 Safari\/537.36","version":"16.0.0.9"} {"reqId":"khU8W08tfvieZyqPsASH","level":3,"time":"2019-07-09T13:19:12+00:00","remoteAddr":"192.168.255.93","user":"--","app":"updater","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=T8VbP1Tvrd%2FQ2NkXM7Zp974n1xBxAjibuXkpBguQ%2B%2Bw%3D%3AOY1tCzen3ayWvIlEWfQFpY5ChEYZYV3T%2Fkh4Y2aos4s%3D","message":"\\OC\\Updater::updateEnd: Update failed","userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/75.0.3770.100 Safari\/537.36","version":"16.0.0.9"} {"reqId":"khU8W08tfvieZyqPsASH","level":1,"time":"2019-07-09T13:19:12+00:00","remoteAddr":"192.168.255.93","user":"--","app":"updater","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=T8VbP1Tvrd%2FQ2NkXM7Zp974n1xBxAjibuXkpBguQ%2B%2Bw%3D%3AOY1tCzen3ayWvIlEWfQFpY5ChEYZYV3T%2Fkh4Y2aos4s%3D","message":"\\OC\\Updater::maintenanceActive: Maintenance mode is kept active","userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/75.0.3770.100 Safari\/537.36","version":"16.0.0.9"} {"reqId":"khU8W08tfvieZyqPsASH","level":1,"time":"2019-07-09T13:19:12+00:00","remoteAddr":"192.168.255.93","user":"--","app":"updater","method":"GET","url":"\/core\/ajax\/update.php?requesttoken=T8VbP1Tvrd%2FQ2NkXM7Zp974n1xBxAjibuXkpBguQ%2B%2Bw%3D%3AOY1tCzen3ayWvIlEWfQFpY5ChEYZYV3T%2Fkh4Y2aos4s%3D","message":"\\OC\\Updater::resetLogLevel: Reset log level to Warning(2)","userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/75.0.3770.100 Safari\/537.36","version":"16.0.0.9"} {"reqId":"cXVHxB5xunfFPxwpgixV","level":3,"time":"2019-07-09T13:23:22+00:00","remoteAddr":"192.168.255.93","user":"--","app":"jsresourceloader","method":"GET","url":"\/\/index.php\/settings\/integrity\/failed","message":"Could not find resource js\/config.js to load","userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/75.0.3770.100 Safari\/537.36","version":"16.0.0.9"} ```

Browser log

Browser log

``` JQMIGRATE: Migrate is installed, version 1.4.1 globals.js:30 The escapeHTML library is deprecated! It will be removed in nextcloud 19. i @ globals.js:30 (anonymous) @ l10n.js:113 (anonymous) @ l10n.js:108 translate @ l10n.js:131 P @ underscore.js:762 (anonymous) @ underscore.js:775 (anonymous) @ underscore.js:122 start @ update.js?v=3d25e510-7:37 (anonymous) @ update.js?v=3d25e510-7:156 dispatch @ jquery.js:4435 _.handle @ jquery.js:4121 ```

[maxolasersquad]

To work around this I set pxc_strict_mode to PERMISSIVE in my cluster SET GLOBAL pxc_strict_mode=PERMISSIVE, ran the upgrade, and then set it back to ENFORCING SET GLOBAL pxc_strict_mode=ENFORCING

[maxolasersquad]

I think the easiest "fix" for this would be to make sure all tables have a primary key. The harder solution would be to detect if this scenario is going to happen and then instruct the user how to work around it.

[MorrisJobke]

cc @nickvergessen @rullzer

[rullzer]

What apps do you have installed? As that doesn't look like a default table.

[kesselb]

DELETE FROM oc_collres_accesscache

https://github.com/nextcloud/server/blob/22de685f546474890ff43bc7d68754afe9fbd6f1/core/Migrations/Version16000Date20190427105638.php#L47-L50

Percona-XtraDB-Cluster prohibits use of DML command on a table (nextcloud.oc_collres_accesscache) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

Looks like percona does not like tables without primary keys https://www.percona.com/doc/percona-xtradb-cluster/LATEST/features/pxc-strict-mode.html#tables-without-primary-keys

https://github.com/nextcloud/server/blob/22de685f546474890ff43bc7d68754afe9fbd6f1/core/Migrations/Version16000Date20190207141427.php#L78-L105

Indeed there is no primary key on this table.

[maxolasersquad]

@rullzer my list of installed apps is listed in the ticket.

[rullzer]

Ah my bad I checked on my phone.

Anyways as @kesselb points is probably a percona issue...

[maxolasersquad]

Yes, it's definitely related to using Percona. If this table is related to an app I have installed I'll file a report with them. I feel like if it's a Nextcloud table there are some actions that can be taken on the Nextcloud side.

1. List Percona is unsupported.
2. Ensure all tables have a PK.
3. Detect when a migration will fail on Percona and provide the administrator with guidance about setting pxc_strict_mode to PERMISSIVE for upgrades. (I don't think it can be relied upon for Nextcloud to have permission to do that.)

[HeavyThumper]

I simply changed the Unique index for that table to a Primary - and the upgrade succeeded.

[kesselb]

The new sql_require_primary_key system variable makes it possible to have statements that create new tables or alter the structure of existing tables enforce the requirement that tables have a primary key. Enabling this variable helps avoid performance problems in row-based replication that can occur when tables have no primary key. Suppose that a table has no primary key and an update or delete modifies multiple rows. On the master server, this operation can be performed using a single table scan but, when replicated using row-based replication, results in a table scan for each row to be modified on the slave. With a primary key, these table scans do not occur. (Bug #17468242, Bug #69845, Bug #17005592, Bug #69223)

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html

sql_require_primary_key is OFF by default but they have a point regarding the row-based replication. collres_accesscache is used to cache some data. Should be possible to recreate the table with a primary key sometime.

[ghost]

Hi all,

I have a similar issue where server control has picked oc_collres_accesscache not having a primary key, but I don't really understand gitlab or anything mentioned. Lol.

I don't use Percona (I understand its built on top of Galera though) so should be the same, but how do I understand the solution to this? Do I need to remove the following lines from the php file as describe above:

                $this->connection
                        ->getQueryBuilder()
                        ->delete('collres_accesscache')
                        ->execute();

Sorry, if this is the wrong place to ask this, like I said I'm new to github and still struggling to understand the platform :-)

EDIT: Also, how come the alter command wouldn't suffice with adding an auto increment statement? For example:

alter table oc_collres_accesscache add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;

Please go easy on me, I'm new to Nextcloud, and only have ever worked on databases not in clusters and only to a certain extent :-) I am just trying to learn best I can.

[ghost]

@kesselb https://severalnines.com/database-blog/deploying-highly-available-nextcloud-mysql-galera-cluster-and-glusterfs

This article also states to add a primary key via this command:

ALTER TABLE nextcloud.oc_collres_accesscache ADD COLUMN `id` INT PRIMARY KEY AUTO_INCREMENT;

A bit different to the command I had but is this even safe in a cluster? Will it work? And will it affect my Nextcloud? What do you guys think?

Once again, please be kind to me, I'm still learning this stuff :-)

[bluikko]

I am seeing similar errors.

@HeavyThumper is it safe to convert the unique key to a PK? Mind to share the statements for the rest of us?

Does someone know if a future database migration that would fail due to the key being unique would cause the whole update to fail in future? Or could updates handle that gracefully?

[HeavyThumper]

@HeavyThumper is it safe to convert the unique key to a PK? Mind to share the statements for the rest of us?

Does someone know if a future database migration that would fail due to the key being unique would cause the whole update to fail in future? Or could updates handle that gracefully?

I'm not a Mysql/Percona/GaleraDB expert, but...

I don't have a statement handy for you - I just used one of the Mysql GUI tools (I used phpMyAdmin). As for the safety concerns - primary keys are unique keys by definition. So I cannot envision any situation where having a unique key additionally designated as a primary key would cause a problem.

[bluikko]

My concern is that if I change the unique key to PK and then later NextCloud decides to fix this there will be a database migration to do the same change again. Obviously that change would then fail. What happens at that point? Will the whole update fail? Will the database be left in a "half-migrated" state?

[HeavyThumper]

The NextCloud maintainers haven't felt like it for the past several versions...this issue comes up with crazy people (like us) who use XtraDB instead of "simple" MySQL - which doesn't complain about tables without PK's. But it's always possible...

As for what would happen - at least three possibilities:

1. The maintainers who implement the upgrade script will do so in a way that checks to see if a PK exists first prior to executing the ALTER TABLE command.
2. An ALTER TABLE command is executed blindly - won't make any changes, and will probably proceed without errors but with a logged message.
3. The update script will halt - requiring you to make a manual adjustment. Which we're talking about doing now anyway - so what's the worry?

I've never had an update fail with NextCloud versions so I trust them. However, as the saying goes, trust but backup first!

[bklang]

I've created a PR attempting to address this issue: https://github.com/nextcloud/server/pull/20934

[MorrisJobke]

@bklang While looking at this, I noticed that we have some tables that are intentionally without a primary key - for example oc_filecache_extended. This is basically a table with additional rows for the filecache, because adding them to the existing table would cause a complete copy of the table and this could take hours if not days on big installations. So there only a unique index is present. Does this work on your Percona setup?

The table was added in 17: https://github.com/nextcloud/server/blob/cb057829f72c70e819f456edfadbb29d72dba832/core/Migrations/Version17000Date20190514105811.php#L70

So maybe this was due to something else? Or maybe a migration on this table?

[bluikko]

Does this work on your Percona setup?

@MorrisJobke unique key is not enough - to my knowledge it needs to be exactly primary key.

[MorrisJobke]

@MorrisJobke unique key is not enough - to my knowledge it needs to be exactly primary key.

I guess we need to dig deeper then, because the instance from @bklang runs on 18 and there is definitely a table without a primary index in there. Seem to be also only triggered during a migration.

[kesselb]

@MorrisJobke https://github.com/nextcloud/server/issues/16311#issuecomment-509718088

delete from triggers that error. filecache_extended might be a problem. But we could use the fileid as primary key without autoincrement.

[bklang]

@MorrisJobke As said above, yes a unique index is not enough. Percona's XtraDB requires any DML operation to be on a table with a primary key. The reason this shows up during migrations is the DELETE FROM oc_collres_accesscache, which occurs after the table is created. It would occur any time someone tries to insert or remove data from that table without a PK, so it would eventually show up in application logs as exceptions from failed queries. Most of the tables lacking PKs seem to have no traffic (at least in my installation), which is why the problem does not appear.

[MorrisJobke]

But we could use the fileid as primary key without autoincrement.

Ah right ... yes, that might work. We need to check if this is possible through the abstractions we use and does not cause trouble in other DBs.

[MorrisJobke]

Everybody that asks what DML is: https://stackoverflow.com/a/44796508/520507 ;) It's basically the main data manipulation operations (Select, insert, update, delete, ...).

[maxolasersquad]

I don't think there's any reason why a table that has a single column with a unique constraint couldn't be made into the primary key. For example oc_filecache_extended.fileid has unique constraint fce_fileid_idx. If that column where to be turned into the primary key for the table everything should function as usual and Percona would understand how to replicate the data safely.

[kesselb]

Percona would understand how to replicate the data safely.

https://github.com/nextcloud/server/issues/16311#issuecomment-579260770 also important for row based replication with MySQL.

[simon-zumbrunnen]

I tried to deploy nextcloud using a Digital Ocean Managed MySQL Database and had the same problem:

https://www.digitalocean.com/docs/databases/#5-june-2020

[PrivatePuffin]

Ensure all tables have a PK.

Wait, who designed that? Isn't "always define a PK, if you can", somethings that is so much of a basic that everyone with any training should know this? . >.<

[solracsf]

This problem affects any type of MySQL cluster, not only Percona. Galera (default for MariaDB) or MySQLCluster are also affected. Products Like SkySQL or managed DBs like DigitalOcean, Scaleway, etc, all cluster based, are problematic.

MariaDB states:

All tables should have a primary key (multi-column primary keys are supported). DELETE operations are unsupported on tables without a primary key. Also, rows in tables without a primary key may appear in a different order on different nodes.

Percona states:

Percona-XtraDB-Cluster prohibits use of DML command on a table without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

DigitalOcean states:

Beginning 04 June 2020, you are required to create a primary key for each new table in any DigitalOcean Managed MySQL Database.

Etc.

This can lead to a number of problems if we try to scale out Nextcloud.

On one instance, these are the tables without PK:

SELECT tab.table_schema AS database_name, tab.table_name FROM information_schema.tables tab LEFT JOIN information_schema.table_constraints TCO ON tab.table_schema = tco.table_schema AND tab.table_name = tco.table_name AND tco.constraint_type = 'PRIMARY KEY' WHERE tco.constraint_type IS NULL AND tab.table_schema NOT IN('mysql', 'information_schema', 'performance_schema', 'sys') AND tab.table_type = 'BASE TABLE' ORDER BY tab.table_schema, tab.table_name;

| ISnP3oT5      | oc_circle_gsevents          |
| ISnP3oT5      | oc_collres_accesscache      |
| ISnP3oT5      | oc_collres_resources        |
| ISnP3oT5      | oc_comments_read_markers    |
| ISnP3oT5      | oc_federated_reshares       |
| ISnP3oT5      | oc_filecache_extended       |
| ISnP3oT5      | oc_notifications_pushtokens |
| ISnP3oT5      | oc_systemtag_object_mapping |
| ISnP3oT5      | oc_talk_guests              |
| ISnP3oT5      | oc_talk_participants        |
| ISnP3oT5      | oc_talk_signaling           |

So this go beyond the oc_collres_accesscache table.

[PrivatePuffin]

This problem affects any type of MySQL cluster, not only Percona. Galera (default for MariaDB) or MySQLCluster are also affected. Products Like SkySQL or managed DBs like DigitalOcean, Scaleway, etc, all cluster based, are problematic.

Yeah thats true... I find it rather odd that, considering nextcloud is also sold to enterprise customers, this issue takes more than 15 months to adress by now. While it even is a relatively easy issue to fix and does indeed affect all major clustering-db solutions.

[nickvergessen]

While it even is a relatively easy issue to fix

Unluckily it's not that easy because not all our supported databases allow adding a primary key column with autoincrement afterwards

[PrivatePuffin]

While it even is a relatively easy issue to fix

Unluckily it's not that easy because not all our supported databases allow adding a primary key column with autoincrement afterwards

Thank you, that indeed explains the timeframe this issue is taking. I stand corrected. Might I inquire which databases don't?

[MorrisJobke]

Will be done via https://github.com/nextcloud/server/pull/23882/commits/838b02c25487dea14e0b055af2746ee79e49de3e

[solracsf]

Thanks @nickvergessen

Will this be backported (20 and 19) or the v21 milestone is immutable?

[MorrisJobke]

Fixed with #23882 and will be back ported to 20 via #24049

[nickvergessen]

Not to 19, if you run a cluster you need to update to 20

[gamunu]

The issue still persists if anyone looking for a solution for DigitalOcean cloud

https://github.com/nextcloud/server/blob/master/lib/private/DB/ConnectionFactory.php#L133

Replace the above line with

$eventManager->addEventSubscriber(
                    new SQLSessionInit("SET SESSION AUTOCOMMIT=1,sql_require_primary_key = 0"));

[nickvergessen]

Which table is missing one?🤔

[bluikko]

The issue still persists if anyone looking for a solution for DigitalOcean cloud

https://github.com/nextcloud/server/blob/master/lib/private/DB/ConnectionFactory.php#L133

Replace the above line with

$eventManager->addEventSubscriber(
                  new SQLSessionInit("SET SESSION AUTOCOMMIT=1,sql_require_primary_key = 0"));

But this is a workaround to make DML work even when PK is missing - if the PK is missing DML is not allowed for a reason. By disabling requirement for PK like this you are disabling a protection. It would be much better to add PKs to tables...

@nickvergessen In my NextCloud 21 the table oc_ratelimit_entries is missing PRIMARY KEY.

[gamunu]

@bluikko, I agree. But I could not wait for a fix.

[nickvergessen]

Fix is in https://github.com/nextcloud/server/pull/30235