Database conversion will fail when emojies are used as part of tasks or other calendar objects

py-crash commented 3 years ago

How to use GitHub

Please use the 👍 reaction to show that you are affected by the same issue.
Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
Subscribe to receive notifications on status change and new comments.

Steps to reproduce

Install Nextcloud unsing SQLite Database.
Create some tasks using emojis in the Name. The tasks wil be shown without any problem in the web interface. It will also work when reading on Task.org app on android.

Screenshot

Start conversion:

$ occ db:convert-type --all-apps mysql nxtcloudadmin nextcloud-db nextcloud

Expected behaviour

THe database migration should succed without a problem. Or the application should not allow emojis as part of the tasks.

Actual behaviour

The migration fails throwing the following error:

 629/629 [============================] 100%oc_calendarobjects_props
chunked query, 2 chunks
  263/1187 [======>---------------------]  22% 
In AbstractMySQLDriver.php line 106:

  An exception occurred while executing 'INSERT INTO `oc_calendarobjects_props` (`id`, `calendarid`, `objectid`, `name`, `parameter`, `value`, `calendartype`) VALUES(?, ?, ?, ?, ?, ?,   
  ?)' with params ["3937", "1", "823", "SUMMARY", null, "Goat counter \ud83d\udc10", "0"]:                                                                                                

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

In PDOStatement.php line 129:

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

In PDOStatement.php line 127:

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

Server configuration

Operating system: Ubuntu 20.04 arm64

Web server: Integrated web server in LinuxServer.io container

Database: SQLite and MariaDB

PHP version: -

Nextcloud version: 20.0.8

Updated from an older Nextcloud/ownCloud or fresh install: Updated from an older Nextcloud version

Where did you install Nextcloud from: LinuxServer.io container

Signing status:

``` No errors have been found. ```

List of activated apps:

App list

```Enabled: - accessibility: 1.6.0 - activity: 2.13.4 - bruteforcesettings: 2.0.1 - cloud_federation_api: 1.3.0 - comments: 1.10.0 - contactsinteraction: 1.1.0 - dashboard: 7.0.0 - dav: 1.16.2 - deck: 1.2.5 - encryption: 2.8.1 - federatedfilesharing: 1.10.2 - federation: 1.10.1 - files: 1.15.0 - files_pdfviewer: 2.0.1 - files_rightclick: 0.17.0 - files_sharing: 1.12.2 - files_trashbin: 1.10.1 - files_versions: 1.13.0 - files_videoplayer: 1.9.0 - firstrunwizard: 2.9.0 - logreader: 2.5.0 - lookup_server_connector: 1.8.0 - mail: 1.9.1 - nextcloud_announcements: 1.9.0 - notifications: 2.8.0 - oauth2: 1.8.0 - password_policy: 1.10.1 - photos: 1.2.3 - privacy: 1.4.0 - provisioning_api: 1.10.0 - recommendations: 0.8.0 - serverinfo: 1.10.0 - settings: 1.2.0 - sharebymail: 1.10.0 - support: 1.3.0 - survey_client: 1.8.0 - systemtags: 1.10.0 - tasks: 0.13.6 - text: 3.1.0 - theming: 1.11.0 - twofactor_backupcodes: 1.9.0 - updatenotification: 1.10.0 - user_status: 1.0.1 - viewer: 1.4.0 - weather_status: 1.0.0 - workflowengine: 2.2.0 Disabled: - admin_audit - files_external - user_ldap ```

Nextcloud configuration:

Config report

``` { "system": { "memcache.local": "\\OC\\Memcache\\APCu", "datadirectory": "***REMOVED SENSITIVE VALUE***", "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "home-server", "home-server.local" ], "dbtype": "sqlite3", "version": "20.0.8.1", "overwrite.cli.url": "https:\/\/home-server", "installed": true, "maintenance": false, "mail_smtpmode": "smtp", "mail_smtpsecure": "ssl", "mail_sendmailmode": "smtp", "mail_from_address": "***REMOVED SENSITIVE VALUE***", "mail_domain": "***REMOVED SENSITIVE VALUE***", "mail_smtpauthtype": "LOGIN", "mail_smtpauth": 1, "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_smtpname": "***REMOVED SENSITIVE VALUE***", "mail_smtppassword": "***REMOVED SENSITIVE VALUE***", "mail_smtpport": "465", "loglevel": 2, "encryption.legacy_format_support": false, "encryption.key_storage_migrated": 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

Eventually replace sensitive data as the name/IP-address of your LDAP server or groups.

</details>

### Client configuration
**Browser:** OCC comand-line

**Operating system:** ubuntu 24.04 arm64

### Logs

<!--- Reports without logs might be closed as unqualified reports! -->

#### Web server error log
<details>
<summary>Web server error log</summary>

No errors in nginx log. The only error in `php/error.log`:

[19-Mar-2021 12:15:53] WARNING: [pool www] server reached pm.max_children setting (5), consider raising it


</details>

#### Nextcloud log (data/nextcloud.log)

No errors in `data/nextcloud.log`
</details>

py-crash commented 3 years ago

I removed all the emojis from the tasks. And I found that this also happens with the mail app:

  An exception occurred while executing 'INSERT INTO `oc_mail_messages` (`id`, `uid`, `mailbox_id`, `subject`, `sent_at`, `flag_answered`, `flag_deleted`, `flag_draft`, `flag_flagged`,  
   `flag_seen`, `flag_forwarded`, `flag_junk`, `flag_notjunk`, `flag_attachments`, `flag_important`, `structure_analyzed`, `preview_text`, `updated_at`, `message_id`, `references`, `in  
  _reply_to`, `thread_root_id`, `flag_mdnsent`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["48", "40559", "21", "\ud83d\udcce  REDACTED", "1610920043", "0", "0", "0", "0", "1", "0", "0", "0", null, "0", "0", null, "1616171437", "<REDACTED>", "[  
  ]", null, "<REDACTED>", "0"]:                                                                                                                           

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

Some of the elements where changed as REDACTED for privacy reasons.

I just removed my e-mail account from nextcloud to try to continue with the conversion.

kesselb commented 3 years ago

https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/mysql_4byte_support.html

As this seems to be a setup issue I would like to ask you to raise your question at https://help.nextcloud.com

py-crash commented 3 years ago

Thanks for the fast response, and sorry for not seing the documentation.

And lots of thank you for all the great work on nextcloud

nextcloud / server