Unable to install new apps because of "constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata`"

ona · June 12, 2024, 8:38am

After updating to NextCloud 28.0.6 form 27.1.7.I had problem updating Bookmarks app.I got the error:

ERROR: An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata` failed for `nextcloud`.`oc_file_metadata`

I had to use occ to

occ upgrade 
occ  app:disable bookmarks
occ maintenance:mode --off

so to get the site back. But since then, I can’t add apps or update the ones that I have because I get always the same error. I also tried

occ files:scan --all --generate-metadata 
occ files:cleanup

but the error is still there.

I have searched and the only result was this

github.com/nextcloud/server

[Bug]: Error migrating from Nextcloud 27 to 28

opened 01:32PM - 09 Mar 24 UTC

closed 02:59PM - 09 Mar 24 UTC

csh4rp

bug 0. Needs triage 28-feedback

### ⚠️ This issue respects the following points: ⚠️ - [X] This is a **bug**, no…t a question or a configuration/webserver/proxy issue. - [X] This issue is **not** already reported on [Github](https://github.com/nextcloud/server/issues?q=is%3Aopen+is%3Aissue+label%3Abug) OR [Nextcloud Community Forum](https://help.nextcloud.com/) _(I've searched it)_. - [X] Nextcloud Server **is** up to date. See [Maintenance and Release Schedule](https://github.com/nextcloud/server/wiki/Maintenance-and-Release-Schedule) for supported versions. - [X] I agree to follow Nextcloud's [Code of Conduct](https://nextcloud.com/contribute/code-of-conduct/). ### Bug description When I run the migration for nextcloud from 27 to 28 this error occurs: Updating database schema Exception: Database error when running migration 28000Date20231004103301 for app core An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata` failed for `nextcloud`.`oc_file_metadata` This migrations script seems to be only creating a table, so I'm not sure what's it all about. ### Steps to reproduce 1. Changed docker base image to 28-fpm 2. Restarted nextcloud ### Expected behavior Application should be updated ### Installation method None ### Nextcloud Server version 28 ### Operating system RHEL/CentOS ### PHP engine version None ### Web server Nginx ### Database engine version MariaDB ### Is this bug present after an update or on a fresh install? None ### Are you using the Nextcloud Server Encryption module? Encryption is Disabled ### What user-backends are you using? - [X] Default user-backend _(database)_ - [ ] LDAP/ Active Directory - [ ] SSO - SAML - [ ] Other ### Configuration report _No response_ ### List of activated Apps _No response_ ### Nextcloud Signing status _No response_ ### Nextcloud Logs _No response_ ### Additional info _No response_

The reporter solved it:

Turns out table file_metadata had stored location as text instead of json from one of mine devices and simply deleting this data that was not in JSON format fixed the issue. Still seems like a bug that either migration did not change it or that it was possible to put it there in the first place.

but since the issue is closed I can’t ask how. I have been looking in the table, but I don’t find any malformed string.
how can I resolve the problem, please? thanks a lot for helping

Nextcloud version: _ 28.0.6_
Operating system and version: Debian 11.9
Apache version: _ 2.4.59_
mysql version: _ 10.5.23_
PHP version: _ 8.1.29_

The issue you are facing:

Is this the first time you’ve seen this error?

As I said, the first time updating bookmarks and then each time I try to update an app or install one.

Steps to replicate it:

In apps interface, try to add a new app, like Announcement Center or Cookbook (I didn’t need them, because of that I installed to test)
I get the error in the interface

An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata` failed for `nextcloud`.`oc_file_metadata`

The output of your Nextcloud log in Admin > Logging:

 DriverException An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata` failed for `nextcloud`.`oc_file_metadata`
