key for tl_files.path is too long under utf8mb4

[fritzmg]

This is only a minor issue. When you are using UTF8MB4 as the dbCharset and then utf8mb4_general_ci as dbCollation, the key length for the path field of tl_files will be automatically reduced from 333 (defined by Contao) to 250.

This will cause the following SQL statement to show up every time in the database update section of the Install Tool and the Extension Repository:

ALTER TABLE `tl_files` DROP INDEX `path`, ADD KEY `path` (`path`(333));

since Contao thinks the index for path was not created yet - but it was, just with a different length than requested.

The maximum key length under MyISAM (which Contao uses) is 1000 bytes. When using utf8 this is fine, since each character is represented by 3 bytes, so 333 times 3 is 999. However, when using utf8mb4 each character is represented by 4 bytes, so the maximum character length for a key is 1000 divided by 4, i.e. 250.

This automatic reduction will happen without an error (@ausi mentioned to me that the "Specified key was too long" error might only happen for indexes over several columns or the primary unique index).

[leofeyer]

We could work around the issue like this:

'path' => (strncmp(Config::get('dbCollation'), 'utf8mb4_', 8) === 0 ? 'index(250)' : 'index(333)')

However, there is more to it if you really want to use utf8mb4:

ALTER TABLE `tl_member` DROP INDEX `email`, ADD KEY `email` (`email`);
ALTER TABLE `tl_user` DROP INDEX `email`, ADD KEY `email` (`email`);
ALTER TABLE `tl_version` DROP INDEX `fromTable`, ADD KEY `fromTable` (`fromTable`);
ALTER TABLE `tl_newsletter_recipients` DROP INDEX `email`, ADD KEY `email` (`email`);

Basically, the problem occurs for every indexed field with a length of varchar(255), so maybe we should define those fields as varchar(250)?

@contao/developers What do you think?

[ausi]

The maximum key length for InnoDB would be different (767) and can be increased to 3072 with innodb_large_prefix. I think it would be great if we could automatically detect the maximum length or detect if the key length was shortened by the MySQL server to know if we should show the update request to the user.

[mlwebworker]

Ich habe derzeit genau ein solches Problem bei Strato. Unter MySQL 5.6.31 werden alle Datenbanken ausschließlich als InnoDB angelegt. Ein Wechsel auf MyISAM ist nicht möglich, weil die Engine nicht verfügbar ist. siehe dazu ausführlicher Forenbeitrag

[fritzmg]

This seems more of a severe problem now. Users are reporting on the community forum that they cannot install Contao anymore on certain hosters.

