Convert database to mysql fails - multibyte emojis

[robin-moser]

Hello,

I'd like to convet my databse to mysql, containing numerous multibyte emojis. The occ db:convert-type creates utf-8 tables, which don't support emojis. I thought with manually changing the collation in mysql the import will succeed, but it failed with the same error. I don't want to manually replace all emojis in my sqlite database to be able to convert.

Is there another option? Could it be safe to manually export sqlite to .sql and import it in mysql or does the db:convert-type some other mystery stuff that I don't have with the manual import?

---

Steps to reproduce

1. Setup NC 14 with sqlite
2. import numerous Files/Contacts/Calendarobjects with multibyte emojis in Filename
3. BEGIN to convert database with php occ db:convert-type --all-apps mysql nextcloud mysql nextcloud
4. PAUSE after table creation (when the promt appears)
5. manually convert tables over mysql:

ALTER TABLE `nextcloud`.`oc_external_config` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `nextcloud`.`oc_accounts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
...

1. CONTINUE convertion (promt yes)

Expected behaviour

Everything in the sqlite database should be converted and imported correctly

Actual behaviour

Convertion fails with:

An exception occurred while executing 'INSERT INTO `oc_calendarobjects_props` (`id`, `calendarid`, `objectid`, `name`, `parameter`, `value`) VALUES(?, ?, ?, ?, ?, ?)' with params ["5272", "31", "2350", "SUMMARY", null, "Example \ud83d\ude0d"]:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\x8D' for column 'value' at row 1

Server configuration

Operating system:
Host: Ubuntu 18.04 LTS
Nextcloud docker image

Web server:
nextcloud:latest docker image (14.0.3.0)
Apache/2.4.25 (Debian) PHP/7.2.12

Database:
mariadb:10.3.5

PHP version:
PHP 7.2.12 (cli)

Nextcloud version: (see Nextcloud admin page)
nextcloud 14.0.3.0

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

Where did you install Nextcloud from:
clean docker image, mounted persistant volumes: /data, /config, /themes, /apps

List of activated apps:

App list

- accessibility: 1.0.1 - activity: 2.7.0 - cloud_federation_api: 0.0.1 - comments: 1.4.0 - dav: 1.6.0 - encryption: 2.2.0 - federatedfilesharing: 1.4.0 - federation: 1.4.0 - files: 1.9.0 - files_pdfviewer: 1.3.2 - files_sharing: 1.6.2 - files_texteditor: 2.6.0 - files_trashbin: 1.4.1 - files_versions: 1.7.1 - files_videoplayer: 1.3.0 - gallery: 18.1.0 - logreader: 2.0.0 - lookup_server_connector: 1.2.0 - nextcloud_announcements: 1.3.0 - notifications: 2.2.1 - oauth2: 1.2.1 - password_policy: 1.4.0 - provisioning_api: 1.4.0 - serverinfo: 1.4.0 - sharebymail: 1.4.0 - support: 1.0.0 - systemtags: 1.4.0 - theming: 1.5.0 - twofactor_backupcodes: 1.3.1 - updatenotification: 1.4.1 - workflowengine: 1.4.0 Disabled: - admin_audit - files_external - firstrunwizard - survey_client - user_external - user_ldap

Nextcloud configuration:

Config report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.example.de"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "https:\/\/cloud.example.de",
        "dbtype": "sqlite3",
        "version": "14.0.3.0",
        "installed": true,
        "maintenance": false,
        "theme": "",
        "loglevel": 1,
        "updater.release.channel": "stable",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "php",
        "mail_smtpauthtype": "LOGIN",
        "mail_domain": "***REMOVED SENSITIVE VALUE***"
    }
}

[nextcloud-bot]

GitMate.io thinks possibly related issues are #10440 (MYSQL), #9552 (Fail to convert database to PgSQL), #6745 (MySQL Database rights), #4157 (Status 500 on missing MySQL Database), and #2893 (Convert database to mysql fails - nc11, fresh install).

[nickvergessen]

Did you follow https://docs.nextcloud.com/server/13/admin_manual/configuration_database/mysql_4byte_support.html ?