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

[jegelstaff]

Robert ran into this issue when trying to install Formulize 7.11 and I have a solution I think...

It comes down to your database version. If you're using MariaDB then version 10.4 or higher should be OK as far as I can tell. I have tested the table creation on version 10.5 and it's OK.

If you're using MySQL, then version 5.7 or higher should be OK, from what I can tell looking at the documentation.

The problem comes from a couple recent changes. The fundamental issue is that there's some index being created on a database table where the size of the keys in the index is too big. What does that mean?

It's lines like this in the modules/formulize/sql/mysql.sql file:

KEY ele_display ( ele_display ( 255 ) )

That says there's 255 bytes from the ele_display field in each record, being used for the key in question. Lines saying UNIQUE and PRIMARY are subject to this problem too.

OK, but 255 is less than 767 right?

Yes, except...

Recently I switched the installer to default to the utf8mb4 charset. Why does that matter? Well, that's the right charset to use because that is basically what people mean by UTF-8 these days, even though in mariadb/mysql 'utf8' means something different from 'utf8mb4'. The point is, utf8mb4 charset in mysql/mariadb is capable to storing all known characters, including all accented characters, and emojis. It's 2023. UTF-8 is the way to go, and 'utf8mb4' is how you do it in mariadb/mysql.

Problem is, full blown UTF-8 uses four bytes per character, not one (that's the mb4 part - multibyte 4). So, the 255 in that key up there is actually 255 x 4 = 1020.

So I think that's the culprit. But the versions of MariaDB and MySQL mentioned above have much higher limits on key sizes, or no limit, so this issue should go away in that case.

If you can't change your database version, you do have the option of changing the default charset at an earlier point in the installer. You could switch it to 'regular' utf8. This is an option when the database is first setup in the installer. Basically, where it says utf8mb4 right now, change it to utf8, and things should be okay. BUT this is only advisable if you know you do not need to work with the full set of utf8 characters, such as emojis, because you need utf8mb4 for those.

--Julian