could not enable apps

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'passwordsalt' => 'edited',
  'secret' => 'edited',
  'trusted_domains' => 
  array (
    0 => 'edited',
  ),
  'datadirectory' => '/var/www/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '28.0.3.2',
  'dbname' => 'edited',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'edited',
  'mysql.utf8mb4' => true,
  'dbuser' => 'edited',
  'dbpassword' => 'edited',
  'installed' => true,
  'instanceid' => 'edited',
  'upgrade.disable-web' => true,
  'updatechecker' => false,
  'default_language' => 'ca',
  'preview_max_x' => 100,
  'preview_max_y' => 100,
  'activity_use_cached_mountpoints' => true,
  'mail_domain' => 'edited',
  'mail_from_address' => 'nextcloud',
  'mail_smtpmode' => 'smtp',
  'mail_smtpauthtype' => 'LOGIN',
  'mail_smtphost' => 'edited',
  'mail_smtpport' => '465',
  'mail_smtpsecure' => 'ssl',
  'default_phone_region' => 'ES',
  'logtimezone' => 'Europe/Madrid',
  'log_rotate_size' => 104857600,
  'profile.enabled' => false,
  'login_form_autocomplete' => false,
  'maintenance' => false,
  'overwrite.cli.url' => 'edited',
  'loglevel' => 2,
  'trashbin_retention_obligation' => 'auto, 30',
  'app_install_overwrite' => 
  array (
    0 => 'occweb',
  ),
  'memcache.local' => '\\OC\\Memcache\\APCu',
);

The output of your Apache/nginx/system log in /var/log/____:

I have made