Fatal error: Uncaught exception Exception with message Query error: Index column size too large. The maximum column size is 767 bytes. (CREATE TABLE `tl_files` …

// edit: ah, this was already mentioned in #8540

[mlwebworker]

Ich kann die Fehlermeldung Fatal error: Uncaught exception Exception with message Query error: Index column size too large. The maximum column size is 767 bytes. (ALTER TABLE tl_files DROP INDEX path, ADD KEY path (path(333));) thrown in system/modules/core/library/Contao/Database/Statement.php on line 295

bei Strato auch bei einer Contao-Installation 3.5.17 und Aktualisierung von Tabellen bestätigen. Ursache ist eventuell eine Änderung bei Strato, die u.a. auf mein mehrfaches Nachfragen wegen MyISAM, InnoDB und Länge des Indexes durchgeführt wurde. Habe gestern die Information von Strato bekommen, dass man etwas umgestellt hat. Ich kann in phpmyadmin sehen, dass innodb_large_prefix auf on steht. Reicht das schon aus oder gibt es noch eine Variable die explizit die Größe festlegt?

[mlwebworker]

Auf Grund eines eines Forumshinweises von tab (https://community.contao.org/de/showthread.php?63567-Contao-Installation-(-3-5-15-LTS)-unter-MySQL-5-6-31-nicht-m%F6glich/page2&p=423074&viewfull=1#post423074) habe ich in meiner Testinstallation mal die Tabelle tl-files umgestellt auf DYNAMIC, dann das Installtool aufgerufen. Ergebnis kein Fehler und Datenbank lässt sich aktualisieren. Frontend und Backend scheinen fehlerfrei zu laufen. Jetzt die Preisfrage an die Experten: Welche anderen Probleme/Inkompatibilitäten etc. können damit entstehen. Datenbanken sind nicht so ganz mein Fachgebiet.

[fritzmg]

Simple workaround for anyone trying to install Contao on Strato and generally for anyone encountering the

Fatal error: Uncaught exception Exception with message Query error: Index column size too large.

(on tl_files) error:

Create a file called dcaconfig.php in the folder /system/config of your Contao installation prior to running the Install Tool during installation of Contao. Add the following content to that file:

 <?php

$GLOBALS['TL_DCA']['tl_files']['config']['sql']['keys']['path'] = 'index(255)'; 

If the file was already created, simply add the last line, then open the Install Tool again.

[ausi]

I think we should set it to index(191) to be compatible with all configurations.

[leofeyer]

Since we are requiring MySQL 5.5.3+ in Contao 4.5 and we have switched to InnoDB in contao/core-bundle@83af27b57229ada7ac36d270f3c79c315ceb0b07 and we are requiring the innodb_large_prefix option (which is enabled by default as of MySQL 5.7.7), I have set the index to 768.

[fritzmg]

Since we are requiring MySQL 5.5.3+ in Contao 4.5 and we have switched to InnoDB in contao/core-bundle@83af27b and we are requiring the innodb_large_prefix option (which is enabled by default as of MySQL 5.7.7), I have set the index to 768.

Hm, what about MariaDB? It is not supported on WebGo with MariaDB 10.1.17 for example. Thus the installation of Contao 4.5-beta3 is not possible.

 CREATE TABLE tl_search (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, title VARCHAR(255) DEFAULT '' NOT NULL, url VARCHAR(255) DEFAULT '' NOT NULL, text MEDIUMTEXT DEFAULT NULL, filesize DOUBLE PRECISION DEFAULT '0' NOT NULL, checksum VARCHAR(32) DEFAULT '' NOT NULL, protected CHAR(1) DEFAULT '' NOT NULL, groups BLOB DEFAULT NULL, language VARCHAR(5) DEFAULT '' NOT NULL, UNIQUE INDEX url (url), UNIQUE INDEX checksum_pid (checksum, pid), FULLTEXT INDEX text (text), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC

MySQL said: Documentation
#1071 - Specified key was too long; max key length is 767 bytes

Same error on my local MariaDB 10.1.22 server.

It does work as expected with MySQL 5.7.20 on WebGo though.

[fritzmg]

According to https://stackoverflow.com/a/43424427/374996 it should work by default with MariaDB 10.2+ as well.

[fritzmg]

For anyone encountering this issue in Contao 4.5-beta3, the following settings must be enabled in your MySQL server:

innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON

These should be enabled by default with MySQL 5.7.7+ or MariaDB 10.2+.

[mlwebworker]

I think with maria DB 10.2+ there was yet another problem https://github.com/contao/core-bundle/issues/918

[fritzmg]

I think with maria DB 10.2+ there was yet another problem contao/core-bundle#918

That's not a problem, since Contao 4.5 requires PHP 7.1 and this problem does not occur with PHP 7.1 due to the updated doctrine dbal dependencies.

[mlwebworker]

Thank you.

[fmoessbauer]

IMO the issue is still not fully resolved, as the ERROR 1709 (HY000): Index column size too large. error appeared during the 4.4.11 -> 4.5.0 update. There should be at least a hint in the changelog. Setting the DB options as described by @fritzmg solves the problem.

[leofeyer]

First of all, there is a hint: https://github.com/contao/managed-edition#innodb-large-prefix

Second of all, you should never get to this point. Which of the three settings was missing on your server?

[fmoessbauer]

Ah, I was referring to the changelog on contao.org: https://contao.org/de/changelog/versions/4.5.html

For me, all settings where missing, however the install tool detected that innodb_large_prefix was not enabled. I then enabled that and the install tool showed the DB updates. However when starting the DB updates, the error appeared in the logs, and hence the tool was not able to get past the change to innoDB.

[ghost]

A new issue thereto has been created at contao/installation-bundle#80.

[jankout]

Hello, I have a problem with the installation by 1und1, now called Ionos. When I use the database from 3.5.36 and let it upgrade with the install tool it appears following error message.

[2018-12-17 15:28:40] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Schema\SchemaException: "There is no column with name 'path(191)' on table 'tl_files'." at /homepages/18/d164093797/htdocs/hanskempel/contao4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/SchemaException.php line 82 {"exception":"[object] (Doctrine\DBAL\Schema\SchemaException(code: 30): There is no column with name 'path(191)' on table 'tl_files'. at /homepages/18/d164093797/htdocs/hanskempel/contao4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/SchemaException.php:82)"} []

How can I solve it?

[ausi]

@jankout see contao/contao#238

[jankout]

@ausi thank you

[Aybee]

Ich kann hier auch gerade install nicht aufrufen.

• Contao 4.4.35 Core only Installation
• united-domains.de
• MySQL 5.7.23
• DB Kollation utf8mb4_unicode_ci
• Kollation der Tabellen utf8mb4_unicode_ci
• MyISAM Tabellen
• Fehler: max key length is 1000 bytes

[2019-02-26 20:38:47] app.CRITICAL: An exception occurred. {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing '\n            ALTER TABLE\n                tl_newsletter_recipients\n            DROP INDEX\n                pid,\n            ADD UNIQUE KEY\n                pid_email (pid, email)\n        ':\n\nSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes at /xxx/x/xxx/htdocs/contao4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:126, Doctrine\\DBAL\\Driver\\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes at /xxx/x/xxx/htdocs/contao4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:109, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes at /xxx/x/xxx/contao4/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:107)"} []

[Aybee]

Ich habe den Fehler beheben können, indem ich die Kollation der DB und Tabellen auf utf8_general_ci eingestellt habe.

[fritzmg]

Nimm lieber utf8_unicode_ci.

Sollte dich das Install Tool nicht eigentlich darauf hinweisen, dass du entweder MyISAM oder utf8 benutzen musst?

[Aybee]

Ich war so geschockt wegen des Fehlers, dass ich panisch auf Altbewertes zurückgegangen bin ;). Aber du hast Recht, hätte auch utf8_unicode_ci ausprobieren können. Danke für den Hinweis.

entweder MyISAM oder utf8

Verstehe ich nicht, ich habe doch jetzt MyISAM und utf8, wieso oder?

Nein, ein Hinweis war da nicht, ich habe beim Install den DB-Zugang eintragen müssen und dann kam obiger Fehler. Es war ein Update von 3.5. Die Tabellen wurden also nicht von Contao erstellt, sondern waren bereits vorhanden.