Installation without innodb_large_prefix fails

[fritzmg]

Contao 4.5.5 introduced the possibility to install Contao on Database servers without the innodb_large_prefix (etc.) options enabled, without having to do any configuration changes.

However, this does not seem to work. Reproduction on my local system:

1. Disable innodb_large_prefix = ON, innodb_file_format = Barracuda, innodb_file_per_table = ON.
2. Run composer create-project contao/managed-edition … 4.5.5.
3. Run the Install Tool and set a fresh database.
4. Create all tables.

The following error occurs on my system:

Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing 'CREATE TABLE tl_newsletter_recipients (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, email VARCHAR(255) DEFAULT '' NOT NULL, active CHAR(1) DEFAULT '' NOT NULL, addedOn VARCHAR(10) DEFAULT '' NOT NULL, confirmed VARCHAR(10) DEFAULT '' NOT NULL, ip VARCHAR(64) DEFAULT '' NOT NULL, token VARCHAR(32) DEFAULT '' NOT NULL, UNIQUE INDEX pid_email (pid, email), INDEX email (email), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC':

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

  at vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractMySQLDriver.php:121
  at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception occurred while executing \'CREATE TABLE tl_newsletter_recipients (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, email VARCHAR(255) DEFAULT \'\' NOT NULL, active CHAR(1) DEFAULT \'\' NOT NULL, addedOn VARCHAR(10) DEFAULT \'\' NOT NULL, confirmed VARCHAR(10) DEFAULT \'\' NOT NULL, ip VARCHAR(64) DEFAULT \'\' NOT NULL, token VARCHAR(32) DEFAULT \'\' NOT NULL, UNIQUE INDEX pid_email (pid, email), INDEX email (email), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC\':SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes', object(PDOException))
     (vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php:176)
  at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(PDOException), 'An exception occurred while executing \'CREATE TABLE tl_newsletter_recipients (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, email VARCHAR(255) DEFAULT \'\' NOT NULL, active CHAR(1) DEFAULT \'\' NOT NULL, addedOn VARCHAR(10) DEFAULT \'\' NOT NULL, confirmed VARCHAR(10) DEFAULT \'\' NOT NULL, ip VARCHAR(64) DEFAULT \'\' NOT NULL, token VARCHAR(32) DEFAULT \'\' NOT NULL, UNIQUE INDEX pid_email (pid, email), INDEX email (email), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC\':SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes')
     (vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php:150)
  at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'CREATE TABLE tl_newsletter_recipients (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, email VARCHAR(255) DEFAULT \'\' NOT NULL, active CHAR(1) DEFAULT \'\' NOT NULL, addedOn VARCHAR(10) DEFAULT \'\' NOT NULL, confirmed VARCHAR(10) DEFAULT \'\' NOT NULL, ip VARCHAR(64) DEFAULT \'\' NOT NULL, token VARCHAR(32) DEFAULT \'\' NOT NULL, UNIQUE INDEX pid_email (pid, email), INDEX email (email), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC')
     (vendor\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:1014)
  at Doctrine\DBAL\Connection->query('CREATE TABLE tl_newsletter_recipients (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, email VARCHAR(255) DEFAULT \'\' NOT NULL, active CHAR(1) DEFAULT \'\' NOT NULL, addedOn VARCHAR(10) DEFAULT \'\' NOT NULL, confirmed VARCHAR(10) DEFAULT \'\' NOT NULL, ip VARCHAR(64) DEFAULT \'\' NOT NULL, token VARCHAR(32) DEFAULT \'\' NOT NULL, UNIQUE INDEX pid_email (pid, email), INDEX email (email), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC')
     (vendor\contao\installation-bundle\src\Database\Installer.php:76)
  at Contao\InstallationBundle\Database\Installer->execCommand('4d5c78f2326a90e27705c1ba89b52371')
     (vendor\contao\installation-bundle\src\Controller\InstallationController.php:448)
  at Contao\InstallationBundle\Controller\InstallationController->adjustDatabaseTables()
     (vendor\contao\installation-bundle\src\Controller\InstallationController.php:101)
  at Contao\InstallationBundle\Controller\InstallationController->installAction()
     (vendor\symfony\symfony\src\Symfony\Component\HttpKernel\HttpKernel.php:151)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor\symfony\symfony\src\Symfony\Component\HttpKernel\HttpKernel.php:68)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor\symfony\symfony\src\Symfony\Component\HttpKernel\Kernel.php:202)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (web\app_dev.php:65)

Server environment:

• Apache 2.4.25
• PHP 7.1.4
• MariaDB 10.1.22

