PostgreSQL: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "oc_schedulingobjects"

Steps to reproduce

create an appointment and save it → everthing is fine
edit appointment and add an attendee or create an new appointment with attendee
saving the edited appointment results in Doctrine exception
no error in the front end, the appointment is left untouched

This isue only occures with PostgreSQL. After converting the database type to mySQL the problem is gone.

Expected behaviour

It should be possible to create an appointment and invite an attendee.

Actual behaviour

It is not possible to invite an attendee.

Server configuration

Operating system: Arch Linux

Web server: Apache 2.4.29

Database: PostgreSQL 10.1

PHP version: 7.2.3

Nextcloud version: 13.0.0.14

Updated from an older Nextcloud/ownCloud or fresh install: No, fresh install.

Where did you install Nextcloud from: Arch repository.

Signing status:

Signing status

No errors have been found.

List of activated apps:

App list

Enabled:

activity: 2.6.1
admin_notifications: 1.0.1
bookmarks: 0.11.0
bruteforcesettings: 1.0.3
calendar: 1.6.1
comments: 1.3.0
contacts: 2.1.2
dav: 1.4.6
deck: 0.3.1
federatedfilesharing: 1.3.1
federation: 1.3.0
files: 1.8.0
files_downloadactivity: 1.2.0
files_pdfviewer: 1.2.0
files_sharing: 1.5.0
files_texteditor: 2.5.1
files_trashbin: 1.3.0
files_versions: 1.6.0
files_videoplayer: 1.2.0
firstrunwizard: 2.2.1
gallery: 18.0.0
logreader: 2.0.0
lookup_server_connector: 1.1.0
mail: 0.7.10
nextcloud_announcements: 1.2.0
notes: 2.3.2
notifications: 2.1.2
oauth2: 1.1.0
password_policy: 1.3.0
polls: 0.8.1
provisioning_api: 1.3.0
serverinfo: 1.3.0
sharebymail: 1.3.0
survey_client: 1.1.0
systemtags: 1.3.0
tasks: 0.9.6
theming: 1.4.1
twofactor_backupcodes: 1.2.3
updatenotification: 1.3.0
workflowengine: 1.3.0
Disabled:
admin_audit
encryption
files_accesscontrol
files_external
richdocuments
user_external
user_ldap

Nextcloud configuration:

Config report

