Integrity constraint violation in document table due to duplicate id

[punkyard]

Hi, i'll try to make it clear: i've installed many instances of nextcloud, installed many apps for our team, for NC being a collaborative platform, not just a file sharing tool. During the last weeks and months, I've add many problems with nextcloud, trying to make it function normally. It doesn't. I can be more specific now: the main issue being the Text App: it would not load, would not open a file or refuse to edit it or delete it:

in Collectives:

I thought it was because of the hestia panel, it's not - i've had the same issue with manual install, snap install, AIO install. A friend put me on a lead: it could be a server config problem or a conflict with another app. So far I don't know, but that for sure creates logs and crashes!

To Reproduce
Steps to reproduce the behavior:

1. Install Nextcloud 24.0.4.1 on ubuntu 22.04 with apache 2.4.54 nginx 1.23.1 Maria DB 10.6.7 and apps:

Activities for shared file downloads 1.13.0
Activity 2.16.0 Featured
Announcement center 6.3.1 Featured
AppOrder 0.15.0
Approval 1.0.10
Breeze Dark 24.0.2
Brute-force settings 2.4.0 Featured
Calendar 3.4.2 Featured
Circles 24.0.0 Featured
Collaborative tags 1.14.0 Featured
Comments 1.14.0 Featured
Community Document Server 0.1.12
Contacts 4.2.0 Featured
Contacts Interaction 1.5.0 Featured
Dashboard 7.4.0 Featured
Deck 1.7.1 Featured
Deleted files 1.14.0 Featured
Federation 1.14.0 Featured
File sharing 1.16.2 Featured
Group folders 12.0.1 Featured
Group quota 0.1.8
Impersonate 1.11.0 Featured
Log Reader 2.9.0 Featured
Mail 1.13.7 Featured
Mind Map 0.0.26
Monitoring 1.14.0 Featured
Nextcloud announcements 1.13.0 Featured
Notifications 2.12.0 Featured
ONLYOFFICE 7.5.4 Featured
Password policy 1.14.0 Featured
PDF viewer 2.5.0 Featured
Photos 1.6.0 Featured
Pico CMS 1.0.19
Privacy 1.8.0 Featured
Quota warning 1.14.0
Ransomware protection 1.13.0
Recommendations 1.3.0 Featured
Right click 1.3.0 Featured
Share by mail 1.14.0 Featured
Talk 14.0.3 Featured
Talked 0.4.0
Tasks 0.14.4 Featured
Text 3.5.1 Featured
Theming 1.15.0 Featured
TimeManager 0.3.2
Unrounded Corners 1.0.3
Update notification 1.14.0 Featured
User status 1.4.0 Featured
Versions 1.17.0 Featured
Video player 1.13.0 Featured
Weather status 1.4.0 Featured
Welcome 1.0.1
Zipper

(many of these apps have been disabled trying to find if one was causin a conflict. Each time i disable an app, Text app will manage to open normally a document. Few minutes later, the error comes back)

2. create a text file with "+" button in 'Files' (with Text app) give it a name

3. Text App opens the new file, the page is blanck

In Collectives, the display is different, the reaction is different when trying to edit or rename:
the 'edit' wheel won't stop spinning, an error message warns: impossible to rename

5. An error is not always displayed in 'Settings/Logging'. As this morning, the whole website went down as i was trying to edit the files that you can see in the pictures. There was no log in 'Logging'. Rebooted. I tried again, the server didn't go down, and I found this log:

`[text] Error: OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1682' for key 'PRIMARY' at <>

0. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/lib/private/DB/QueryBuilder/QueryBuilder.php line 329
   OC\DB\Exceptions\DbalException::wrap()
1. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/lib/public/AppFramework/Db/QBMapper.php line 139
   OC\DB\QueryBuilder\QueryBuilder->executeStatement()
2. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/apps/text/lib/Service/DocumentService.php line 157
   OCP\AppFramework\Db\QBMapper->insert()
3. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/apps/text/lib/Service/ApiService.php line 102
   OCA\Text\Service\DocumentService->createDocument()
4. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/apps/text/lib/Controller/SessionController.php line 50
   OCA\Text\Service\ApiService->create()
5. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/lib/private/AppFramework/Http/Dispatcher.php line 225
   OCA\Text\Controller\SessionController->create()
6. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/lib/private/AppFramework/Http/Dispatcher.php line 133
   OC\AppFramework\Http\Dispatcher->executeController()
7. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/lib/private/AppFramework/App.php line 172
   OC\AppFramework\Http\Dispatcher->dispatch()
8. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/lib/private/Route/Router.php line 298
   OC\AppFramework\App::main()
9. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/lib/base.php line 1023
   OC\Route\Router->match()
10. /home/koraprod/web/kolab.koraland.net/public_html/nextprod2022/index.php line 36
    OC::handleRequest()

PUT /apps/text/session/create
from 93.5.120.234 by Webmaster at 2022-08-30T11:17:55+00:00`

