There is a problem in the comments section of the NextCloud

ℹ️ Support
, , , ,
1

Nextcloud version : 29.0.7
Operating system and version : Ubuntu 22
Apache or nginx version: Apache
PHP version : 8.1.2

The issue you are facing:

Is this the first time you’ve seen this error? (Y/N):

Steps to replicate it:

The output of your Nextcloud log in Admin > Logging:

NotNullConstraintViolationException An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" of relation "oc_comments" violates not-null constraint DETAIL: Failing row contains (null, 0, 0, 0, users, jhone, test messege, comment, 2024-09-24 15:42:32, null, files, 43655, null, null, null, null).

Unfortunately, I have had this problem for some time
Comments are not registered anywhere and such an error appears
Even in the talk program, there is such a problem that null is sent in the recorded error, and SQL does not allow null to be recorded in the id column.

Please advise what is the problem and how can this problem be solved?

I also updated the database tables with the raw version, but it was not solved
I also updated the NextCloud version, but it didn’t solve it

2

Hello @khayyen in the forum.

Please update the issue with the basic information: What is the exact problem and when do you get the problem? That way others can try to reproduce and test it. Ideally, you will get a good answer. The more information you share the more likely someone will be able to reproduce.

Therefore please fill out the steps to replicate and the clear description.

Christian

1 Like
3

The problem happened when NextCloud was moved from one server to another and this problem occurred
The problem is that the oc_comments table does not work in general, and whatever is related to this table, no data is recorded in it.
For example, comments for files
Comment on the desk
talk messages

And what appears in the error is that the id column cannot be null

4

How did you do that.
Please describe exactly, step by step.

ernolf

1 Like
5

This is not readily reproducible.

I believe NotNullConstraintViolationException should be part of a stack trace, so please post the full raw log entry.

Please also:

  • confirm integrity check passes occ integrity:check-core
  • post your config occ config:list system (we need to know what db you’re using and how it’s set up)
  • describe how you moved the database to the new server

Also, probably not a bad idea to tell us if there are any warnings or errors appearing under the setup checks (Admin settings->Overview).

6

Full report on the error that occurred:

{
  "reqId": "Z0Snk8S0mPvyYdt5O5JE",
  "level": 3,
  "time": "2024-09-25T01:59:04+03:30",
  "remoteAddr": "212.86.84.121",
  "user": "mammad",
  "app": "webdav",
  "method": "POST",
  "url": "/remote.php/dav/comments/files/36422",
  "message": "An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"id\" of relation \"oc_comments\" violates not-null constraint\nDETAIL:  Failing row contains (null, 0, 0, 0, users, mammad, test text, comment, 2024-09-24 22:29:04, null, files, 36422, null, null, null, null).",
  "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:130.0) Gecko/20100101 Firefox/130.0",
  "version": "29.0.7.1",
  "exception": {
    "Exception": "Doctrine\\DBAL\\Exception\\NotNullConstraintViolationException",
    "Message": "An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"id\" of relation \"oc_comments\" violates not-null constraint\nDETAIL:  Failing row contains (null, 0, 0, 0, users, mammad, test text, comment, 2024-09-24 22:29:04, null, files, 36422, null, null, null, null).",
    "Code": 7,
    "Trace": [
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1943,
        "function": "convert",
        "class": "Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1885,
        "function": "handleDriverException",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1213,
        "function": "convertExceptionDuringQuery",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
        "line": 292,
        "function": "executeStatement",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
        "line": 383,
        "function": "executeStatement",
        "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
        "line": 393,
        "function": "executeStatement",
        "class": "OC\\DB\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
        "line": 280,
        "function": "execute",
        "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
        "line": 1140,
        "function": "execute",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
        "line": 1096,
        "function": "insert",
        "class": "OC\\Comments\\Manager",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
        "line": 244,
        "function": "save",
        "class": "OC\\Comments\\Manager",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
        "line": 121,
        "function": "createComment",
        "class": "OCA\\DAV\\Comments\\CommentsPlugin",
        "type": "->",
        "args": [
          "*** sensitive parameters replaced ***"
        ]
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
        "line": 89,
        "function": "httpPost",
        "class": "OCA\\DAV\\Comments\\CommentsPlugin",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 472,
        "function": "emit",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 253,
        "function": "invokeMethod",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 321,
        "function": "start",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
        "line": 383,
        "function": "exec",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
        "line": 35,
        "function": "exec",
        "class": "OCA\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/remote.php",
        "line": 172,
        "args": [
          "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
        ],
        "function": "require_once"
      }
    ],
    "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php",
    "Line": 47,
    "Previous": {
      "Exception": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
      "Message": "SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"id\" of relation \"oc_comments\" violates not-null constraint\nDETAIL:  Failing row contains (null, 0, 0, 0, users, mammad, test text, comment, 2024-09-24 22:29:04, null, files, 36422, null, null, null, null).",
      "Code": 7,
      "Trace": [
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
          "line": 132,
          "function": "new",
          "class": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
          "type": "::"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
          "line": 1207,
          "function": "execute",
          "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
          "line": 292,
          "function": "executeStatement",
          "class": "Doctrine\\DBAL\\Connection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
          "line": 383,
          "function": "executeStatement",
          "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
          "line": 393,
          "function": "executeStatement",
          "class": "OC\\DB\\Connection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
          "line": 280,
          "function": "execute",
          "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
          "line": 1140,
          "function": "execute",
          "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
          "line": 1096,
          "function": "insert",
          "class": "OC\\Comments\\Manager",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
          "line": 244,
          "function": "save",
          "class": "OC\\Comments\\Manager",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
          "line": 121,
          "function": "createComment",
          "class": "OCA\\DAV\\Comments\\CommentsPlugin",
          "type": "->",
          "args": [
            "*** sensitive parameters replaced ***"
          ]
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
          "line": 89,
          "function": "httpPost",
          "class": "OCA\\DAV\\Comments\\CommentsPlugin",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 472,
          "function": "emit",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 253,
          "function": "invokeMethod",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 321,
          "function": "start",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
          "line": 383,
          "function": "exec",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
          "line": 35,
          "function": "exec",
          "class": "OCA\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/remote.php",
          "line": 172,
          "args": [
            "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
          ],
          "function": "require_once"
        }
      ],
      "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
      "Line": 28,
      "Previous": {
        "Exception": "PDOException",
        "Message": "SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"id\" of relation \"oc_comments\" violates not-null constraint\nDETAIL:  Failing row contains (null, 0, 0, 0, users, mammad, test text, comment, 2024-09-24 22:29:04, null, files, 36422, null, null, null, null).",
        "Code": "23502",
        "Trace": [
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
            "line": 130,
            "function": "execute",
            "class": "PDOStatement",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
            "line": 1207,
            "function": "execute",
            "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
            "line": 292,
            "function": "executeStatement",
            "class": "Doctrine\\DBAL\\Connection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
            "line": 383,
            "function": "executeStatement",
            "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
            "line": 393,
            "function": "executeStatement",
            "class": "OC\\DB\\Connection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
            "line": 280,
            "function": "execute",
            "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
            "line": 1140,
            "function": "execute",
            "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
            "line": 1096,
            "function": "insert",
            "class": "OC\\Comments\\Manager",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
            "line": 244,
            "function": "save",
            "class": "OC\\Comments\\Manager",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
            "line": 121,
            "function": "createComment",
            "class": "OCA\\DAV\\Comments\\CommentsPlugin",
            "type": "->",
            "args": [
              "*** sensitive parameters replaced ***"
            ]
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
            "line": 89,
            "function": "httpPost",
            "class": "OCA\\DAV\\Comments\\CommentsPlugin",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 472,
            "function": "emit",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 253,
            "function": "invokeMethod",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 321,
            "function": "start",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
            "line": 383,
            "function": "exec",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
            "line": 35,
            "function": "exec",
            "class": "OCA\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/remote.php",
            "line": 172,
            "args": [
              "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
            ],
            "function": "require_once"
          }
        ],
        "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
        "Line": 130
      }
    },
    "message": "An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"id\" of relation \"oc_comments\" violates not-null constraint\nDETAIL:  Failing row contains (null, 0, 0, 0, users, mammad, test text, comment, 2024-09-24 22:29:04, null, files, 36422, null, null, null, null).",
    "exception": [],
    "CustomMessage": "An exception occurred while executing a query: SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column \"id\" of relation \"oc_comments\" violates not-null constraint\nDETAIL:  Failing row contains (null, 0, 0, 0, users, mammad, test text, comment, 2024-09-24 22:29:04, null, files, 36422, null, null, null, null)."
  },
  "id": "66f33d3665390"
}
7

