DB Migration to MySQL: check if 4byte support is enabled to avoid migration errors

[b0ssi]

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

Bug depends on custom state SQLite database, so probably won't be easy to reproduce, but details below.

1. have database holding certain data that triggers the bug (sorry for being vague here, but this is the unknown variable)
2. attempt migration: php occ db:convert-type mysql --password=<pw> --clear-schema --all-apps <user> <host> <databasename>
3. wait for migration to partially go through, then fail

Expected behaviour

have migration successfully convert all tables

Actual behaviour

I get a partially successful migration, then an exception when trying to convert some field data in contacts/calendar?

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, 6 chunks
 5519/5519 [============================] 100%oc_activity_mq
    0 [>---------------------------]oc_addressbookchanges
chunked query, 7 chunks
 6975/6975 [============================] 100%oc_addressbooks
 4/4 [============================] 100%oc_appconfig
 239/239 [============================] 100%oc_authtoken
 313/313 [============================] 100%oc_bruteforce_attempts
    0 [>---------------------------]oc_calendar_invitations
    0 [>---------------------------]oc_calendar_reminders
 150/150 [============================] 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
 5251/5251 [============================] 100%oc_calendarobjects
 909/909 [============================] 100%oc_calendarobjects_props
chunked query, 6 chunks
 4900/5168 [==========================>-]  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

Server configuration

Operating system:
Debian

Web server:
Apache2

Database:
MariaDB (1:10.5.11-1)

PHP version:
7.4

Nextcloud version: (see Nextcloud admin page)
21.0.4

Updated from an older Nextcloud/ownCloud or fresh install:
this installation dates back a good few years (~2015?) well into OwnCloud days.

Where did you install Nextcloud from:
native web installer/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.

List of activated apps:

Enabled:
  - accessibility: 1.7.0
  - activity: 2.14.3
  - calendar: 2.3.3
  - cloud_federation_api: 1.4.0
  - comments: 1.11.0
  - contacts: 4.0.3
  - contactsinteraction: 1.2.0
  - dashboard: 7.1.0
  - dav: 1.17.1
  - federatedfilesharing: 1.11.0
  - federation: 1.11.0
  - files: 1.16.0
  - files_pdfviewer: 2.1.0
  - files_rightclick: 1.0.0
  - files_sharing: 1.13.1
  - files_trashbin: 1.11.0
  - files_versions: 1.14.0
  - files_videoplayer: 1.10.0
  - firstrunwizard: 2.10.0
  - logreader: 2.6.0
  - lookup_server_connector: 1.9.0
  - nextcloud_announcements: 1.10.0
  - notifications: 2.9.0
  - oauth2: 1.9.0
  - password_policy: 1.11.0
  - photos: 1.3.0
  - privacy: 1.5.0
  - provisioning_api: 1.11.0
  - recommendations: 1.0.0
  - serverinfo: 1.11.0
  - settings: 1.3.0
  - sharebymail: 1.11.0
  - support: 1.4.0
  - survey_client: 1.9.0
  - systemtags: 1.11.0
  - text: 3.2.0
  - theming: 1.12.0
  - twofactor_backupcodes: 1.10.0
  - twofactor_totp: 6.1.0
  - updatenotification: 1.11.0
  - user_status: 1.1.1
  - viewer: 1.5.0
  - weather_status: 1.1.0
  - workflowengine: 2.3.0
Disabled:
  - admin_audit
  - bruteforcesettings
  - encryption
  - files_external
  - user_external
  - user_ldap

Nextcloud configuration:

{
    "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": "21.0.4.1",
        "logtimezone": "UTC",
        "installed": true,
        "theme": "",
        "loglevel": 2,
        "maintenance": false,
        "trashbin_retention_obligation": "auto",
        "htaccess.RewriteBase": "\/",
        "updater.release.channel": "stable",
        "versions_retention_obligation": "auto",
        "twofactor_enforced": "true",
        "twofactor_enforced_groups": [],
        "twofactor_enforced_excluded_groups": [],
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_sendmailmode": "smtp",
        "mail_smtpauth": 1,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpauthtype": "PLAIN",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "encryption.legacy_format_support": false,
        "encryption.key_storage_migrated": false
    }
}

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/...
webdav

Client configuration

Browser:
Firefox

Operating system:
Arch Linux

Logs

most likely not relevant but please request if needed

Web server error log

most likely not relevant but please request if needed

Nextcloud log (data/nextcloud.log)

most likely not relevant but please request if needed

Browser log

most likely not relevant but please request if needed

[b0ssi]

Some additional findings: Looking at the SQLite database I'm trying to migrate to MySQL and looking at the table mentioned (oc_cameraobjects_props) I'm finding a range of entries with a four-byte unicode character prefixed that matches the hex code logged in the error (0x f0 9f 8e 82). Doing a quick lookup it turns out it's... a birthday cake :D and it's (probably) exactly that cake that birthday events prefixed with in the calendar, so this shouldn't be rare corner case.

If this implies that this is an issue of lacking support for (multibyte) unicode during migration it's probably something that should handled as unicode in calendar entries (or in any other readable text really) is probably something that should be expected. I'm mostly guessing and extrapolating from what I can see spontaneously. Happy to provide more context where necessary. Thanks for looking into it!

Update: Guessing that 1366 mentioned in the error log might refer to the objectid in this table, the cow that triggers this error contains the following data:

id	calendarid	objectid	name	parameter	value	calendartype
5688	4	1366	SUMMARY	NULL	<string suffixed with 0x f0 9f 8e 82>	0

[ChristophWurst]

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

[b0ssi]

Terrific, thanks a ton @ChristophWurst, this was the last missing bit. There had been a few issues now resolved in past releases that I bumped into whenever I tried to migrate to MySQL, hence the bias toward suspecting bits in the business logic that might yet need to be straightened out. Would it be worth hinting at the underlying issue more explicitly in the error that gets thrown when attempting to migrate to a database that is not 4-byte enabled? In any case, thanks again; this issue is resolved and migration went through without a hitch 👍

[ChristophWurst]

Would it be worth hinting at the underlying issue more explicitly in the error that gets thrown when attempting to migrate to a database that is not 4-byte enabled?

We could do this. The current admin overview page only checks the current database, so this scenario won't be detected.

[szaimen]

This sounds like a feature request to me

[joshtrichards]

For anyone that feels like implementing this, here are some clues...

The db:convert-type command is implemented here:

https://github.com/nextcloud/server/blob/master/core/Command/Db/ConvertType.php

There's an existing utility function that can be used to check for utfmb4

server/lib/private/DB/MySqlTools.php

Line 18 in b77c151

public function supports4ByteCharset(IDBConnection $connection) {

The check will have to run against the $toDB rather than the existing db:

server/core/Command/Db/ConvertType.php

Line 167 in b77c151

$toDB = $this->getToDBConnection($input, $output);

You can see a working example of how it's used in a different occ command here:

server/core/Command/Db/ConvertMysqlToMB4.php

Lines 42 to 48 in b77c151

	$tools = new MySqlTools();
	if (!$tools->supports4ByteCharset($this->connection)) {
	$url = $this->urlGenerator->linkToDocs('admin-mysql-utf8mb4');
	$output->writeln("The database is not properly setup to use the charset utf8mb4.");
	$output->writeln("For more information please read the documentation at $url");
	return 1;
	}

The existing warning used in our setup checks for the live database can probably just be used here, doing the check before running the conversion if the condition is detected:

server/apps/settings/lib/SetupChecks/MysqlUnicodeSupport.php

Lines 40 to 43 in b77c151

	return SetupResult::warning(
	$this->l10n->t('MySQL is used as database but does not support 4-byte characters. To be able to handle 4-byte characters (like emojis) without issues in filenames or comments for example it is recommended to enable the 4-byte support in MySQL.'),
	$this->urlGenerator->linkToDocs('admin-mysql-utf8mb4'),
	);