Switch MySQL database character set and collation to UTF8 Multibyte if supported

[tschloeter]

Hello,

I found out that synchronizing a calendar or address book that contains UTF-8 Multibyte characters with a length of 4 bytes via CalDAV / CardDAV breaks the database. This especially happens when an entry contains Emoji symbols as most of these are 4-byte UTF-8 characters.

I solved the problem by changing the database character set to "utf8mb4" and collation to "utf8mb4_unicode_ci" respectively using ALTER DATABASE / ALTER TABLE commands.
Then I changed the server/client character sets and collation in my.cnf of my MariaDB server.
Finally, I performed a full-text search for "utf8" in the owncloud htdocs directory and changed all SQL commands to take advantage of the new character set and collation.

Now the calendar and address book do support Emoji symbols.

A good explanation is given on this website:
https://mathiasbynens.be/notes/mysql-utf8mb4

Regards,
Thomas

[karlitschek]

@DeepDiver1975 what do you think?

[DeepDiver1975]

We have been discussing this many times - as soon as we switch to utf8mb4* the index max length will shrink even further - as a result we would need to redesign the whole database schema.

We are not going to do this - sorry - switch to postgres

[tschloeter]

This is what I also experienced. I had to limit the index size of half a dozen fields to 191 bytes, so the index fits in the max length of 767 bytes.

What is the bad thing about that? I understand that the change is not going to make it into the mainstream, but I would like to understand what problems I have to expect from the change. I imagined that there will be performance issues or problems if two very long entries only differ after the first 191 characters. Is this correct?

[DeepDiver1975]

The problem is that some of the indexes cannot be generated - you will end up with no indexes (if the full schema can be applied at all)

[tschloeter]

I did that as I told above. I had to limit the index size in five or six cases (VARCHAR 255-fields). The indexes can still be generated, but they only apply to the first 191 chars in the string. Apart from that there was no problem applying the schema.

What I meant above is whether limiting the index size affects integrity or "only" performance? Does it even affect anything at all if I do not have many entries with more than 191 chars?

As far as I understand, there should not be any issues regarding functionality, right?

[DeepDiver1975]

or "only" performance

even "only" that is not acceptable for us

[tschloeter]

Sure, as I said, I am not asking because I want you to implement it.

I just do not expect any issues apart from performance, but I would like to know if I should. So far, for my ~10 accounts setup, I did not experience any problems.

If my setup is just not well-tested and therefor "experimental", this is OK for me.

If you strongly recommend not to use ownCloud with shorter indexes due to a known issue that will break the database for sure, I would reinstall the stable release from scratch.

[beyerservice]

@tschloeter Hi Thomas,
just want to know it this is still working for you?
I have about 2000 caldav entries with emoji and its really boring to hear: dont us them...
And onwcloud could at last remove those items instead of just making things more bad (vevent-data corrupt and to on)

[tschloeter]

Hello,

I am still running OC 8.0 with my dirty-hack patch to use utf8mb4. Everything works fine, I have not encountered a single problem yet despite I am not able to update to 8.1 or 8.2 without losing my patch. I have address books and calendars with hundrets of emojis on it. In my opinion, these days emojis are part of the standardized character set just as basic ASCII has been in the 80’s. Software that does not support them just ignores what is going on out there.

Cheers,
Thomas

Am 05.11.2015 um 19:40 schrieb beyerservice notifications@github.com:

@tschloeter https://github.com/tschloeter Hi Thomas,
just want to know it this is still working for you?
I have about 2000 caldav entries with emoji and its really boring to hear: dont us them...
And onwcloud could at last remove those items instead of just making things more bad (vevent-data corrupt and to on)

—
Reply to this email directly or view it on GitHub #15660 (comment).

[beyerservice]

right, thats exactly my thought...
Too sad they discuss anything else.
Because I am on shared host (and cant change /etc/my.cnf) I need to test, it it works with sqlite

[enoch85]

Is this supported yet @DeepDiver1975 ?

[DeepDiver1975]

Oc10 will come with support for utf8mb4