First, I installed a raw NextCloud on the new server
The previous database was mysql and the new database is pgsql
I converted the tables and manually entered the new database

Now all the parts are working well and there is only problem in the oc_comment table section and any part that needs to record in this table.

No specific error is displayed in this section

8 
{
    "system": {
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "localhost",
            "***REMOVED SENSITIVE VALUE***",
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "29.0.7.1",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": "0",
            "dbindex": "0",
            "timeout": "0.5",
            "password": "***REMOVED SENSITIVE VALUE***"
        },
        "memcache.local": "\\OC\\Memcache\\Redis",
        "filelocking.enabled": "true",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "upgrade.disable-web": false,
        "log_type": "file",
        "logfile": "\/var\/log\/nextcloud\/nextcloud.log",
        "loglevel": "2",
        "log.condition": {
            "apps": [
                "admin_audit"
            ]
        },
        "mail_smtpmode": "smtp",
        "remember_login_cookie_lifetime": "10800",
        "log_rotate_size": "10485760",
        "trashbin_retention_obligation": "auto, 60",
        "versions_retention_obligation": "auto, 180",
        "activity_expire_days": "120",
        "simpleSignUpLink.shown": false,
        "default_phone_region": "IR",
        "logtimezone": "Asia\/Tehran",
        "htaccess.RewriteBase": "\/",
        "session_lifetime": "10800",
        "session_keepalive": "false",
        "share_folder": "\/Shared",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "forwarded_for_headers": [
            "HTTP_X_FORWARDED_FOR"
        ],
        "app_install_overwrite": [
            "integration_whiteboard",
            "apporder",
            "discretepdfsigner",
            "files_external_gdrive",
            "ocdownloader",
            "transfer"
        ],
        "preview_concurrency_new": "4",
        "preview_concurrency_all": "8",
        "enabledPreviewProviders": [
            "OC\\Preview\\Imaginary",
            "OC\\Preview\\Image",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\MP3",
            "OC\\Preview\\TXT",
            "OC\\Preview\\OpenDocument",
            "OC\\Preview\\Movie",
            "OC\\Preview\\Krita",
            "OC\\Preview\\dwg",
            "OC\\Preview\\docx"
        ],
        "preview_imaginary_url": "***REMOVED SENSITIVE VALUE***",
        "preview_max_x": "2048",
        "preview_max_y": "2048",
        "jpeg_quality": "60",
        "preview_max_memory": "256",
        "maintenance": false,
        "has_rebuilt_cache": true,
        "mail_smtpsecure": "ssl",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": 1,
        "mail_smtpport": "465",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "theme": "",
        "onlyoffice": {
            "jwt_secret": "***REMOVED SENSITIVE VALUE***",
            "jwt_header": "AuthorizationJwt"
        },
        "allow_local_remote_servers": "true"
    }
}
9

Does the output of this SQL query:

\d oc_comments;

… look like this:

nextcloud=> \d oc_comments;
                                                Table "public.oc_comments"
         Column         |              Type              | Collation | Nullable |                 Default
