Adding calendar or reminder via CalDAV on macOS results in SQL error

[jsalort]

Summary

I have experienced the same issue as reported on the nextcloud github:
nextcloud/server#4364

It is closed there (with a fix, which presumably could be used in owncloud as well).
However, I am using ownCloud (and it reports up to date), and I do experience the same problem.

Additionnally, I noticed that the same bug affects both the macOS Calendar app and the macOS Reminders app.

Steps to reproduce

1. Add CalDAV account to macOS Calendar or Reminders app.
2. Add a calendar, or add the reminder list

Expected behaviour

A new calendar / reminder list should appear on the server

Actual behaviour

macOS Reminders says nothing; macOS Calendar reports HTTP error 500 on operation "CalDAVAddCalendarQueueableOperation".

Server configuration

Operating system: macOS 10.13.5

Web server:
Ubuntu 16.04.4 LTS
Apache2 2.4.18

Database:
mysql2 5.7.22

PHP version:
php 7.0.30

ownCloud version: (see ownCloud admin page)
owncloud 10.0.8.5

Updated from an older ownCloud or fresh install:
Updated from ownCloud 9

Signing status (ownCloud 9.0 and above):

https://gist.github.com/jsalort/a28a6c53cf7102c9400db18740cb8867

List of activated apps:

Enabled:

• activity: 2.3.6
• calendar: 1.5.7
• comments: 0.3.0
• configreport: 0.1.1
• contacts: 1.5.4
• dav: 0.3.2
• federatedfilesharing: 0.3.1
• federation: 0.1.0
• files: 1.5.1
• files_external: 0.7.1
• files_pdfviewer: 0.8.2
• files_sharing: 0.10.1
• files_texteditor: 2.2.1
• files_trashbin: 0.9.1
• files_versions: 1.3.0
• files_videoplayer: 0.9.8
• firstrunwizard: 1.1
• gallery: 16.0.2
• market: 0.2.4
• notes: 2.0.3
• notifications: 0.3.3
• polls: 0.8.1
• provisioning_api: 0.5.0
• systemtags: 0.3.0
• tasks: 0.9.6
• templateeditor: 0.3
• updatenotification: 0.2.1
  Disabled:
• encryption
• external
• theme-example
• user_external

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

No

Are you using encryption: yes/no

No

Logs

Web server error log

xx.xx.xx.xx - jsalort [07/Jul/2018:14:53:06 +0200] "MKCALENDAR /remote.php/dav/calendars/jsalort/13FE848C-7F3D-4011-95BF-CC6C17CF8C4E/ HTTP/1.1" 500 2412 "-" "Mac+OS+X/10.13.5 (17F77) CalendarAgent/399.2.2"

ownCloud log (data/owncloud.log)

