Why do some fields have a varchar precision of 191 for modUserProfile in the modx schema?

Development
1

Looking through modx.mysql.schema.xml, the definition for modUserProfile has a few fields that have a varchar precision of 191:

  • country
  • city
  • photo
  • website

Why 191? Seems like such a random number?

2

It’s not random, it’s math. :wink:

  • On a database that uses a utf8mb collation, each character takes 4 bytes.
  • On innodb, indexes (the prefixes to be exact) can contain up to 767 bytes.

So, the max indexable length on utf8mb + innodb is 767/4=191 characters.

3 Likes
3

As I’ve just noticed, MySQL 5.7 and above have increased the allowed index length so it’s now only limited to 191 length for 5.6 and below.

Perhaps MODX 3 can up the minimum requirements? :slight_smile:

1 Like
4

Thanks @markh and @digitalpenguin, that makes sense!

Why then doesn’t it apply to the other fields? For instance, city has a precision of 191, but state has a precision of 25. Why the discrepancy?

(Also, I’m asking with the utmost respect for the MODX team. I’m relatively new to DB design and I’m trying to understand the rationale behind certain decisions to further my own knowledge.)

5

Most if not all of the varchar fields that are now 191 characters in length were previously 255. This fact is because before utf8mb4 collations were available in MySQL and InnoDB became the default storage engine, indices were not constrained as @markh described. The state field has always been smaller and so was not modified from 255 to 191.

That said, columns that are not part of an index, which city is not, did not need to be reduced to 191 characters. In retrospect, the decision to modify all of the existing varchar(255) fields to varchar(191) was done with some misunderstanding of the issue. But that is the change we made and was accepted into the core to address problems users were having when trying to deploy MODX with utf8mb4 collations.

2 Likes
6

Ahh, that makes sense. Thanks @opengeek et al!

7

A little late to the party, but I just filed https://github.com/modxcms/revolution/issues/15339.

I found this thread trying to figure out my installation issue. I then noticed the manager behavior as well. I thought I’d post here in case others come across this discussion they can refer back to the github issue.

I could be the only Extra author with an element (snippet in my case) description over 191 characters :slight_smile:. I couldn’t find any existing issue for this, so apologies if its a duplicate.