------------------------+--------------------------------+-----------+----------+-----------------------------------------
 id                     | bigint                         |           | not null | nextval('oc_comments_id_seq'::regclass)
 parent_id              | bigint                         |           | not null | 0
 topmost_parent_id      | bigint                         |           | not null | 0
 children_count         | integer                        |           | not null | 0
 actor_type             | character varying(64)          |           | not null | ''::character varying
 actor_id               | character varying(64)          |           | not null | ''::character varying
 message                | text                           |           |          |
 verb                   | character varying(64)          |           |          | NULL::character varying
 creation_timestamp     | timestamp(0) without time zone |           |          | NULL::timestamp without time zone
 latest_child_timestamp | timestamp(0) without time zone |           |          | NULL::timestamp without time zone
 object_type            | character varying(64)          |           | not null | ''::character varying
 object_id              | character varying(64)          |           | not null | ''::character varying
 reference_id           | character varying(64)          |           |          | NULL::character varying
 reactions              | character varying(4000)        |           |          | NULL::character varying
 expire_date            | timestamp(0) without time zone |           |          | NULL::timestamp without time zone
 meta_data              | text                           |           |          | ''::text
Indexes:
    "oc_comments_pkey" PRIMARY KEY, btree (id)
    "comments_actor_index" btree (actor_type, actor_id)
    "comments_object_index" btree (object_type, object_id, creation_timestamp)
    "comments_parent_id_index" btree (parent_id)
    "comments_topmost_parent_id_idx" btree (topmost_parent_id)
    "expire_date" btree (expire_date)

ernolf

1 Like
10

The output of the SQL query command is:

nextcloud_db=# \d oc_comments
                              Table "public.oc_comments"
         Column         |            Type             | Collation | Nullable | Default
------------------------+-----------------------------+-----------+----------+---------
 id                     | numeric(20,0)               |           | not null |
 parent_id              | numeric(20,0)               |           | not null |
 topmost_parent_id      | numeric(20,0)               |           | not null |
 children_count         | bigint                      |           | not null |
 actor_type             | character varying(64)       |           | not null |
 actor_id               | character varying(64)       |           | not null |
 message                | text                        |           |          |
 verb                   | character varying(64)       |           |          |
 creation_timestamp     | timestamp without time zone |           |          |
 latest_child_timestamp | timestamp without time zone |           |          |
 object_type            | character varying(64)       |           | not null |
 object_id              | character varying(64)       |           | not null |
 reference_id           | character varying(64)       |           |          |
 reactions              | character varying(4000)     |           |          |
 expire_date            | timestamp without time zone |           |          |
 meta_data              | text                        |           |          |
Indexes:
    "oc_comments_pkey" PRIMARY KEY, btree (id)
    "comments_actor_index" btree (actor_type, actor_id)
    "comments_object_index" btree (object_type, object_id, creation_timestamp)
    "comments_parent_id_index" btree (parent_id)
    "comments_topmost_parent_id_idx" btree (topmost_parent_id)
    "expire_date" btree (expire_date)

I removed the comments and data that was already in this table during the migration so that it is raw
Didn’t this cause the numbering order of IDs to be messed up?!

11

You must fix that table, there are no defaults defined.

This should fix it:

BEGIN;

-- Change column `id` to bigint and set a sequence as the default
ALTER TABLE oc_comments
    ALTER COLUMN id TYPE bigint,
    ALTER COLUMN id SET DEFAULT nextval('oc_comments_id_seq'::regclass);

-- Change `parent_id` to bigint and set the default value to 0
ALTER TABLE oc_comments
    ALTER COLUMN parent_id TYPE bigint,
    ALTER COLUMN parent_id SET DEFAULT 0;

-- Change `topmost_parent_id` to bigint and set the default value to 0
ALTER TABLE oc_comments
    ALTER COLUMN topmost_parent_id TYPE bigint,
    ALTER COLUMN topmost_parent_id SET DEFAULT 0;

-- Change `children_count` to integer and set the default value to 0
ALTER TABLE oc_comments
    ALTER COLUMN children_count TYPE integer,
    ALTER COLUMN children_count SET DEFAULT 0;

-- Set default value for `actor_type` to an empty string
ALTER TABLE oc_comments
    ALTER COLUMN actor_type SET DEFAULT ''::character varying;