{"reqId":"RVtl7DMgOSrfBYfTLIZJ","level":4,"time":"2018-07-07T11:46:14+00:00","remoteAddr":"xx.xx.xx.xx","user":"jsalort","app":"webdav","method":"MKCALENDAR","url":"/remote.php/dav/calendars/jsalort/13FE848C-7F3D-4011-95BF-CC6C17CF8C4E/","message":"Exception: {"Message":"An exception occurred while executing 'INSERT INTO oc_calendars (principaluri, uri, synctoken, transparent, components, displayname, timezone, calendarorder, calendarcolor) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [\"principals\\\/users\\\/jsalort\", \"13FE848C-7F3D-4011-95BF-CC6C17CF8C4E\", 1, false, \"VTODO\", \"Nouvelle liste\", \"BEGIN:VCALENDAR\\r\\nVERSION:2.0\\r\\nPRODID:-\\\/\\\/Apple Inc.\\\/\\\/Mac OS X 10.13.5\\\/\\\/EN\\r\\nCALSCALE:GREGORIAN\\r\\nBEGIN:VTIMEZONE\\r\\nTZID:Europe\\\/Paris\\r\\nBEGIN:DAYLIGHT\\r\\nTZOFFSETFROM:+0100\\r\\nRRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=-1SU\\r\\nDTSTART:19810329T020000\\r\\nTZNAME:UTC+2\\r\\nTZOFFSETTO:+0200\\r\\nEND:DAYLIGHT\\r\\nBEGIN:STANDARD\\r\\nTZOFFSETFROM:+0200\\r\\nRRULE:FREQ=YEARLY;BYMONTH=10;BYDAY=-1SU\\r\\nDTSTART:19961027T030000\\r\\nTZNAME:UTC+1\\r\\nTZOFFSETTO:+0100\\r\\nEND:STANDARD\\r\\nEND:VTIMEZONE\\r\\nEND:VCALENDAR\\r\\n\", \"1073741912\", \"#711A76FF\"]:\n\nSQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'transparent' at row 1","Exception":"Doctrine\\DBAL\\Exception\\DriverException","Code":0,"Trace":"#0 \/home\/jsalort\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php(128): Doctrine\\DBAL\\Driver\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\DBAL\\Driver\\PDOException))\n#1 \/home\/jsalort\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php(1015): Doctrine\\DBAL\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Object(Doctrine\\DBAL\\Driver\\PDOException), 'INSERT INTO oc...', Array)\\n#2 \\\/home\\\/jsalort\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(210): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO oc...', Array, Array)\n#3 \/home\/jsalort\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Query\/QueryBuilder.php(208): OC\\DB\\Connection->executeUpdate('INSERT INTO `oc...', Array, Array)\n#4 \/home\/jsalort\/owncloud\/lib\/private\/DB\/QueryBuilder\/QueryBuilder.php(141): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 \/home\/jsalort\/owncloud\/apps\/dav\/lib\/CalDAV\/CalDavBackend.php(577): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 \/home\/jsalort\/owncloud\/lib\/composer\/sabre\/dav\/lib\/CalDAV\/CalendarHome.php(265): OCA\\DAV\\CalDAV\\CalDavBackend->createCalendar('principals\/user...', '13FE848C-7F3D-4...', Array)\n#7 \/home\/jsalort\/owncloud\/lib\/composer\/sabre\/dav\/lib\/DAV\/Server.php(1196): Sabre\\CalDAV\\CalendarHome->createExtendedCollection('13FE848C-7F3D-4...', Object(Sabre\\DAV\\MkCol))\n#8 \/home\/jsalort\/owncloud\/lib\/composer\/sabre\/dav\/lib\/CalDAV\/Plugin.php(303): Sabre\\DAV\\Server->createCollection('calendars\/jsalo...', Object(Sabre\\DAV\\MkCol))\n#9 [internal function]: Sabre\\CalDAV\\Plugin->httpMkCalendar(Object(Sabre\\HTTP\\Request), Object(Sabre\\HTTP\\Response))\n#10 \/home\/jsalort\/owncloud\/lib\/composer\/sabre\/event\/lib\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\n#11 \/home\/jsalort\/owncloud\/lib\/composer\/sabre\/dav\/lib\/DAV\/Server.php(479): Sabre\\Event\\EventEmitter->emit('method:MKCALEND...', Array)\n#12 \/home\/jsalort\/owncloud\/lib\/composer\/sabre\/dav\/lib\/DAV\/Server.php(254): Sabre\\DAV\\Server->invokeMethod(Object(Sabre\\HTTP\\Request), Object(Sabre\\HTTP\\Response))\n#13 \/home\/jsalort\/owncloud\/apps\/dav\/lib\/Server.php(278): Sabre\\DAV\\Server->exec()\n#14 \/home\/jsalort\/owncloud\/apps\/dav\/appinfo\/v2\/remote.php(31): OCA\\DAV\\Server->exec()\n#15 \/home\/jsalort\/owncloud\/remote.php(165): require_once('\/home\/jsalort\/o...')\n#16 {main}","File":"\/home\/jsalort\/owncloud\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/AbstractMySQLDriver.php","Line":115,"User":"jsalort"}"}

[ownclouders]

GitMate.io thinks the contributors most likely able to help are @DeepDiver1975, and @ownclouders.

Possibly related issues are #28840 (-Error-), #26902 (error), #26667 (Shared calendars not editable via caldav), #3276 (Can owncloud do calendar reminders? ), and #484 ([FEATURE] Add support to set reminders for new events in Calendar).

[DeepDiver1975]

10.0.10 will hold a fix for it

[mjollnir]

Hi I upgraded to 10.0.10-1+1.1 (Debian) hoping this would be solved and unfortunately it's not. But I actually just realised I have a slightly different issue, the calendar I've added is a remote subscription. So the action that's causing the error for me is

CalDAVAddSubscriptionCalendarQueueableOperation
not
CalDAVAddCalendarQueueableOperation

Is this another manifestation of the same issue? Thanks!

[DeepDiver1975]

Hi I upgraded to 10.0.10-1+1.1 (Debian) hoping this would be solved and unfortunately it's not. But I actually just realised I have a slightly different issue, the calendar I've added is a remote subscription. So the action that's causing the error for me is

CalDAVAddSubscriptionCalendarQueueableOperation
not
CalDAVAddCalendarQueueableOperation

Is this another manifestation of the same issue? Thanks!

please open a new issue with as many details as possible - please follow the issue template. THX