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

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

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 :slight_smile:

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:

  1. 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)
  2. 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"}}

Pastebin for the nextcloud log

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?

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

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

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

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

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.

‘enable_file_metadata’ got deprecated :frowning:
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 :purple_heart:

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

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.