A similar error was reported on the community forums: https://community.contao.org/de/showthread.php?70063-Fehler-bei-der-Installation

Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing 'CREATE TABLE tl_files (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid BINARY(16) DEFAULT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, uuid BINARY(16) DEFAULT NULL, type VARCHAR(16) DEFAULT '' NOT NULL, path VARCHAR(1022) DEFAULT '' NOT NULL, extension VARCHAR(16) DEFAULT '' NOT NULL, hash VARCHAR(32) DEFAULT '' NOT NULL, found CHAR(1) DEFAULT '1' NOT NULL, name VARCHAR(255) DEFAULT '' NOT NULL, importantPartX INT DEFAULT 0 NOT NULL, importantPartY INT DEFAULT 0 NOT NULL, importantPartWidth INT DEFAULT 0 NOT NULL, importantPartHeight INT DEFAULT 0 NOT NULL, meta BLOB DEFAULT NULL, INDEX pid (pid), UNIQUE INDEX uuid (uuid), INDEX path (path), INDEX extension (extension), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC':

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

  at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:121
  at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception occurred while executing \'CREATE TABLE tl_files (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid BINARY(16) DEFAULT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, uuid BINARY(16) DEFAULT NULL, type VARCHAR(16) DEFAULT \'\' NOT NULL, path VARCHAR(1022) DEFAULT \'\' NOT NULL, extension VARCHAR(16) DEFAULT \'\' NOT NULL, hash VARCHAR(32) DEFAULT \'\' NOT NULL, found CHAR(1) DEFAULT \'1\' NOT NULL, name VARCHAR(255) DEFAULT \'\' NOT NULL, importantPartX INT DEFAULT 0 NOT NULL, importantPartY INT DEFAULT 0 NOT NULL, importantPartWidth INT DEFAULT 0 NOT NULL, importantPartHeight INT DEFAULT 0 NOT NULL, meta BLOB DEFAULT NULL, INDEX pid (pid), UNIQUE INDEX uuid (uuid), INDEX path (path), INDEX extension (extension), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC\':SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes', object(PDOException))
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:176)
  at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(PDOException), 'An exception occurred while executing \'CREATE TABLE tl_files (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid BINARY(16) DEFAULT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, uuid BINARY(16) DEFAULT NULL, type VARCHAR(16) DEFAULT \'\' NOT NULL, path VARCHAR(1022) DEFAULT \'\' NOT NULL, extension VARCHAR(16) DEFAULT \'\' NOT NULL, hash VARCHAR(32) DEFAULT \'\' NOT NULL, found CHAR(1) DEFAULT \'1\' NOT NULL, name VARCHAR(255) DEFAULT \'\' NOT NULL, importantPartX INT DEFAULT 0 NOT NULL, importantPartY INT DEFAULT 0 NOT NULL, importantPartWidth INT DEFAULT 0 NOT NULL, importantPartHeight INT DEFAULT 0 NOT NULL, meta BLOB DEFAULT NULL, INDEX pid (pid), UNIQUE INDEX uuid (uuid), INDEX path (path), INDEX extension (extension), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC\':SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes')
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:150)
  at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'CREATE TABLE tl_files (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid BINARY(16) DEFAULT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, uuid BINARY(16) DEFAULT NULL, type VARCHAR(16) DEFAULT \'\' NOT NULL, path VARCHAR(1022) DEFAULT \'\' NOT NULL, extension VARCHAR(16) DEFAULT \'\' NOT NULL, hash VARCHAR(32) DEFAULT \'\' NOT NULL, found CHAR(1) DEFAULT \'1\' NOT NULL, name VARCHAR(255) DEFAULT \'\' NOT NULL, importantPartX INT DEFAULT 0 NOT NULL, importantPartY INT DEFAULT 0 NOT NULL, importantPartWidth INT DEFAULT 0 NOT NULL, importantPartHeight INT DEFAULT 0 NOT NULL, meta BLOB DEFAULT NULL, INDEX pid (pid), UNIQUE INDEX uuid (uuid), INDEX path (path), INDEX extension (extension), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC')
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1014)
  at Doctrine\DBAL\Connection->query('CREATE TABLE tl_files (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid BINARY(16) DEFAULT NULL, tstamp INT UNSIGNED DEFAULT 0 NOT NULL, uuid BINARY(16) DEFAULT NULL, type VARCHAR(16) DEFAULT \'\' NOT NULL, path VARCHAR(1022) DEFAULT \'\' NOT NULL, extension VARCHAR(16) DEFAULT \'\' NOT NULL, hash VARCHAR(32) DEFAULT \'\' NOT NULL, found CHAR(1) DEFAULT \'1\' NOT NULL, name VARCHAR(255) DEFAULT \'\' NOT NULL, importantPartX INT DEFAULT 0 NOT NULL, importantPartY INT DEFAULT 0 NOT NULL, importantPartWidth INT DEFAULT 0 NOT NULL, importantPartHeight INT DEFAULT 0 NOT NULL, meta BLOB DEFAULT NULL, INDEX pid (pid), UNIQUE INDEX uuid (uuid), INDEX path (path), INDEX extension (extension), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC')
     (vendor/contao/installation-bundle/src/Database/Installer.php:76)
  at Contao\InstallationBundle\Database\Installer->execCommand('f8e2ce866429d34943e8e53b8fe6d671')
     (vendor/contao/installation-bundle/src/Controller/InstallationController.php:448)
  at Contao\InstallationBundle\Controller\InstallationController->adjustDatabaseTables()
     (vendor/contao/installation-bundle/src/Controller/InstallationController.php:101)
  at Contao\InstallationBundle\Controller\InstallationController->installAction()
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:151)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:68)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php:202)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (web/app_dev.php:65)

[fritzmg]

kubjo from the Forum says that he uses MySQL 5.7.21, which supports innodb_large_prefix by default. May be his is another problem entirely - the error message, Specified key was too long; max key length is 3072 bytes also suggests that.

[leofeyer]

Fixed in contao/core-bundle@60e1b42. Please test.

[fritzmg]

Works 👍