-- Set default value for `actor_id` to an empty string
ALTER TABLE oc_comments
    ALTER COLUMN actor_id SET DEFAULT ''::character varying;

-- Set default value for `verb` to NULL
ALTER TABLE oc_comments
    ALTER COLUMN verb SET DEFAULT NULL::character varying;

-- Change `creation_timestamp` and `latest_child_timestamp` to timestamp(0) without time zone and set the default to NULL
ALTER TABLE oc_comments
    ALTER COLUMN creation_timestamp TYPE timestamp(0) without time zone,
    ALTER COLUMN creation_timestamp SET DEFAULT NULL::timestamp without time zone,
    ALTER COLUMN latest_child_timestamp TYPE timestamp(0) without time zone,
    ALTER COLUMN latest_child_timestamp SET DEFAULT NULL::timestamp without time zone;

-- Set default value for `object_type` to an empty string
ALTER TABLE oc_comments
    ALTER COLUMN object_type SET DEFAULT ''::character varying;

-- Set default value for `object_id` to an empty string
ALTER TABLE oc_comments
    ALTER COLUMN object_id SET DEFAULT ''::character varying;

-- Set default value for `reference_id` to NULL
ALTER TABLE oc_comments
    ALTER COLUMN reference_id SET DEFAULT NULL::character varying;

-- Set default value for `reactions` to NULL
ALTER TABLE oc_comments
    ALTER COLUMN reactions SET DEFAULT NULL::character varying;

-- Set default value for `expire_date` to NULL
ALTER TABLE oc_comments
    ALTER COLUMN expire_date SET DEFAULT NULL::timestamp without time zone;

-- Set default value for `meta_data` to an empty text
ALTER TABLE oc_comments
    ALTER COLUMN meta_data SET DEFAULT ''::text;

COMMIT;

No guarantee, I did not test it.

Much and good luck,
ernolf

4 Likes
12

One additional point: This is only fixing a single table. You might want to look at all other tables as well…

3 Likes
13

Yes, apparently that was the problem
By changing the tables, the previous error is not repeated, but a new error appears in the log:

 DriverException An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for sequence oc_comments_id_seq

More complete report:

