Updating calendar objects with long descriptions triggers exceptions SQLSTATE[22001]

[yan12125]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Steps to reproduce

1. Create a task with a very long description in https://some.nextcloud.domain/index.php/apps/tasks/
2. Update the description and add some characters

Expected behaviour

The task description is updated

Actual behaviour

500 internal server error

I inserted a logger statement in /usr/share/webapps/nextcloud/lib/private/DB/Connection.php, Connection::executeStatement(). The failing SQL statement is:

INSERT INTO "oc_calendarobjects_props" ("calendarid", "calendartype", "objectid", "name", "parameter", "value") VALUES(:dcValue1, :dcValue2, :dcValue3, :name, :parameter, :value)

Where parameters are

{
    "dcValue1":1,
    "dcValue2":0,
    "dcValue3":95470,
    "name":"DESCRIPTION",
    "parameter":null,
    "value":"(some very long text with more than 255 bytes)"
}

That seems quite similar to https://help.nextcloud.com/t/import-ics-db-error-concerning-column-truncation-how-to-report-that-issue/71910. I didn't find a bug report in this repository, hence the report.

Also, this may be related to my previous report #7876, which also failed in table oc_calendarobjects_props with SQLSTATE 22009. The older issue is no longer reproducible for me, though.

Server configuration

Operating system:
Arch Linux

Web server:
Apache 2.4.46

Database:
PostgreSQL 13.2

PHP version:
8.0.3

Nextcloud version: (see Nextcloud admin page)
21.0.0

Updated from an older Nextcloud/ownCloud or fresh install:
Updated from ownCloud several years ago

Where did you install Nextcloud from:
Arch Linux official package https://archlinux.org/packages/community/any/nextcloud/

Signing status:

Signing status

No errors have been found.

List of activated apps:

App list

Enabled:
  - activity: 2.14.3
  - admin_audit: 1.11.0
  - calendar: 2.1.3
  - cloud_federation_api: 1.4.0
  - contacts: 3.4.3
  - dav: 1.17.1
  - federatedfilesharing: 1.11.0
  - files: 1.16.0
  - logreader: 2.6.0
  - lookup_server_connector: 1.9.0
  - oauth2: 1.9.0
  - provisioning_api: 1.11.0
  - settings: 1.3.0
  - tasks: 0.13.6
  - twofactor_backupcodes: 1.10.0
  - viewer: 1.5.0
  - workflowengine: 2.3.0
Disabled:
  - accessibility
  - comments
  - contactsinteraction
  - dashboard
  - encryption
  - federation
  - files_external
  - files_pdfviewer
  - files_rightclick
  - files_sharing
  - files_trashbin
  - files_versions
  - files_videoplayer
  - firstrunwizard
  - nextcloud_announcements
  - notifications
  - password_policy
  - photos
  - privacy
  - recommendations
  - serverinfo
  - sharebymail
  - support
  - survey_client
  - systemtags
  - text
  - theming
  - updatenotification
  - user_ldap
  - user_status
  - weather_status

Nextcloud configuration:

Config report

{
    "system": {
        "version": "21.0.0.18",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "defaultapp": "calendar",
        "appstoreenabled": false,
        "log_type": "syslog",
        "config_is_read_only": true,
        "loglevel": 2,
        "maintenance": false,
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbuser": "***REMOVED SENSITIVE VALUE***"
    }
}

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

Are you using encryption: yes/no
no

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

LDAP configuration (delete this part if not used)

N/A

Client configuration

Browser:
Firefox 86.0.1

Operating system:
Windows 10 20H2 x64

Logs

Web server error log

Web server error log

(IP) - - [17/Mar/2021:20:50:11 +0800] "PUT /remote.php/dav/calendars/username/personal/xxx.ics HTTP/2.0" 500 353

Nextcloud log (data/nextcloud.log)

Nextcloud log

{"reqId":"3kbjhkkTmYuHFGr2hiZl","level":4,"time":"2021-03-17T12:41:11+00:00","remoteAddr":"192.168.236.3","user":"yen","app":"webdav","method":"PUT","url":"/remote.php/dav/calendars/yen/personal/4580674220197251593.ics","message":"{\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing a query: SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)\",\"Code\":7,\"Trace\":[{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1728,\"function\":\"convert\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\API\\\\PostgreSQL\\\\ExceptionConverter\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1667,\"function\":\"handleDriverException\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1146,\"function\":\"convertExceptionDuringQuery\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/Connection.php\",\"line\":257,\"function\":\"executeStatement\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php\",\"line\":213,\"function\":\"executeStatement\",\"class\":\"OC\\\\DB\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":286,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":2663,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":1215,\"function\":\"updateProperties\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/CalDAV/CalendarObject.php\",\"line\":103,\"function\":\"updateCalendarObject\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1137,\"function\":\"put\",\"class\":\"Sabre\\\\CalDAV\\\\CalendarObject\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":492,\"function\":\"updateFile\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPut\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/Server.php\",\"line\":332,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":35,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/remote.php\",\"line\":167,\"args\":[\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php\",\"Line\":83,\"Previous\":{\"Exception\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Exception\",\"Message\":\"SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)\",\"Code\":7,\"Trace\":[{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php\",\"line\":84,\"function\":\"new\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Exception\",\"type\":\"::\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1136,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Statement\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/Connection.php\",\"line\":257,\"function\":\"executeStatement\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php\",\"line\":213,\"function\":\"executeStatement\",\"class\":\"OC\\\\DB\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":286,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":2663,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":1215,\"function\":\"updateProperties\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/CalDAV/CalendarObject.php\",\"line\":103,\"function\":\"updateCalendarObject\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1137,\"function\":\"put\",\"class\":\"Sabre\\\\CalDAV\\\\CalendarObject\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":492,\"function\":\"updateFile\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPut\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/Server.php\",\"line\":332,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":35,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/remote.php\",\"line\":167,\"args\":[\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php\",\"Line\":26,\"Previous\":{\"Exception\":\"PDOException\",\"Message\":\"SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)\",\"Code\":\"22001\",\"Trace\":[{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php\",\"line\":82,\"function\":\"execute\",\"class\":\"PDOStatement\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1136,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Statement\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/Connection.php\",\"line\":257,\"function\":\"executeStatement\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php\",\"line\":213,\"function\":\"executeStatement\",\"class\":\"OC\\\\DB\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":286,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":2663,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":1215,\"function\":\"updateProperties\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/CalDAV/CalendarObject.php\",\"line\":103,\"function\":\"updateCalendarObject\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1137,\"function\":\"put\",\"class\":\"Sabre\\\\CalDAV\\\\CalendarObject\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":492,\"function\":\"updateFile\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPut\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/Server.php\",\"line\":332,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":35,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/remote.php\",\"line\":167,\"args\":[\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php\",\"Line\":82}},\"CustomMessage\":\"--\"}","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0","version":"21.0.0.18"}

Browser log

Browser log

PUT https://my.nextcloud.domain/remote.php/dav/calendars/username/personal/xxx.ics
[HTTP/2 500 Internal Server Error 127ms]

[yan12125]

The issue seems to be that Nextcloud picks first 254 characters for the calendar property value (via mb_substr() [1]), while the PostregreSQL database wants data not longer than 255 bytes. The difference causes issues with long descriptions with multi-byte characters (Chinese in my case). Replacing all mb_substr() calls with mb_strcut() in the file linked above fixes the issue for me.

[1] https://github.com/nextcloud/server/blob/v21.0.0/apps/dav/lib/CalDAV/CalDavBackend.php#L2658