grep -r '23000' /var/log/*

with no results

Output errors in nextcloud.log in /var/www/ or as admin user in top right menu, filtering for errors. Use a pastebin service if necessary.

{"reqId":"Ru0HWIxeJlzMKz0WUHPW","level":3,"time":"2024-06-12T10:25:58+02:00","remoteAddr":"185.219.136.221","user":"edited-user","app":"settings","method":"POST","url":"/index.php/settings/apps/enable","message":"could not enable apps","userAgent":"Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:126.0) Gecko/20100101 Firefox/126.0","version":"28.0.3.2","exception":{"Exception":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata` failed for `nextcloud`.`oc_file_metadata`","Code":4025,"Trace":[{"file":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1938,"function":"convert","class":"Doctrine\\DBAL\\Driver\\API\\MySQL\\ExceptionConverter","type":"->"},{"file":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1880,"function":"handleDriverException","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1105,"function":"convertExceptionDuringQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Connection.php","line":261,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Migrator.php","line":162,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Migrator.php","line":67,"function":"applySchema","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Connection.php","line":588,"function":"migrate","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/MigrationService.php","line":450,"function":"migrateToSchema","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/MigrationService.php","line":401,"function":"migrateSchemaOnly","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/Installer.php","line":155,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/www/nextcloud/nextcloud/apps/settings/lib/Controller/AppSettingsController.php","line":454,"function":"installApp","class":"OC\\Installer","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":230,"function":"enableApps","class":"OCA\\Settings\\Controller\\AppSettingsController","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":137,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/App.php","line":184,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/Route/Router.php","line":315,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/nextcloud/lib/base.php","line":1069,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/nextcloud/index.php","line":39,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php","Line":117,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDO\\Exception","Message":"SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata` failed for `nextcloud`.`oc_file_metadata`","Code":4025,"Trace":[{"file":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php","line":76,"function":"new","class":"Doctrine\\DBAL\\Driver\\PDO\\Exception","type":"::"},{"file":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1100,"function":"query","class":"Doctrine\\DBAL\\Driver\\PDO\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Connection.php","line":261,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Migrator.php","line":162,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Migrator.php","line":67,"function":"applySchema","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Connection.php","line":588,"function":"migrate","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/MigrationService.php","line":450,"function":"migrateToSchema","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/MigrationService.php","line":401,"function":"migrateSchemaOnly","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/Installer.php","line":155,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/www/nextcloud/nextcloud/apps/settings/lib/Controller/AppSettingsController.php","line":454,"function":"installApp","class":"OC\\Installer","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":230,"function":"enableApps","class":"OCA\\Settings\\Controller\\AppSettingsController","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":137,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/App.php","line":184,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/Route/Router.php","line":315,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/nextcloud/lib/base.php","line":1069,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/nextcloud/index.php","line":39,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php","Line":28,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[23000]: Integrity constraint violation: 4025 CONSTRAINT `oc_file_metadata.metadata` failed for `nextcloud`.`oc_file_metadata`","Code":"23000","Trace":[{"file":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php","line":71,"function":"query","class":"PDO","type":"->"},{"file":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1100,"function":"query","class":"Doctrine\\DBAL\\Driver\\PDO\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Connection.php","line":261,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Migrator.php","line":162,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Migrator.php","line":67,"function":"applySchema","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/Connection.php","line":588,"function":"migrate","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/MigrationService.php","line":450,"function":"migrateToSchema","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/DB/MigrationService.php","line":401,"function":"migrateSchemaOnly","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/Installer.php","line":155,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/www/nextcloud/nextcloud/apps/settings/lib/Controller/AppSettingsController.php","line":454,"function":"installApp","class":"OC\\Installer","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":230,"function":"enableApps","class":"OCA\\Settings\\Controller\\AppSettingsController","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":137,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/AppFramework/App.php","line":184,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/nextcloud/lib/private/Route/Router.php","line":315,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/nextcloud/lib/base.php","line":1069,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/nextcloud/index.php","line":39,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php","Line":71}},"message":"could not enable apps","exception":{},"CustomMessage":"could not enable apps"}}

ona · June 12, 2024, 8:45am

Pastebin for the nextcloud log

jtr · June 12, 2024, 2:30pm

Is your dbtableprefix something other than oc_?

After updating to NextCloud 28.0.6 form 27.1.7

Since you updated from 27.1.7 rather than the latest maintenance release within 27.1 at the time, you may have hit [stable27] Use table prefix in metadata migration by artonge · Pull Request #43971 · nextcloud/server · GitHub which was fixed in 27.1.8.

'version' => '28.0.3.2',

Your config suggests you’re actually running 28.0.3.2.

What install method are you using? How did you update?

ona · June 13, 2024, 7:08am

Thanks a lot @jtr

Is your dbtableprefix something other than oc_?

no, it is oc_

What install method are you using? How did you update?

I am in a provider that updates the NextCloud a couple of times a year.
I have another installation and has no problems, but it is new, no with upgrade.
They use Ansible and Puppet.
I have access with ssh if you want me to check anything.

The first time I had this problem was updating the app Bookmarks and I have disabled. Can this be related?

Also, if you know a way to check the oc_file_metadata.metadata, maybe we can resolve the problem.

Have a good day

ona · June 13, 2024, 9:53am

I just checked the oc_file_metadata.metadata and is empty in all the rows.
I run
sudo -u nextcloud php occ files:scan --all --generate-metadata
and in all the files I get one of this 2 errors

Error during scan: exif_read_data(): File not supported
Error during scan: exif_read_data(): Incorrect APP1 Exif Identifier Code

The files are files generated by OnlyOffice and Collabora (odt, docx, ods, etc) in NextCloud, or images (jpg, png, etc) or .md mostly.

A month ago, the server was updated to Debian 11 from 10. Can be something that is not installed?

Thanks a lot

ona · June 13, 2024, 10:22am

I have just check and all php modules required are installed.

ona · June 13, 2024, 3:47pm

Still with the problem.
Now I have look in a new 28.0.3 installation and it has not the oc_file_metadata table.

I have search and didn’t find any document with the tables database explanation. Do you know when it is created? Thanks

Maadix · June 14, 2024, 8:01am

Hi,
I am the person providing the installation to @ona.
The Nexctloud is installed from tar.gz with Mysql and Apache.

I printed the query that is throwing the exception:

ALTER TABLE oc_file_metadata CHANGE metadata metadata JSON NOT NULL COMMENT '(DC2Type:json)

I am lost. I don’t understand why this ALTER TABLE is running.

show CREATE TABLE oc_file_metadata returns:


 oc_file_metadata | CREATE TABLE `oc_file_metadata` (
  `id` bigint(20) NOT NULL,
  `group_name` varchar(50) NOT NULL,
  `metadata` longtext NOT NULL COMMENT '(DC2Type:json)',
  `value` longtext DEFAULT NULL,
  PRIMARY KEY (`id`,`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

I have imported the database into a test server (just the datatbase, without the data folder) and it gives no error. Apps can be enabled.

I have tried adding ‘enable_file_metadata’ => false, to config.php but nothing changed.

ona · June 14, 2024, 9:10am

‘enable_file_metadata’ got deprecated
but in documentation is not changed
https://docs.nextcloud.com/server/28/admin_manual/configuration_server/config_sample_php_parameters.html#enable-file-metadata

here a big thanks for the people that makes documentation

ona · June 15, 2024, 9:22am

Following instructions from @Maadix , I deleted oc_file_metadata.metadata and now I can again install apps.