ddev: "Index column size too large" MySQL error when importing TYPO3 DB
What happened (or feature request):
When importing db dump of the TYPO3 9 I’ve got mysql error:
“Index column size too large. The maximum column size is 767 bytes.” An example failing query is:
CREATE TABLE sys_file_processedfile (
uid int(11) NOT NULL auto_increment,
tstamp int(11) NOT NULL default '0',
crdate int(11) NOT NULL default '0',
storage int(11) NOT NULL default '0',
original int(11) NOT NULL default '0',
identifier varchar(512) NOT NULL default '',
name tinytext,
configuration text,
configurationsha1 char(40) NOT NULL default '',
originalfilesha1 char(40) NOT NULL default '',
task_type varchar(200) NOT NULL default '',
checksum char(10) NOT NULL default '',
width int(11) default '0',
height int(11) default '0',
PRIMARY KEY (uid),
KEY combined_1 (original,task_type,configurationsha1),
KEY identifier (storage,identifier(199))
);
Error: Index column size too large. The maximum column size is 767 bytes.
The same error was reported by user trying to install fresh TYPO3 v7.6. However he reported that installation of the fresh 8.7 or 9 went through (probably because starting from 8.7 TYPO3 adds default charset when creating tables).
How to reproduce this:
log in to ddev mysql, and try to run query from above.
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Comments: 24 (22 by maintainers)
Commits related to this issue
- Remove utf8mb4 config for databases that can't handle it, DO NO PULL. For drud/ddev#654 — committed to rfay/mariadb-local by rfay 6 years ago
I had the same problem, my dirty workaround is to change it to utf8general_ci with the phpmyadmin. but it would be nice to have a better solution within the config
cross linking documentation - how to apply custom mysql config for ddev https://ddev.readthedocs.io/en/latest/users/extend/customization-extendibility/#providing-custom-mysqlmariadb-configuration-mycnf
A little more background to the issue after investigating it.
utf8mb4_bin; utf8mb4 uses 4 bytes per char, while “standard” utf8 collation uses 3 bytes per char.DEFAULT CHARSET=utf8at the end, mysql will try to create table using default db collation which is utf8mb4.KEY identifier (storage,identifier(199)). Doing little math 199 chars *4 bytes = 796 bytes, so already over the 767 limit.This topic isnt that actual anymore, but i can confirm upgrading from 10.1 to 10.2 completely solved my problem in regards to that error. I’ve been upgrading it according to Plesk documentation and then did a manual upgrade of all existing databaseses. luckily there were no issues at all and now i got rid of that issues which kept me off in migrating and consolidating websites from 2 servers into 1. thanks and cheers!
I wonder if a short-term solution to this is to add a post-start hook that does an alter on the database to set the correct charset?
i.e.
ALTER DATABASE db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;– Source https://mathiasbynens.be/notes/mysql-utf8mb4