{ "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "***REMOVED SENSITIVE VALUE***" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***", "dbtype": "pgsql", "version": "13.0.0.14", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "", "dbtableprefix": "oc_", "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "data-fingerprint": "eaa11f1bd391bfc2dc9512be92b22157", "maintenance": false } }

Are you using external storage, if yes which one: no

Are you using encryption: no

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

Client configuration

Browser: Firefox, Chrome, Thunderbird / Lightning …

Operating system: Arch Linux

Logs

Nextcloud log (data/nextcloud.log)

Nextcloud log

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "oc_schedulingobjects" ("principaluri", "calendardata", "uri", "lastmodified", "etag", "size") VALUES(?, ?, ?, ?, ?, ?)' with params ["principals/users/[REMOVED]", "BEGIN:VCALENDAR\r\nVERSION:2.0\r\nPRODID:-//Sabre//Sabre VObject 4.1.2//EN\r\nCALSCALE:GREGORIAN\r\nMETHOD:REQUEST\r\nBEGIN:VTIMEZONE\r\n[…DELETED…]\r\nSEQUENCE:1\r\nX-MOZ-GENERATION:3\r\nEND:VEVENT\r\nEND:VCALENDAR\r\n", "sabredav-6f8e8738-04a2-42cc-a8d5-4ec0114cd746.ics", 1521033701, "751f620dbe364fbf1c40b03b3422ca93", 1021]: SQLSTATE[22P02]: Invalid text representation: 7 FEHLER: ungültige Eingabesyntax für Typ bytea

@icewind1991

@icewind1991 @nickvergessen

@enoch85

@enoch85 can you confirm this?

Using the Calendar app I can confirm that it works as expected. No error when following the steps to reproduce in this ticket.

@enoch85

@enoch85 which pgsql version do you run?

@blizzz

@blizzz

PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Ubuntu 9.6.8-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit

→ might be an incompatibility with 10

I just got this error and fixed it by removing all non ascii from the location of the event.
Barnhälsovården
I am using 16.04.4 LTS with postgresql 9.5+173ubuntu0.1

nextcloud.log gives:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bytea","Code":0,"Trace":"#0 \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php(128): Doctrine\\DBAL\\Driver\\AbstractPostgreSQLDriver->convertException('An exception oc...
snip
LOCATION:Norrlands universitetssjukhus Barnh\\u00e4lsov\\u00e5rden\\\\,
snip

Actually this is a bug in doctrine dbal not handling the bytea type for postgres properly. It either needs a hex representation or their escaping of every non printable character. (See https://www.postgresql.org/docs/current/static/datatype-binary.html)
So the \\u00e for example violates their escape format.

@nickvergessen

Funny enough. There was a similar bug some time ago #4071. But the fix was not for the root cause, I believe. @nickvergessen

@go2sh

Where did my comment go...
The thing is we don't (want to) use bytea. So where ever this happens, a patch similar to the one linked by @go2sh needs to be applied. but if the type in the db is actually bytea we have a bigger problem. Can you check this?

In my postgres DB 'calendardata' in 'oc_schedulingobjects' is bytea, yes.

It is the default type in dbal for blob with psql. It comes from the migration 'Version1004Date20170825134824.php'.

nextcloud=# select column_name, data_type from information_schema.columns where table_name = 'oc_schedulingobjects';
 column_name  |     data_type
--------------+-------------------
 id           | bigint
 principaluri | character varying
 calendardata | bytea
 uri          | character varying
 lastmodified | integer
 etag         | character varying
 size         | bigint
(7 rows)

I also witness this from time to time. It happens with some calendar objects and not others, I suspect due to some particular text used. I have not identified a pattern.

I have observed that this problem seems to only happen if I invite an e-mail address that happens to be associated with a Nextcloud account on my server. In my case, the other accounts on my Nextcloud calendar do not use their calendars (not sure that is relevant, but thought I'd mention just in case).

I can confirm that this Problem still exists on the latest stable nextcloud ("installed" via the official docker container):

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "schedulingobjects" ("principaluri", "calendardata", "uri", "lastmodified", "etag", "size") VALUES(?, ?, ?, ?, ?, ?)' with params ["principals\/users\/TEST", "BEGIN:VCALENDAR\r\nVERSION:2.0\r\nPRODID:-\/\/Sabre\/\/Sabre VObject 4.1.2\/\/EN\r\nCALSCALE:GREGORIAN\r\nMETHOD:REQUEST\r\nBEGIN:VTIMEZONE\r\nTZID:Europe\/Madrid\r\nTZURL:http:\/\/tzurl.org\/zoneinfo\/Europe\/Madrid\r\nX-LIC-LOCATION:Europe\/Madrid\r\nBEGIN:DAYLIGHT\r\nTZOFFSETFROM:+0100\r\nTZOFFSETTO:+0200\r\nTZNAME:CEST\r\nDTSTART:19810329T020000\r\nRRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=-1SU\r\nEND:DAYLIGHT\r\nBEGIN:DAYLIGHT\r\nTZOFFSETFROM:+0000\r\nTZOFFSETTO:+0100\r\nTZNAME:WEST\r\nDTSTART:19180416T000000\r\nRDATE:19180416T000000\r\nRDATE:19190407T000000\r\nRDATE:19240417T000000\r\nRDATE:19260418T000000\r\nRDATE:19270410T000000\r\nRDATE:19280415T010000\r\nRDATE:19290421T000000\r\nRDATE:19370617T000000\r\nRDATE:19380403T000000\r\nEND:DAYLIGHT\r\nBEGIN:DAYLIGHT\r\nTZOFFSETFROM:+0100\r\nTZOFFSETTO:+0200\r\nTZNAME:WEMT\r\nDTSTART:19380430T230000\r\nRDATE:19380430T230000\r\nEND:DAYLIGHT\r\nBEGIN:DAYLIGHT\r\nTZOFFSETFROM:+0200\r\nTZOFFSETTO:+0100\r\nTZNAME:WEST\r\nDTSTART:19381003T005959\r\nRDATE:19381003T005959\r\nEND:DAYLIGHT\r\nBEGIN:DAYLIGHT\r\nTZOFFSETFROM:+0100\r\nTZOFFSETTO:+0200\r\nTZNAME:CEST\r\nDTSTART:19420502T230000\r\nRDATE:19420502T230000\r\nRDATE:19430417T230000\r\nRDATE:19440415T230000\r\nRDATE:19450414T230000\r\nRDATE:19460413T230000\r\nRDATE:19490430T230000\r\nRDATE:19740413T230000\r\nRDATE:19750412T230000\r\nRDATE:19760327T230000\r\nRDATE:19770402T230000\r\nRDATE:19780402T020000\r\nRDATE:19790401T020000\r\nRDATE:19800406T020000\r\nEND:DAYLIGHT\r\nBEGIN:STANDARD\r\nTZOFFSETFROM:+0200\r\nTZOFFSETTO:+0100\r\nTZNAME:CET\r\nDTSTART:19961027T030000\r\nRRULE:FREQ=YEARLY;BYMONTH=10;BYDAY=-1SU\r\nEND:STANDARD\r\nBEGIN:STANDARD\r\nTZOFFSETFROM:-001444\r\nTZOFFSETTO:+0000\r\nTZNAME:WET\r\nDTSTART:19010101T004516\r\nRDATE:19010101T004516\r\nEND:STANDARD\r\nBEGIN:STANDARD\r\nTZOFFSETFROM:+0100\r\nTZOFFSETTO:+0000\r\nTZNAME:WET\r\nDTSTART:19181007T015959\r\nRDATE:19181007T015959\r\nRDATE:19191007T015959\r\nRDATE:19241005T015959\r\nRDATE:19261003T015959\r\nRDATE:19271002T015959\r\nRDATE:19281007T015959\r\nRDATE:19291006T015959\r\nRDATE:19371003T015959\r\nRDATE:19391008T015959\r\nEND:STANDARD\r\nBEGIN:STANDARD\r\nTZOFFSETFROM:+0000\r\nTZOFFSETTO:+0100\r\nTZNAME:CET\r\nDTSTART:19400317T000000\r\nRDATE:19400317T000000\r\nEND:STANDARD\r\nBEGIN:STANDARD\r\nTZOFFSETFROM:+0200\r\nTZOFFSETTO:+0100\r\nTZNAME:CET\r\nDTSTART:19420901T010000\r\nRDATE:19420901T010000\r\nRDATE:19431003T010000\r\nRDATE:19441001T010000\r\nRDATE:19450930T010000\r\nRDATE:19460929T010000\r\nRDATE:19491002T010000\r\nRDATE:19741006T010000\r\nRDATE:19751005T010000\r\nRDATE:19760926T010000\r\nRDATE:19770925T010000\r\nRDATE:19781001T030000\r\nRDATE:19790930T030000\r\nRDATE:19800928T030000\r\nRDATE:19810927T030000\r\nRDATE:19820926T030000\r\nRDATE:19830925T030000\r\nRDATE:19840930T030000\r\nRDATE:19850929T030000\r\nRDATE:19860928T030000\r\nRDATE:19870927T030000\r\nRDATE:19880925T030000\r\nRDATE:19890924T030000\r\nRDATE:19900930T030000\r\nRDATE:19910929T030000\r\nRDATE:19920927T030000\r\nRDATE:19930926T030000\r\nRDATE:19940925T030000\r\nRDATE:19950924T030000\r\nEND:STANDARD\r\nBEGIN:STANDARD\r\nTZOFFSETFROM:+0100\r\nTZOFFSETTO:+0100\r\nTZNAME:CET\r\nDTSTART:19790101T000000\r\nRDATE:19790101T000000\r\nEND:STANDARD\r\nEND:VTIMEZONE\r\nBEGIN:VEVENT\r\nDTSTAMP:20180803T095012Z\r\nUID:027aff51-dace-4897-a3c1-0b1209d192be\r\nSUMMARY:Birthday fun\r\nLOCATION:Bxxxxx Stra\u00dfe 00b \\nBxxxxx \\nxxxxxxx\r\nDTSTART;TZID=Europe\/Madrid:20180818T180000\r\nDTEND;TZID=Europe\/Madrid:20180819T040000\r\nSTATUS:CONFIRMED\r\nORGANIZER:mailto:[email protected]\r\nATTENDEE;CN=Nnnnn Mmmmm;CUTYPE=INDIVIDUAL;ROLE=OPT-PARTICIPANT;RSVP=TRUE;PA\r\n RTSTAT=NEEDS-ACTION:mailto:[email protected]\r\nATTENDEE;CN=Xxx Yyyyyyyy;CUTYPE=INDIVIDUAL;ROLE=OPT-PARTICIPANT;RSVP=TRUE;P\r\n ARTSTAT=NEEDS-ACTION:mailto:[email protected]\r\nEND:VEVENT\r\nEND:VCALENDAR\r\n", "sabredav-4758a602-be96-4ad4-8b3e-b5671560368a.ics", 1533289807, "f80e76461249a4a767627f3fa1c02c37", 3518]: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bytea

Its especially bad as the error is only visible in the log. The event itself gets silently discarded by the server. Davdroid shows no error at all.

It seems that there might a IQueryBuilder::PARAM_LOB missing at

server/apps/dav/lib/CalDAV/CalDavBackend.php

Line 1989 in 89b6ee1

'calendardata' => $query->createNamedParameter($objectData),

See https://github.com/owncloud/core/pull/27914/files#diff-ded00a57c79a474ac4c4b43c620d6050R1483 where this bug got fixed in owncloud.

Can you prepare a PR with that?

Sadly not, Sorry. I've no testing environment here to test the changes.

Here is one without having tried the unittests: #10523

The change applied to my local instance fixes the problem though.

This was fixed in #10523

Navigation Menu

Repository navigation

PostgreSQL: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "oc_schedulingobjects" #8825

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

Client configuration

Logs

Nextcloud log (data/nextcloud.log)

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

PostgreSQL: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "oc_schedulingobjects" #8825

Description

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

Client configuration

Logs

Nextcloud log (data/nextcloud.log)

Activity

blizzz commented on Mar 19, 2018

nickvergessen commented on Mar 19, 2018

enoch85 commented on Mar 19, 2018

blizzz commented on Mar 19, 2018

enoch85 commented on Mar 19, 2018

blizzz commented on Mar 19, 2018

mikaelfrykholm commented on Mar 26, 2018

mikaelfrykholm commented on Mar 26, 2018

go2sh commented on Mar 27, 2018

go2sh commented on Mar 27, 2018

nickvergessen commented on Mar 27, 2018

go2sh commented on Mar 27, 2018

go2sh commented on Mar 27, 2018

bowlofeggs commented on Jun 18, 2018

bowlofeggs commented on Jun 18, 2018

janLo commented on Aug 3, 2018

janLo commented on Aug 3, 2018

nickvergessen commented on Aug 3, 2018

janLo commented on Aug 3, 2018

janLo commented on Aug 3, 2018

georgehrke commented on Sep 14, 2019

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions