3.0.33 Problems Installing with utf8mb4 and InnoDB

[clsource]

Using

PHP 7.0.10
MySQL 5.6.28

A Mysql Database with utf8mb4_general_ci

Selected Blank Profile

After selecting
utf8mb4 and InnoDB

Shows an Error
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

After sending the admin data
shows (<db> is the name for the database)

SQLSTATE[42S02]: Base table or view not found: 1146 Table '<db>.caches' doesn't exist

And the installation can not continue.

Cheers 👍

[clsource]

Also detailed here

#2018

[ryancramerdesign]

I don't have an environment where I can duplicate this yet, but am guessing that the combination of InnoDB + utf8mb4 must have an even shorter index size limitation than MyISAM + utf8mb4. I'm assuming you don't see the same error with MyISAM + utf8mb4? But assuming the InnoDB/utf8mb4 combination adds more limitations to index size, I guess the next question would be what table/column it's coming from, and what the limitations are. I suppose I need to find an environment I can duplicate this in, as the error messages aren't clear about where the issue is occurring. But if you or @LostKobrakai have any idea, please let me know.

As for <db> I think that's just MySQL error notation, as we don't use that notation anywhere. I'm pretty sure the error about the caches table not existing is just a side effect of a failed install and probably doesn't have anything to do with it other than preventing the issue from going further. That's because the caches table is installed via a system update, which wouldn't have been able to execute due to the failed install.

[LostKobrakai]

For me it was only the caches table. I ran the code in the install.sql manually and iirc this worked without issue or I needed to change the text key lenght, not sure.

[ryancramerdesign]

@LostKobrakai do you see the error if you attempt to run this query manually?

CREATE TABLE caches (
  name VARCHAR(128) NOT NULL PRIMARY KEY,
  data MEDIUMTEXT NOT NULL, 
  expires DATETIME NOT NULL, 
  INDEX expires (expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I'm hoping not, not being able to have a 128 char primary key would be a problem.

Also, which install.sql did you test? We've got one in /wire/core/install.sql and another in each of the site profiles, like /site-blank/install/install.sql for example.

[LostKobrakai]

I think I did use the site-default one, but just notices it didn't use InnoDB on that run. But I just checked what you posted above and it did run manually without problem.

[clsource]

Utf8mb4 and myisam installs well.

http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

The number of allowed characters just depends on your character set. UTF8
may use up to 3 bytes per character, utf8mb4 up to 4 bytes, and latin1 only
1 byte. Thus for utf8 your key length is limited to 255 characters, since 3*255
= 765 < 767

Since utf8bm4 is up to 4 bytes the key length should be up to 191 chars
(4*191=764).

[clsource]

It seems the problem is with the indexes
https://answers.launchpad.net/maria/+question/241612

This is not a bug, but a (rather well known) limitation in MySQL/MariaDB.

From the manual at http://dev.mysql.com/doc/refman/5.6/en/create-table.html

"For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created
that use only the leading part of column values, using col_name(length)
syntax to specify an index prefix length.
...
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note
that prefix limits are measured in bytes, whereas the prefix length in
CREATE TABLE statements is interpreted as number of characters ..."

A utf8 character can use up to 3 bytes. Hence you cannot index columns or
prefixes of columns longer than 333 (MyISAM) or 255 (InnoDB) utf8
characters.

[LostKobrakai]

Ah, I'm using mariadb here as well.

[ryancramerdesign]

The limitation in utf8mb4 is actually 250 rather than 255 if I recall. Looks like I still had some varchar(255) in the install.sql files, which needed to be varchar(250) for utf8mb4. I have updated those and pushed that. Hopefully this fixes it but please let me know what you find. Thanks.

[clsource]

I continue to see the problem.
I tested changing site/install.sql all varchar(250) to varchar(191)
and no problems.

If I change to varchar(192) the problem continues

[ryancramerdesign]

Thanks, I've pushed another update that makes it use 191 when InnoDB+utf8mb4. Rather than updating the install.sql files, the installer (install.php) just does a live replacement on the SQL when executing the install queries. That way 250 will still be used when it can be, as 191 is getting a little shorter than I'd like.

[clsource]

Its seems working now 👍
thanks 😄