{
  "reqId": "RqjAjhv8P3fPKHDLJfzu",
  "level": 3,
  "time": "2024-09-25T17:40:21+03:30",
  "remoteAddr": "212.86.84.121",
  "user": "mammad",
  "app": "webdav",
  "method": "POST",
  "url": "/remote.php/dav/comments/files/43655",
  "message": "An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for sequence oc_comments_id_seq",
  "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:130.0) Gecko/20100101 Firefox/130.0",
  "version": "29.0.7.1",
  "exception": {
    "Exception": "Doctrine\\DBAL\\Exception\\DriverException",
    "Message": "An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for sequence oc_comments_id_seq",
    "Code": 7,
    "Trace": [
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1943,
        "function": "convert",
        "class": "Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1885,
        "function": "handleDriverException",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
        "line": 1213,
        "function": "convertExceptionDuringQuery",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
        "line": 292,
        "function": "executeStatement",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
        "line": 383,
        "function": "executeStatement",
        "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
        "line": 393,
        "function": "executeStatement",
        "class": "OC\\DB\\Connection",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
        "line": 280,
        "function": "execute",
        "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
        "line": 1140,
        "function": "execute",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
        "line": 1096,
        "function": "insert",
        "class": "OC\\Comments\\Manager",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
        "line": 244,
        "function": "save",
        "class": "OC\\Comments\\Manager",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
        "line": 121,
        "function": "createComment",
        "class": "OCA\\DAV\\Comments\\CommentsPlugin",
        "type": "->",
        "args": [
          "*** sensitive parameters replaced ***"
        ]
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
        "line": 89,
        "function": "httpPost",
        "class": "OCA\\DAV\\Comments\\CommentsPlugin",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 472,
        "function": "emit",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 253,
        "function": "invokeMethod",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
        "line": 321,
        "function": "start",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
        "line": 383,
        "function": "exec",
        "class": "Sabre\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
        "line": 35,
        "function": "exec",
        "class": "OCA\\DAV\\Server",
        "type": "->"
      },
      {
        "file": "/var/www/nextcloud/remote.php",
        "line": 172,
        "args": [
          "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
        ],
        "function": "require_once"
      }
    ],
    "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php",
    "Line": 87,
    "Previous": {
      "Exception": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
      "Message": "SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for sequence oc_comments_id_seq",
      "Code": 7,
      "Trace": [
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
          "line": 132,
          "function": "new",
          "class": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
          "type": "::"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
          "line": 1207,
          "function": "execute",
          "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
          "line": 292,
          "function": "executeStatement",
          "class": "Doctrine\\DBAL\\Connection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
          "line": 383,
          "function": "executeStatement",
          "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
          "line": 393,
          "function": "executeStatement",
          "class": "OC\\DB\\Connection",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
          "line": 280,
          "function": "execute",
          "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
          "line": 1140,
          "function": "execute",
          "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
          "line": 1096,
          "function": "insert",
          "class": "OC\\Comments\\Manager",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
          "line": 244,
          "function": "save",
          "class": "OC\\Comments\\Manager",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
          "line": 121,
          "function": "createComment",
          "class": "OCA\\DAV\\Comments\\CommentsPlugin",
          "type": "->",
          "args": [
            "*** sensitive parameters replaced ***"
          ]
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
          "line": 89,
          "function": "httpPost",
          "class": "OCA\\DAV\\Comments\\CommentsPlugin",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 472,
          "function": "emit",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 253,
          "function": "invokeMethod",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
          "line": 321,
          "function": "start",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
          "line": 383,
          "function": "exec",
          "class": "Sabre\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
          "line": 35,
          "function": "exec",
          "class": "OCA\\DAV\\Server",
          "type": "->"
        },
        {
          "file": "/var/www/nextcloud/remote.php",
          "line": 172,
          "args": [
            "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
          ],
          "function": "require_once"
        }
      ],
      "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
      "Line": 28,
      "Previous": {
        "Exception": "PDOException",
        "Message": "SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for sequence oc_comments_id_seq",
        "Code": "42501",
        "Trace": [
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
            "line": 130,
            "function": "execute",
            "class": "PDOStatement",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
            "line": 1207,
            "function": "execute",
            "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
            "line": 292,
            "function": "executeStatement",
            "class": "Doctrine\\DBAL\\Connection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
            "line": 383,
            "function": "executeStatement",
            "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
            "line": 393,
            "function": "executeStatement",
            "class": "OC\\DB\\Connection",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
            "line": 280,
            "function": "execute",
            "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
            "line": 1140,
            "function": "execute",
            "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/lib/private/Comments/Manager.php",
            "line": 1096,
            "function": "insert",
            "class": "OC\\Comments\\Manager",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
            "line": 244,
            "function": "save",
            "class": "OC\\Comments\\Manager",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/Comments/CommentsPlugin.php",
            "line": 121,
            "function": "createComment",
            "class": "OCA\\DAV\\Comments\\CommentsPlugin",
            "type": "->",
            "args": [
              "*** sensitive parameters replaced ***"
            ]
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
            "line": 89,
            "function": "httpPost",
            "class": "OCA\\DAV\\Comments\\CommentsPlugin",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 472,
            "function": "emit",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 253,
            "function": "invokeMethod",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 321,
            "function": "start",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/lib/Server.php",
            "line": 383,
            "function": "exec",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php",
            "line": 35,
            "function": "exec",
            "class": "OCA\\DAV\\Server",
            "type": "->"
          },
          {
            "file": "/var/www/nextcloud/remote.php",
            "line": 172,
            "args": [
              "/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"
            ],
            "function": "require_once"
          }
        ],
        "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
        "Line": 130
      }
    },
    "message": "An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for sequence oc_comments_id_seq",
    "exception": [],
    "CustomMessage": "An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for sequence oc_comments_id_seq"
  },
  "id": "66f419d386d43"
}
14

It was fixed
This error was solved by giving sequence access to the database user

Thanks to those who helped

1 Like
15

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