search

nextcloud / server

☁️ Nextcloud server, a safe home for all your data
https://nextcloud.com
GNU Affero General Public License v3.0
27.52k stars 4.08k forks source link

DB Migration Fails if Calendar Event Starts Before Start of the Unix Epoch #17324

Closed b0ssi closed 3 years ago

b0ssi commented 5 years ago

Steps to reproduce

  1. Have a nextcloud instance running on SQLite
  2. Create a calendar event with a "start at" timestamp before 1970-01-01
  3. Try and migrate the database to one that uses stronger typing (for the oc_calendarobjects.firstoccurence field, e.g. MySQL)

Expected behaviour

The database should migrate over without excepting.

Actual behaviour

The migration process throws an exception, if it tries to migrate a calendar event that has a start date (and with that occurrances) which lies before 1970-01-01 (start of the Unix epoch). I believe this is caused by the fact that the oc_calendarobjects.firstoccurence column has an unsigned type, while a date before 1970-01-01 would possibly be denoted by a negative timestamp in the oc_calendarobjects.firstoccurence table (?).

Please note that the possible causes I mention here are mostly assumptions on my side, but correlation seems to be likely.

Error thrown looks something like this:

An exception occurred while executing

'INSERT INTO `oc_calendarobjects`
(
  `id`,
  `calendardata`,
  `uri`,
  `lastmodified`,
  `etag`,
  `size`,
  `uid`,
  `componenttype`,
  `firstoccurence`,
  `lastoccurence`,
  `classification`,
  `calendarid`,
  `calenda  
   rtype`
) VALUES (
   ?,
   ?,
   ?,
   ?,
   ?,
   ?,
   ?,
   ?,
   ?,
   ?,
   ?,
   ?,
   ?
)
'

with params

[
  "119",
  "***REMOVED SENSITIVE VALUE***",
  "***REMOVED SENSITIVE VALUE***",
  "1462500697",
  "***REMOVED SENSITIVE VALUE***",
  "1014",
  "***REMOVED SENSITIVE VALUE***",
  "VEVENT",
  "-847584000",
  "2145916800",
  "0",
  "9",
  "0"
]

Server configuration

Operating system: Debian Buster

Web server: Apache2

Database: MariaDB

PHP version: 7.3

Nextcloud version: (see Nextcloud admin page) 16.0.4

Updated from an older Nextcloud/ownCloud or fresh install: Continuously updated for many major release versions now.

Where did you install Nextcloud from: Not entirely sure what this is asking - source of install was the .tar.gz archive.

Signing status:

Signing status ``` Login as admin user into your Nextcloud and access http://example.com/index.php/settings/integrity/failed paste the results here. > No errors have been found. ```

List of activated apps:

App list ``` If you have access to your command line run e.g.: sudo -u www-data php occ app:list from within your Nextcloud installation folder Enabled: - accessibility: 1.2.0 - activity: 2.9.1 - calendar: 1.7.1 - cloud_federation_api: 0.2.0 - comments: 1.6.0 - contacts: 3.1.3 - dav: 1.9.2 - federatedfilesharing: 1.6.0 - federation: 1.6.0 - files: 1.11.0 - files_pdfviewer: 1.5.0 - files_rightclick: 0.15.1 - files_sharing: 1.8.0 - files_texteditor: 2.8.0 - files_trashbin: 1.6.0 - files_versions: 1.9.0 - files_videoplayer: 1.5.0 - firstrunwizard: 2.5.0 - gallery: 18.3.0 - logreader: 2.1.0 - lookup_server_connector: 1.4.0 - nextcloud_announcements: 1.5.0 - notifications: 2.4.1 - oauth2: 1.4.2 - password_policy: 1.6.0 - privacy: 1.0.0 - provisioning_api: 1.6.0 - recommendations: 0.4.0 - serverinfo: 1.6.0 - sharebymail: 1.6.0 - support: 1.0.0 - survey_client: 1.4.0 - systemtags: 1.6.0 - theming: 1.7.0 - twofactor_backupcodes: 1.5.0 - twofactor_totp: 3.0.1 - updatenotification: 1.6.0 - viewer: 1.1.0 - workflowengine: 1.6.0 Disabled: - admin_audit - bruteforcesettings - encryption - files_external - user_external - user_ldap ```

Nextcloud configuration:

