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

[dominikzogg]

Bug Report

Q	A
BC Break	no
Version	v2.6.2

Summary

I've got the following error SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes while try to use the orm:schema-tool:update --force command.

Current behavior

I've get an SQL error on CREATE TABLE SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

How to reproduce

Project (Skeleton App):
https://github.com/chubbyphp/petstore

Mapping:
https://github.com/chubbyphp/petstore/blob/master/app/Mapping/Orm/PetMapping.php

Works on Travis with 2.5.14
https://travis-ci.org/chubbyphp/petstore/jobs/435299083

Failed on Travis, with v2.6.2
https://travis-ci.org/chubbyphp/petstore/jobs/435299084

In AbstractMySQLDriver.php line 126:

An exception occurred while executing 'CREATE TABLE pet (id VARCHAR(255) NO
T NULL, createdAt DATETIME NOT NULL, updatedAt DATETIME DEFAULT NULL, name
VARCHAR(255) NOT NULL, tag VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFA
ULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':

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

Expected behavior

A successful table creation as in v2.5.14

[Ocramius]

utf8mb4_unicode_ci is the new default for column creation, hence you need to either reduce the size of your indexed VARCHAR columns in order to make them work correctly, since MySQL can't index them otherwise.

The limit for a VARCHAR column being indexed is 191 characters.

See also https://stackoverflow.com/questions/15157227/mysql-varchar-index-length

Closing here, as this is a MySQL specific error, and we can't change the default for VARCHAR to 191 chars due to BC compliance, while requiring users to create custom indexes is less impacting.

Related: doctrine/dbal#3230

[dominikzogg]

@Ocramius ok thanks, idea why this works on ORM v2.5.14?

[Ocramius]

Default encoding used to just be utf8, which uses 3 bytes per character, while utf8mb4 (TL;DR of the story: emojis don't really work on just utf8) uses 4 bytes per character. Index size limits in MySQL are in bytes, not characters.

[dominikzogg]

This means, that doctrine/dbal: 2.5.0, doctrine/orm: 2.5.14 makes utf8mb4 implicit to utf8 on a create table. At leas this ones seems to be a a bug to me. See: https://travis-ci.org/chubbyphp/petstore/jobs/435961586

[Ocramius]

As already stated, the default used to be utf8, and it changed for good reasons.

[dominikzogg]

@Ocramius I fully agree this SQL error is not doctrine fault, I fully agree that changing to utf8mb4 is a good choice. But i am not speaking about default, i ask both versions for utf8mb4 and the ORM made utf8 out of it. No error utf8mb4 is not supported or handle it correctly in 2.5 versions https://github.com/chubbyphp/chubbyphp-doctrine-db-service-provider/blob/master/src/ServiceProvider/DoctrineDbalServiceProvider.php#L120

[Ocramius]

2.5 won't get fixes unless it's about security issues

[dominikzogg]

@Ocramius fair enough, thanks for response

[himak]

Change Laravel default database (utf8mb4) properties in file config/database.php to:

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

[techtunix]

Change Laravel default database (utf8mb4) properties in file config/database.php to:

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

Worked in my case when run php artisan migrate --seed

[Black0wI]

Changing MariaDb config fixed this issue for me :

in my.cnf :

innodb_large_prefix=true
innodb_file_format=barracuda
innodb_default_row_format=dynamic
innodb_file_per_table=true

Restart mariadb

Hope this help

[k00ni]

In case you used Symfony/DoctrineMigrationBundle site to setup Doctrine Migrations in your Symfony application.

If you run for instance:

bin/console doctrine:migrations:sync-metadata-storage

you might see the same problem (Specific key was too long).

Reason for that could be the column length (version_column_length) promoted in the Symfony docs for config/packages/doctrine_migrations.yaml:

storage:
        # Default (SQL table) metadata storage configuration
        table_storage:
            table_name: 'doctrine_migration_versions'
            version_column_name: 'version'
            version_column_length: 1024 #                      <=========== here
            executed_at_column_name: 'executed_at'

Solution: Lower it to 192 or something else fixed the problem for me.

[mrcsmotta1]

Change the value of the "version_column_length" field in migrations.php to 192, if you're not using a yaml file

'table_storage' => [
'table_name' => 'doctrine_migration_versions',
'version_column_name' => 'version',
'version_column_length' => 192,
'executed_at_column_name' => 'executed_at',
'execution_time_column_name' => 'execution_time',
],