Expected behavior
Open .md files, edit files, rename files in 'Files' and 'Collectives', close file.

same behavior on different Clients:

• mac os 10 14 6 / Windows 10 / Linux lite
• Safari, Brave, Epic, firefox

Server details

You can use the Issue Template application to prefill most of the required information: https://apps.nextcloud.com/apps/issuetemplate

I have installed Issue Template (which is listed in red amongst other apps). Going to the Setting menu, clicking '! Issue Template" shows a blank screen:

Text app version: 3.5.1

main Operating system:
Mac os 10 14 6

Web server:
Ubuntu 22.04.1 LTS (GNU/Linux 5.15.0-46-generic x86_64)

Database:
Maria DB 10.6.7

PHP version:
php -v
PHP 8.1.9 (cli) (built: Aug 15 2022 09:40:11) (NTS)
Zend Engine v4.1.9, Copyright (c) Zend Technologies

Nextcloud version:
Nextcloud 24.0.4.1

Logs

Nextcloud log of the last 3 days

{"reqId":"FUASRj1n3mCDo3aSIpCW","level":2,"time":"2022-08-28T13:11:32+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"8thoQ37rlbY28TvNYJGF","level":2,"time":"2022-08-28T19:17:34+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"FLlNlqusxegx3Yhw88LK","level":3,"time":"2022-08-28T20:15:42+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"UGRoAhnOltWkpWm98EUn","level":3,"time":"2022-08-28T20:19:24+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"OoGwst30SN0IuD8zrBB3","level":3,"time":"2022-08-28T20:21:28+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"1OticEz3KAxy7XjsZ0NH","level":3,"time":"2022-08-28T20:21:58+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"BjRwW8oUjHiaeJHa3Ii7","level":3,"time":"2022-08-28T20:47:13+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"g5YFAoteUkDrcI4gBaea","level":3,"time":"2022-08-28T20:51:55+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"L5N9oanrgrNI6PdkHKiw","level":3,"time":"2022-08-28T20:51:57+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"EzDXauvMo6UCerFrod4U","level":3,"time":"2022-08-28T20:52:05+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"OucG6K8VgG8gLtkv8DAT","level":3,"time":"2022-08-28T20:52:06+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"JMACOGgCeQInzQT9mlVU","level":3,"time":"2022-08-28T20:58:01+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"cGxaupsAtzXh9v8uodLg","level":3,"time":"2022-08-28T20:59:06+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"o4Y5j9SdMmJcLO5ycF19","level":3,"time":"2022-08-28T21:04:14+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"lVmhWLolXX7IomaN3YgY","level":3,"time":"2022-08-28T21:04:37+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"tcaelAelDn9KnIM4XHh4","level":3,"time":"2022-08-28T21:18:02+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"verNFGV2jTYOZnILHmj0","level":3,"time":"2022-08-28T21:26:50+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"QQ8pKxwAyarghtjFzPON","level":3,"time":"2022-08-28T22:26:21+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"WXow5Z0JHy4A6vw0kgAS","level":2,"time":"2022-08-29T11:09:49+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"TWeXc5YeI78BE2rpDqFl","level":2,"time":"2022-08-29T11:10:15+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"5RVVNBr8aAKnIBVxBemI","level":3,"time":"2022-08-29T17:31:54+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"3l8j9LMsZipy3AEKbJaQ","level":3,"time":"2022-08-29T17:47:09+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"znxC4BZUF5Nh7IZMZ50Y","level":3,"time":"2022-08-29T17:47:25+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"OFkl1zizI5c0DwpcEZm2","level":3,"time":"2022-08-29T17:48:17+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"trOKzYy0u6AV5e5kslRC","level":3,"time":"2022-08-29T17:48:22+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"BmvmrTSgC7pSktv6ECL4","level":3,"time":"2022-08-29T18:59:53+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"9OT76UTmEYs8luDqyZ8O","level":3,"time":"2022-08-29T19:00:06+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"hMgfTS0ZDW42UzwYJygf","level":3,"time":"2022-08-29T19:02:16+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"20esSimNntIWp5ByHZYB","level":3,"time":"2022-08-29T22:03:54+00:00","remoteAddr":"93.5.120.234","user":"koralandprod","app>
{"reqId":"tIHPT2HUvFL961DNzN9M","level":3,"time":"2022-08-30T11:17:55+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">
{"reqId":"iad1oxo5vWkhXQXJ5GVx","level":3,"time":"2022-08-30T11:50:23+00:00","remoteAddr":"93.5.120.234","user":"Webmaster","app":">

Browser log

Safari

Brave

`'use strict';

var enhanceError = require('./enhanceError');

/**

• Create an Error with the specified message, config, error code, request and response.
• @param {string} message The error message.
• @param {Object} config The config.
• @param {string} [code] The error code (for example, 'ECONNABORTED').
• @param {Object} [request] The request.
• @param {Object} [response] The response.
• @returns {Error} The created error.
  */
  module.exports = function createError(message, config, code, request, response) {
  var error = new Error(message);
  return enhanceError(error, config, code, request, response);
  };
  `

`'use strict';

var createError = require('./createError');

/**

• Resolve or reject a Promise based on response status.
• @param {Function} resolve A function that resolves the promise.
• @param {Function} reject A function that rejects the promise.
• @param {object} response The response.
  */
  module.exports = function settle(resolve, reject, response) {
  var validateStatus = response.config.validateStatus;
  if (!response.status || !validateStatus || validateStatus(response.status)) {
  resolve(response);
  } else {
  reject(createError(
  'Request failed with status code ' + response.status,
  response.config,
  null,
  response.request,
  response
  ));
  }
  };`

I'm really trying my best to make it clear - it's only six months i've started using a VPS.
There is a conflict somewhere (according to a developper in Paris that took a quick look at it) but I can't point at it yet.
Please tell me if you'd like more info and logs to put some light on it.
thank you very much

[punkyard]

CONFIG.CONF
(all cache mods have been removed from config.php in order to isolate the cause)

<?php
$CONFIG = array (
  'instanceid' => '***',
  'passwordsalt' => '***',
  'secret' => '***',
  'trusted_domains' => 
  array (
    0 => '***',
  ),
  'default_phone_region' => 'FR',
  'datadirectory' => '/public_html/data',
  'skeletondirectory' => '/public_html/data/skeleton',
  'templatedirectory' => '',
  'dbtype' => 'mysql',
  'version' => '24.0.4.1',
  'overwrite.cli.url' => '***',
  'dbname' => '***',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => '***',
  'dbpassword' => '***',
  'installed' => true,
  'mail_from_address' => '***',
  'mail_smtpmode' => 'smtp',
  'mail_sendmailmode' => 'smtp',
  'mail_domain' => '***',
  'mail_smtpsecure' => 'ssl',
  'mail_smtpauthtype' => 'LOGIN',
  'mail_smtpauth' => 1,
  'mail_smtphost' => '***',
  'mail_smtpport' => '465',
  'mail_smtpname' => '***',
  'mail_smtppassword' => '***',
  'allow_user_to_change_display_name' => true,
  'loglevel' => 2,
  'htaccess.RewriteBase' => '/',
  'maintenance' => false,
  'app_install_overwrite' => 
  array (
    0 => 'issuetemplate',
  ),
);

[max-nextcloud]

Hi @punkyard,

I'm sorry to hear you are facing these issues. Thanks for reporting.

The trace you pasted looks like it's trying to create a document entry in the table for a file that already has one. That's odd.
I checked the codepath and it looks like the fileId is used as the id in the documents table - which can lead to the constraint violation in the trace.

I'll focus on the constraint violation - but frankly it looks like something is wrong with the setup. Therefore I'd like to ask you to either raise a support ticket in case you have an enterprise subscription or head over to the forum to get help.

There's documentation for how to install nextcloud on ubuntu 22.04. I'd recommend to follow that as close as possible.

Other things that might be causing issues are using some form of reverse proxy, load balancer or clustered setup as well as runninng nextcloud for multiple domains on the same instance.

[max-nextcloud]

@punkyard I had another look at this issue. I have not seen other reports of it. Could you check with a more recent version of Nextcloud again?

If the issue still exists please upload the actual log file or the last lines of it. The paste in the issue description is cut of at the end with a > character:

This is effectively removing the most useful information.

[punkyard]

Hi,
back on this conversation
I moved to AiO since.
This issue is no more actual for me.