Calendar doesn't support dates before Unix epoch => can't import contact birthdays #23004

Closed
twistedfall opened this Issue Mar 9, 2016 · 19 comments

Projects

None yet

7 participants

@twistedfall

You can't create event in the calendar with the start/end date that predates Unix epoch because of the column type for oc_calendarobjects.firstoccurence and calendarobjects.lastoccurence being INT UNSIGNED. This problem is especially visible when system tries to create birthday calendar entries for "older" (born before 1970) contacts.

Modifying the type of those columns to just BIGINT resolves the problem. Using BIGINT helps with another problem of how iOS stores birthdates without a year, it assigns them year 1604 resulting in a very long timestamp like -11540880000

@LukasReschke LukasReschke added this to the 9.0.1-current-maintenance milestone Mar 9, 2016
@DeepDiver1975 DeepDiver1975 self-assigned this Mar 9, 2016
@DeepDiver1975
Member

@twistedfall THX - can I ask you for an example ics file which holds data as described? THX

@DeepDiver1975
Member

THX

@PVince81
Collaborator
PVince81 commented Mar 9, 2016

Is this a regression ?

@DeepDiver1975
Member

I never tried with the old calendar app - unsure ..

But worth a notification to @evert https://github.com/fruux/sabre-dav/blob/master/examples/sql/mysql.calendars.sql#L10

@twistedfall

The old table has these fields:

startdate datetime
enddate datetime

so I suppose yes, it is a regression

@evert
evert commented Mar 9, 2016

What are the symptoms of the problem? The firstoccurence/lastoccurence stuff is specifically for things like querying/freebusy which generally is not really important for dates that far in the past.

So I'm curious what you are seeing. Where is it going wrong? Any error messages?

@PVince81 PVince81 added the sev2-high label Mar 9, 2016
@twistedfall

I encountered this problem when trying to migrate the birthdays using occ dav:sync-birthday-calendar. It kept giving me error messages like:

https://gist.github.com/twistedfall/f3c77514da447f9b6447

and then, after fixing INT UNSIGNED to just INT:

https://gist.github.com/twistedfall/5a6608191dac6cce7cf7 (this is the one created by iOS, when you set up only day and month, but not the year for the contact's birthday)

changing to BIGINT helped in this case.

I also tried to create event with date of 01-01-1950, it failed producing similar error message in the log.

@DeepDiver1975
Member

@twistedfall I just tested this with mysql and firstoccurence is set to 0 - no exception is thrown

@evert
evert commented Mar 9, 2016

So perhaps the migration script should ensure that those values are set to 0 for values < 0. I don't know if this is also an issue on the sabre side.

@twistedfall

Do you mean that you supplied firstoccurence to be 0, or you supplied negative value and it was set to 0 in the database?

@twistedfall

It's not an option to set negative values to 0 as it breaks the date encoded into timestamp, basically a date of birth of 04-12-1950 will be reset to 1970-01-01.

@DeepDiver1975
Member

Do you mean that you supplied firstoccurence to be 0, or you supplied negative value and it was set to 0 in the database?

I basically insert your ics file - https://github.com/owncloud/core/pull/23018/files#diff-f3017aa6638f5486e5938504e2f4faa5R472 and firstoccurrance is 0 in the database

@DeepDiver1975
Member

@twistedfall which database are you using?

@evert
evert commented Mar 9, 2016

It's not an option to set negative values to 0 as it breaks the date encoded into timestamp, basically a date of birth of 04-12-1950 will be reset to 1970-01-01.

This shouldn't be a problem. It doesn't affect your source data, just your indexes.

@DeepDiver1975
Member

It's not an option to set negative values to 0 as it breaks the date encoded into timestamp, basically a date of birth of 04-12-1950 will be reset to 1970-01-01.

as @evert explained this column is only used for query - we have to check if this still works at the end

@twistedfall

I see. The database version is MySQL 5.6.29-1debian8

@DeepDiver1975 DeepDiver1975 added a commit that referenced this issue Mar 9, 2016
@DeepDiver1975 DeepDiver1975 fixes #23004 a98d5f8
@DeepDiver1975 DeepDiver1975 added a commit that referenced this issue Mar 10, 2016
@DeepDiver1975 DeepDiver1975 fixes #23004 6133253
@DeepDiver1975 DeepDiver1975 added a commit that referenced this issue Mar 10, 2016
@DeepDiver1975 DeepDiver1975 fixes #23004 36b543c
@vonschultz

This bug is not fixed. In apps/dav/appinfo/database.xml, I had to change
<name>firstoccurence</name> <type>integer</type> <unsigned>true</unsigned> <length>11</length> to ... <unsigned>false</unsigned> ... before an upgrade with a date before Unix epoch went through. Just changing firstoccurrence to BIGINT doesn't solve the problem if the BIGINT is still unsigned.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment