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

tschlotfeldt · 2018-03-14T16:21:52Z

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

blizzz · 2018-03-19T12:16:29Z

@icewind1991 @nickvergessen

nickvergessen · 2018-03-19T13:49:00Z

@enoch85 can you confirm this?

enoch85 · 2018-03-19T15:16:12Z

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

blizzz · 2018-03-19T16:25:34Z

@enoch85 which pgsql version do you run?

enoch85 · 2018-03-19T18:36:29Z

@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

blizzz · 2018-03-19T22:09:36Z

→ might be an incompatibility with 10

mikaelfrykholm · 2018-03-26T10:57:03Z

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

mikaelfrykholm · 2018-03-26T11:00:22Z

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

go2sh · 2018-03-27T07:55:14Z

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.

go2sh · 2018-03-27T08:11:36Z

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

nickvergessen · 2018-03-27T08:53:46Z

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?

go2sh · 2018-03-27T09:06:03Z

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'.

go2sh · 2018-03-27T09:08:33Z

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)

bowlofeggs · 2018-06-18T03:10:02Z

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.

bowlofeggs · 2018-06-18T15:24:35Z

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).

janLo · 2018-08-03T10:02:13Z

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:xxx@yyy.de\r\nATTENDEE;CN=Nnnnn Mmmmm;CUTYPE=INDIVIDUAL;ROLE=OPT-PARTICIPANT;RSVP=TRUE;PA\r\n RTSTAT=NEEDS-ACTION:mailto:nnn@xxx.de\r\nATTENDEE;CN=Xxx Yyyyyyyy;CUTYPE=INDIVIDUAL;ROLE=OPT-PARTICIPANT;RSVP=TRUE;P\r\n ARTSTAT=NEEDS-ACTION:mailto:xxx@yyy.de\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.

janLo · 2018-08-03T10:49:04Z

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.

nickvergessen · 2018-08-03T10:55:42Z

Can you prepare a PR with that?

janLo · 2018-08-03T11:22:42Z

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

janLo · 2018-08-03T11:31:19Z

Here is one without having tried the unittests: #10523

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

georgehrke · 2019-09-14T14:28:28Z

This was fixed in #10523

tschlotfeldt changed the title ~~Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "oc_schedulingobjects"~~ PostgreSQL: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "oc_schedulingobjects" Mar 17, 2018

tschlotfeldt closed this as completed Mar 17, 2018

tschlotfeldt reopened this Mar 17, 2018

blizzz added bug feature: dav labels Mar 19, 2018

go2sh mentioned this issue Mar 27, 2018

Issue with QueryBuilder and Postgres 10.X #8953

Closed

nextcloud-bot added the stale Ticket or PR with no recent activity label Jul 19, 2018

nextcloud-bot removed the stale Ticket or PR with no recent activity label Aug 3, 2018

janLo mentioned this issue Aug 3, 2018

Adding test for table schedulingobjects and fixing postgres LOB #10523

Merged

nextcloud-bot mentioned this issue Nov 2, 2018

Exception: Doctrine\\DBAL\\Exception\\DriverException | cannot start web service #12228

Closed

nextcloud-bot mentioned this issue Nov 7, 2018

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO oc_filecache #12343

Closed

nextcloud-bot mentioned this issue Nov 15, 2018

insert operations under some circumstances refused on postgreSQL (NC14) #12465

Closed

skjnldsv added the 0. Needs triage Pending check for reproducibility or if it fits our roadmap label Jun 12, 2019

georgehrke closed this as completed Sep 14, 2019

georgehrke mentioned this issue Sep 14, 2019

Bug: Synchronisation error, new appointment not pushed #9093

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

tschlotfeldt commented Mar 14, 2018 •

edited

Loading

blizzz commented Mar 19, 2018

nickvergessen commented Mar 19, 2018

enoch85 commented Mar 19, 2018

blizzz commented Mar 19, 2018

enoch85 commented Mar 19, 2018 •

edited

Loading

blizzz commented Mar 19, 2018

mikaelfrykholm commented Mar 26, 2018

mikaelfrykholm commented Mar 26, 2018

go2sh commented Mar 27, 2018 •

edited

Loading

go2sh commented Mar 27, 2018

nickvergessen commented Mar 27, 2018

go2sh commented Mar 27, 2018 •

edited

Loading

go2sh commented Mar 27, 2018

bowlofeggs commented Jun 18, 2018

bowlofeggs commented Jun 18, 2018

janLo commented Aug 3, 2018

janLo commented Aug 3, 2018

nickvergessen commented Aug 3, 2018

janLo commented Aug 3, 2018

janLo commented Aug 3, 2018

georgehrke commented Sep 14, 2019

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

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

Comments

tschlotfeldt commented Mar 14, 2018 • edited Loading

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

Client configuration

Logs

Nextcloud log (data/nextcloud.log)

blizzz commented Mar 19, 2018

nickvergessen commented Mar 19, 2018

enoch85 commented Mar 19, 2018

blizzz commented Mar 19, 2018

enoch85 commented Mar 19, 2018 • edited Loading

blizzz commented Mar 19, 2018

mikaelfrykholm commented Mar 26, 2018

mikaelfrykholm commented Mar 26, 2018

go2sh commented Mar 27, 2018 • edited Loading

go2sh commented Mar 27, 2018

nickvergessen commented Mar 27, 2018

go2sh commented Mar 27, 2018 • edited Loading

go2sh commented Mar 27, 2018

bowlofeggs commented Jun 18, 2018

bowlofeggs commented Jun 18, 2018

janLo commented Aug 3, 2018

janLo commented Aug 3, 2018

nickvergessen commented Aug 3, 2018

janLo commented Aug 3, 2018

janLo commented Aug 3, 2018

georgehrke commented Sep 14, 2019

tschlotfeldt commented Mar 14, 2018 •

edited

Loading

enoch85 commented Mar 19, 2018 •

edited

Loading

go2sh commented Mar 27, 2018 •

edited

Loading

go2sh commented Mar 27, 2018 •

edited

Loading