MySQL improved unicode support

DOMjudge / domjudge

DOMjudge programming contest jury system

https://www.domjudge.org

GNU General Public License v2.0

709 stars 248 forks source link

MySQL improved unicode support #173

Closed bertptrs closed 8 years ago

bertptrs commented 8 years ago

Currently, the database driver uses utf8_general_ci as the collation and utf8 as the character set. This character set does not support characters more than two bytes wide. Since teams generally attempt to use weird characters in their name to show they know they exist, the newer utf8mb4 could be used.

You can change the database on your own and then change the DJ_CHARACTER_SET_MYSQL to the proper value, but in exactly two queries a COLLATE statement is used with a hard coded utf8_general_ci collation.

So the issue is, could this be changed to a more configurable option, with the default being utf8mb4?

thijskh commented 8 years ago

Yes, agreed. It would be nice if it's indeed configurable with that default. We can change the recommended version of MySQL to 5.5, while still supporting earlier MySQL's (the user would just need to change that config parameter).

Would you mind making a pull request?

bertptrs commented 8 years ago

Sure, I just have to clean up what I've done to my own installation and make a patch.

eldering commented 8 years ago

A few remarks/questions:

Are we actually trying to support arbitrary character sets or is this just a transition to proper 4-byte UTF-8 support? I'd vote for the latter.
I think the specific "COLLATE utf8_general_ci" should be replaced by "utf8_unicode_ci" (or the utfmb4 version), since utf8_general_ci is not precisely adhering to the UTF-8 collation rules.
There should either be a configure time option to set this, or we should drop support for MySQL < 5.5.3 because with the proposed patch, things will break for older versions.

bertptrs commented 8 years ago

To answer a your questions:

I think we should improve the UTF-8 support, and not arbitrary character sets. MySQL's utf8 character set unfortunately is a mistake and only implements a small subset of utf8.
You may be correct, but I don't think it will make that much of a difference in sorting, at least not for characters for which users expect an odering.
You are right. I'm not sure on which platforms Domjudge usually runs, but Ubuntu ships with at least 5.5.44 for all supported versions, and I'm sure CentOS can upgrade to it as well. A version bump may have very little compatibility issues.

eldering commented 8 years ago

Ok, RHEL6, Debian wheezy (oldstable) and Ubuntu 14.04 (LTS) have MySQL 5.5.44, so then I'd say we simply drop support for older stuff and get rid of the cruft.

thijskh commented 8 years ago

:+1:

thijskh commented 8 years ago

All for dropping pre-5.5 support. However, I think it would still work for 5.1 by changing just the constants back, only the creation of a new database would then fail. So it's all fairly acceptable.

eldering commented 8 years ago

Issue addressed in recent commits. Note that I made some small changes to the pull request.