Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.Problem/Motivation
Over in #1314214: MySQL driver does not support full UTF-8 (emojis, asian symbols, mathematical symbols), indexes that were going to exceed the maximum length allowed were automatically truncated. This change was only applied to normal indexes though, and not unique or primary keys.
Proposed resolution
Shorten primary and unique keys as needed.
Remaining tasks
User interface changes
API changes
Data model changes
| Comment | File | Size | Author |
|---|---|---|---|
| #1 | shorten-all-indexes-2522098-01.patch | 1.92 KB | jhedstrom |
Comments
Comment #1
jhedstromFirst attempt. The code could probably be better refactored so this is more elegant.
Comment #2
morgantocker CreditAttribution: morgantocker commentedBe careful with this. Shortening a UNIQUE index means that only part of the constraint is applied (from left to right). This might be OK for most cases, but input like URLs contain uniqueness very late in the string.
Comment #3
stefan.r CreditAttribution: stefan.r commentedThis was actually as designed. We can't just shorten a primary key / unique index at runtime without changing behavior (strings that start with the same 191 characters will give an error) whereas for a regular index there is no real impact whether it's 191 characters long or 255 characters long.
We created a change record that explicitly states this: https://www.drupal.org/node/2510940
So if we we were to change anything, wouldn't it have to be the length of the field and not that of the primary key/unique index?
And in such case it'd be confusing to have 255 in the schema but 191 in the database, so I think this should just keep throwing an error on table creation, but maybe we can catch the "
Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes" error and gives a few more pointers instead or link to the change record?Comment #4
stefan.r CreditAttribution: stefan.r commentedComment #5
jhedstromre #2 good catch. This is a first attempt to fix issues over in the Head 2 Head module for testing updates, not sure if it's the best idea or not.
#2514860: Add db dumps of each beta for testing purposes
Comment #6
jhedstromCross post on that. #3 makes total sense as well, and since this isn't necessarily something people will run into, this might be won't fix, or go with the try/catch for better DX.
Comment #7
jhedstromI added #2522120: DbDumpCommand should add collation information to the generated script as a better fix for the more immediate issue with testing update paths.
Comment #8
morgantocker CreditAttribution: morgantocker commentedFor MySQL version context (also mentioned in the other related issues):
The 767 bytes is a limitation in the _default_ configuration of MySQL. From 5.5+ it is possible to have a 3000 byte limit, but it requires changing settings Drupal can not expect users can change (innodb-file-format=barracuda, innodb-large-prefix=1).
The defaults for both of these settings change in MySQL 5.7, so any script to truncate indexes should not do this to an install of 5.7+ because this would offer an unneeded functionality regression.
Comment #21
catchClosing this as 'works as designed'.