Preparing for move to utfbmb4 should I limit character length?

Question

I'm looking into moving our database to utf8mb4 - as indicated will be required in the future. I've been trying to find information about the possible impact in doing so and I found this post: https://mathiasbynens.be/notes/mysql-utf8mb4

That article indicates the following (it has a nice, thorough explanation if anyone else is interested, this is just the summary of one section):

The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters when using utf8mb4. (Because of this, I had to change some indexed VARCHAR(255) columns to VARCHAR(191).)

So does it make sense that if I need to create any custom text fields in the near future (before making the swap) I should go ahead and limit the database field length to 191 (or less)? I'm looking for someone to verify I have not jumped to the wrong conclusion.

Does this also imply that I should look through my existing data for any fields with data that might be longer than 191 characters - and then adjust? I assume I could not adjust the data but it may be truncated upon conversion to utf8mb4 if it is longer than 191 characters.

Answer 1

If you have a modern (e.g. Debian 10+) server installed fresh, you should be OK. If not, I believe these are the settings you want in your server config (e.g. /etc/mysql/mariadb.conf.d/50-server.cnf if you're using MariaDB on Debian)

innodb_large_prefix = 1
#https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_file_format
innodb_file_format = barracuda
# https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_file_per_table
innodb_file_per_table = 1
# Set default row format to a Barracuda one. COMPRESSED is useful on a
# per-table basis.
innodb_default_row_format = DYNAMIC

The large prefix is key here:

If set to 1, tables that use specific row formats are permitted to have index key prefixes up to 3072 bytes. If not set, the limit is 767 bytes.

The row formats allowed are DYNAMIC and COMPRESSED. (The latter can be very handy if you don't do many writes to a table with a lot of compressible data, e.g. activities, but that's probably a special case.)

So then you don't have to shorten your text fields.

Answer 2

It's a good question. I haven't come across any problems like this. I see one varchar(512) custom field in utf8mb4 and the longest value it has at the moment is 383 and both the field and index seem fine, but it wasn't converted it's always been utf8mb4. I do have innodb_default_row_format=dynamic which I remember seeing somewhere that's what tables need.

That article is from 2012 too so some things may have changed.

For indexes, civi only creates an index for custom fields if you check the is_searchable box, so that could be another way to work around indexes at least if you end up with those troubles.

Also it's not well-advertised but you can test conversion by using api3 system.utf8conversion which will convert your tables. You don't need to manually run alter table statements.