Config report ``` If you have access to your command line run e.g.: sudo -u www-data php occ config:list system from within your Nextcloud installation folder { "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": "sqlite3", "version": "16.0.4.1", "logtimezone": "UTC", "installed": true, "theme": "", "loglevel": 2, "maintenance": false, "trashbin_retention_obligation": "auto", "htaccess.RewriteBase": "\/", "updater.release.channel": "production", "versions_retention_obligation": "auto", "twofactor_enforced": "true", "twofactor_enforced_groups": [], "twofactor_enforced_excluded_groups": [] } } or Insert your config.php content here. Make sure to remove all sensitive content such as passwords. (e.g. database password, passwordsalt, secret, smtp password, …) ```

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)

LDAP config ``` With access to your command line run e.g.: sudo -u www-data php occ ldap:show-config from within your Nextcloud installation folder > a big red "There are no commands defined in the "ldap" namespace." Without access to your command line download the data/owncloud.db to your local computer or access your SQL server remotely and run the select query: SELECT * FROM `oc_appconfig` WHERE `appid` = 'user_ldap'; Eventually replace sensitive data as the name/IP-address of your LDAP server or groups. ```

Client configuration

Browser: Firefox

Operating system: Arch Linux

Logs

Web server error log

Web server error log ``` n/a ```

Nextcloud log (data/nextcloud.log)

Nextcloud log ``` n/a ```

Browser log

Browser log ``` Insert your browser log here, this could for example include: a) The javascript console log b) The network log c) ... n/a ```
kesselb commented 5 years ago

cc @georgehrke

georgehrke commented 5 years ago

@b0ssi How did you create that event, which is starting before 1970-01-01 in the first place? The CalDAV server is supposed to reject such events.

j-ed commented 5 years ago

@georgehrke I would assume that many recurring birthday event are starting before 1970-01-01. Because of that, blocking dates prior that date would be very unsatisfying from my point of view.

georgehrke commented 5 years ago

@j-ed. If a contact's birthday is before 1970, the year that the recurring event starts in is automatically set to 1970. (The year that is displayed is obviously the correct one.)

https://github.com/nextcloud/server/blob/master/apps/dav/lib/CalDAV/BirthdayService.php#L239L241

j-ed commented 5 years ago

@georgehrke That's new to me, thank you for the clarification.

b0ssi commented 5 years ago

@georgehrke two possibilities:

  1. Those events are fairly old and either manually imported them in an early version of Nextcloud (maybe the first), or they might even have been migrated over from a previous Owncloud installation (I'm not entirely sure any more, sorry).
  2. I am also syncing the same calendars from Android into Nextcloud (using DAVx5). I only enabled this recently (past two months max).

So, depending on how the sync works, those events might still originate from source 1., or might have been force-updated by source 2. It looks like most (all?) events affected are events that I have not updated in a long time (certainly not since I enabled the Android calendar sync).

One other detail is that I only recently (a week or two ago) enabled the Calendar app in Nextcloud, but I believe it's been installed all along.

One observation I made later (sorry, should have added this right-away) seems to be in-line with what @georgehrke mentions to @j-ed. I do the following:

Note though that in this case these are not birthday events, but events I have set up manually (that I have set to a start date before 1970).

On a related tangent, I also ran into some issues with some of the Contacts: At least one had unicode characters prepending the title- or name-field that the migration process failed to write into the MariaDB SQL database during migration.

Hope that gives some insight; it seems like either some migration/upgrade checks that would align calendar/contacts data with what's expected (e.g. reset to 1970) are not run (during upgrades or other appropriate, specific points), or DAVx5 is syncing "bad" data and Nextcloud doesn't apply those rectifying filters during sync, e.g. if an incoming DVAx5 event is prior 1970.

This is possibly more of a rare case, but maybe not so much, as it'd probably be common for older calendar-events or contacts to hang around?

georgehrke commented 5 years ago

On a related tangent, I also ran into some issues with some of the Contacts: At least one had unicode characters prepending the title- or name-field that the migration process failed to write into the MariaDB SQL database during migration.

Please file a dedicated issue for this. :)

I will need to do some further checks about our caldav server and comment again later today.

b0ssi commented 5 years ago

Hey @georgehrke, let me know if there's any more info I could provide that might help, or is this deemed to much of an edge-case to pursue at this point?

szaimen commented 3 years ago

Is this Issue still valid? If not, please close this issue. Thanks! :)

b0ssi commented 3 years ago

I'll confirm soon @szaimen. Last time I tried it was still actual (which was well after the above discussion but by now a while ago again). Will update this spot~

