[Neos] Migrations: Specified key was too long; max key length is 767 bytes

[Christian Müller]

Hi all,

I now got the info from two persons that the migrations of Neos didn't 
work for them and stopped with the error
"Specified key was too long; max key length is 767 bytes".

The main reason this happens is:
"CREATE INDEX path_workspace ON typo3_typo3cr_domain_model_nodedata 
(path, workspace)"

in migration "Version20131129110302".
This specific index is removed again one migration version later. The 
problem is that MySQL silently reduced the key size to the max length it 
could handle, but this depends on the MySQL configuration and version. 
 From some version on the silent truncation is not happening at all 
regardless of your configuration and will throw the error.
For reference see also [1].

Now for this specific index I really would suggest we remove it from the 
migrations, as currently it is created and removed again. It wouldn't be 
breaking in any case. Even if you are on Version20131129110302 it just 
means you have an unnecessary index that you should drop. What do you think?

But this problem is just the tip of the iceberg so to speak. Because I 
got another report about

"CREATE TABLE flow3_resource_resourcepointer (hash VARCHAR(255) NOT 
NULL, PRIMARY KEY(hash)) ENGINE = InnoDB"

in TYPO3.Flow "Version20110613223837".

This normally isn't a problem unless the charset is utf8mb4 (available 
in newer versions) which reserves 4 bytes per character (utf8 uses 3).
So with default utf8 you are at 765 bytes just below the limit but with 
4 bytes you are far above the limit. Interestingly this happened on a 
default Debian Wheezy and variables say the charset is utf8 not utf8mb4 
so I cannot explain myself why it happend. I am investigating this now. 
Setting [2] helped in that case but I guess we should think about those 
scenarios as well.
See also some of the discussion in here [3] (warning some of the info in 
there is not really accurate).

So to make a long story short, I would like to remove all notions of the 
path_workspace index as a bugfix as this is really breaking in case 
MySQL doesn't silently truncate the index anymore. Additionally we 
should think about the other indexes as well separately.

Cheers,
Christian


[1] http://bugs.mysql.com/bug.php?id=68453
[2] 
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
[3] 
http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes