Switching to utf8mb4 charset may not be as simple as I thought initially...

MySQL limits the maximum index key size depending on the engine used:

• MyISAM: 1000 bytes [1]
• InnoDB: 767 bytes [2] in MySQL < 5.7.7 (unless 'innodb_large_prefix' option is enabled), 3072 bytes in 5.7.7 and above (or when innodb_large_prefix == ON)

Considering 4 bytes per characters, the maximum length of a character field used as index key is:

• MyISAM: 1000 / 4 = 250 chars
• InnoDB: 767 / 4 = 191 chars

Schema update step 196 updated the user table's username field to 255 chars in length (see 0008017). Converting the table to utf8mb4 or trying to create it from scratch with that charset triggers the following error

1071: Specified key was too long; max key length is 767 bytes

We have the following options:

1. forget about utf8mb4, keep using 3-byte utf8 (eventually, someone will face issues as they try to store 4-byte unicode chars, e.g. emoji or some CJK characters)
2. reduce size of username column from 255 to 191 chars
3. change the idx_user_username index to become a 'prefix' index, i.e. to only cover the first 191 chars of the username field

Option 3 is probably acceptable, since it seems highly unlikely that we would have email addresses longer than 191 chars to begin with, and even less to have 2 of them differ only on or after the 192nd char. Nevertheless there is still the technical possiblity that this situation will occur.

I would recommend number 2 as the safest option, and assuming we don't really need 255 chars for the username. I don't think we do; the original requirement from 0008017 was to allow storing email addresses as user identifier; 255 was set arbitrarily.

[1] http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html
[2] http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Looking at the code under admin/check/ it seems that our current minimum requirement for MySQL is 5.0.8 and hence if we are planning to use utf8mb4, then we need to up such requirement to 5.5.3. Not sure how common is 5.5.3. Though if we end up waiting, then we could make the move directly to 5.7.7 where it wouldn't be necessary to reduce the field size.

I don't have a problem with reducing the max size for username. I assume based on the above, this is the only offending field.

To wait for 5.7.7 is no option.
Even the next version of LTS Ubuntu comes with an older version.

Ubuntu LTS versions that come with 5 years of security updates [1]
12.04 5.5.22
14.04 5.5.35
16.04 5.6.27

Red Hat Enterprise Linux is quite another story [2]
RHEL-5.1 5.0.95
RHEL-6.7 5.1.73
RHEL-7.2 no longer MySQL but MariaDB 5.5.44

So requiring 5.5.3 would rule out Red Hat.

It seems we should stay with utf8 at the moment.

[1] http://distrowatch.com/table.php?distribution=ubuntu
[2] http://distrowatch.com/table.php?distribution=redhat

Thanks to both of you for your comments.

Actually my intention was not to enforce utf8mb4 across the board, but rather to detect the mysql version at install time, and define the charset accordingly (utf8 if < 5.5.3, utf8mb4 otherwise), allowing instances running recent software to benefit from better unicode support..

We can keep MySQL 5.0.8 as minimal support, even though that's effectively end-of-life since 2013.

With regards to MariaDB, at least until version 5.5 it's supposed to be a "drop-in replacement" for MySQL, so (in theory) RHEL 7.2 should be just fine and use utf8mb4.

Not sure how things will evolve with MariaDB 10.x / MySQL 5.7 though, but that's another topic.

Pull request https://github.com/mantisbt/mantisbt/pull/699

@dregad How about the option is using a prefix index and updating the API(s) that looks up by username to potentially handle more than one match? That is assuming the DBMS won't filter these independent of the index anyway. Which I think it should.

The problem is not with filtering, it is about ensuring the key's uniqueness, which can't be guaranteed with a prefix index.

Updating the API would not resolve this issue, and sounds like overengineering for an issue that is anyway quite unlikely to occur (have you ever heard of a 191-char long e-mail address ?)

Interesting note on Drupal's approach to handle index size limitation on utf8mb4 fields https://www.drupal.org/node/1314214

In order to (greatly) simplify the implementation of utf8mb4 charset support, including upgrade steps and future maintenance, I would like to propose that we increase the minimum version requirement for MySQL to 5.5.3 in 2.0.x.

5.5.3 was released in March 2010. It was actually a milestone release, the General availability is 5.5.8 (released 2010-12-03), but I don't see the need to require a higher version than we actually need.

As pointed out by atrol, this will prevent some distros from running our software, but I think that's an acceptable trade-off.

For the record, Drupal followed a similar approach, as documented in the link I referenced in my previous post.

we increase the minimum version requirement for MySQL to 5.5.3 in 2.0.x.

I would prefer this instead of implementing workarounds to fix 0021101.
Maybe target for 2.1.x if someone think it's to early to enforce 5.5.3.

I would prefer this instead of implementing workarounds to fix 0021101.

I agree, but I also believe that we do need a solution for 1.x as well, since depending on MySQL settings, use of any 4-byte char will either result in

• the offending char and everything after it to be silently truncated
• a DB error to occur, preventing data from being saved to the DB

The workaround I propose in 0021101 / PR https://github.com/mantisbt/mantisbt/pull/797 is fairly simple, and prevents an error which is more and more likely to occur as people using Mantis on smartphones are used to inserting emojis.

Of course will need to be reverted once utf8mb4 support has been implemented.

This blog post suggests a different and interesting workaround compared to the pull requests referred to in the previous post: https://roartindon.blogspot.de/2015/04/hacking-utf16-to-work-around-mysqls.html
Rather than replacing everything with <?>, surrogate pairs are used. It requires some unicode decoding code so maybe it's not worth the effort, but still an interesting idea.

Changing target version since we'll have MySQL 5.5.3 as minimum requirement there.

since we'll have MySQL 5.5.3 as minimum requirement there

Did you consider 0020431:0052214 ?
Added also note to 0021841:0054575

There is some generic info about UTF8mb4 here: https://www.everipedia.com/UTF8/

There is some generic info about UTF8mb4 here: https://www.everipedia.com/UTF8/

@travm1 what is your point ?

cool that mantis will to that out of the box utf8mb4, without manuell database stuff with this quite cool mysql, mssql, postgre tool https://www.heidisql.com/ :-)

now mantis is able to process unicode... stuff like this is possible https://unicode.org/emoji/charts/full-emoji-list.html
would be more nice to work with smileys while typing reports, better workplace, not so conservativ.
you don't need grafics anymore.

stuff like http://www.sonderzeichen.de/Emoticons-Alphabet.html this html charakter also works with 😁 for example ... � ���

i'll do a ticket, for supporting input with smileys...

is the bugstracker mysql database really under UTF8mb4 correctly ?

in my previous ticket are the correct unicode smileys, normaly they are working... we all see not the smiley but �

So my mantis mysql database is very old, initially created many many years ago. It still uses latin1 and latin1_swedish_ci. Since this issue is not fixed, I suppose I should migrate to (3 byte) utf8 instead of utfmb4 for now?

@rogueresearch, if you are going to migrate your database from latin1 now, IMHO you might as well go straight for utfmb4. I don't expect any negative impact from this (since utf8mb4 is a superset of the limited utf8 3-byte unicode encoding which was standard until MySQL 8), and you'll be ready for 4-byte unicode without the need for another migration. Just make sure you change the encoding for all tables and columns as well.