b0ssi commented 3 years ago

@szaimen Sorry for the wait, finally got a chance to test. It seems conversion from sqlite to mysql/mariadb is still failing, though failure looking different on the command line.

As per docs I'm running: sudo -u www-data php occ db:convert-type --password=<pw> --all-apps mysql <user> <host/ip> <dbname> The converter gets to the point where it confirms with the following:

Creating schema in new database
The following tables will not be converted:
oc_documents_invite
oc_documents_member
oc_documents_op
oc_documents_revisions
oc_documents_session
oc_lucene_status
oc_music_album_artists
oc_music_albums
oc_music_ampache_sessions
oc_music_ampache_users
oc_music_artists
oc_music_playlist_tracks
oc_music_playlists
oc_music_tracks
oc_passman_credentials
oc_passman_delete_vault_request
oc_passman_files
oc_passman_revisions
oc_passman_share_request
oc_passman_sharing_acl
oc_passman_vaults
oc_privatedata
Continue with the conversion (y/n)? [n] y
oc_accounts

but soon after fails as such:

 0/3 [>---------------------------]   0%
In QuoteHelper.php line 62:

  Only strings, Literals and Parameters are allowed  

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

Uncertain if this might possibly be the same/a related issue, or entirely different in nature. Please let me know if I'd better open a separate issue for this, or if there's anything I could try to debug or gather extra telemetry.

szaimen commented 3 years ago

Looks like this was just fixed yesterday: https://github.com/nextcloud/server/issues/26085

szaimen commented 3 years ago

Please test again with NC21.0.3 Thanks!

b0ssi commented 3 years ago

Sure thing, will do as soon as the update to 21.0.3 shows up on our end (stable) :+1:

b0ssi commented 3 years ago

Hi @szaimen - just upgraded to 21.0.3 and tried conversion again; it's definitely getting farther now but still fails due to an unexpected datetime format it seems.

Creating schema in new database
The following tables will not be converted:
oc_documents_invite
oc_documents_member
oc_documents_op
oc_documents_revisions
oc_documents_session
oc_lucene_status
oc_music_album_artists
oc_music_albums
oc_music_ampache_sessions
oc_music_ampache_users
oc_music_artists
oc_music_playlist_tracks
oc_music_playlists
oc_music_tracks
oc_passman_credentials
oc_passman_delete_vault_request
oc_passman_files
oc_passman_revisions
oc_passman_share_request
oc_passman_sharing_acl
oc_passman_vaults
oc_privatedata
Continue with the conversion (y/n)? [n] y
oc_accounts
 3/3 [============================] 100%oc_accounts_data
    0 [>---------------------------]oc_activity
chunked query, 5 chunks
 4709/4709 [============================] 100%oc_activity_mq
    0 [>---------------------------]oc_addressbookchanges
chunked query, 7 chunks
 6809/6809 [============================] 100%oc_addressbooks
 4/4 [============================] 100%oc_appconfig
 239/239 [============================] 100%oc_authtoken
 21/21 [============================] 100%oc_bruteforce_attempts
    0 [>---------------------------]oc_calendar_invitations
    0 [>---------------------------]oc_calendar_reminders
 151/151 [============================] 100%oc_calendar_resources
    0 [>---------------------------]oc_calendar_resources_md
    0 [>---------------------------]oc_calendar_rooms
    0 [>---------------------------]oc_calendar_rooms_md
    0 [>---------------------------]oc_calendarchanges
chunked query, 6 chunks
 5244/5244 [============================] 100%oc_calendarobjects
 910/910 [============================] 100%oc_calendarobjects_props
chunked query, 6 chunks
 4900/5169 [==========================>-]  94%
In ExceptionConverter.php line 114:

  An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8E\x82 L...' for column `nextcloud`.`o
  c_calendarobjects_props`.`value` at row 1

In Exception.php line 26:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8E\x82 L...' for column `nextcloud`.`oc_calendarobjects_props`.`value` at row 1

In Statement.php line 82:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8E\x82 L...' for column `nextcloud`.`oc_calendarobjects_props`.`value` at row 1

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

Should we continue this as part of this issue or create a new one if it's distinct? Thank you!

szaimen commented 3 years ago

Please open a new issue for that one. Thanks for the feedback!

b0ssi commented 3 years ago

No problem, thanks for looking into the issue! :+1:

b0ssi commented 3 years ago

FYI I finally opened a new bug report here: https://github.com/nextcloud/server/issues/28875