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

Originally created by @dominikzogg on GitHub (Sep 30, 2018).

Originally assigned to: @Ocramius on GitHub.

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

Originally created by @dominikzogg on GitHub (Sep 30, 2018). Originally assigned to: @Ocramius on GitHub. ### 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

admin added the Can't Fix Invalid labels 2026-01-22 15:26:23 +01:00

admin closed this issue

admin commented

@Ocramius commented on GitHub (Sep 30, 2018):

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: https://github.com/doctrine/dbal/issues/3230

@Ocramius commented on GitHub (Sep 30, 2018): `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: https://github.com/doctrine/dbal/issues/3230

admin commented

2026-01-22 15:26:24 +01:00

@dominikzogg commented on GitHub (Oct 1, 2018):

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

@dominikzogg commented on GitHub (Oct 1, 2018): @Ocramius ok thanks, idea why this works on ORM v2.5.14?

admin commented

@Ocramius commented on GitHub (Oct 1, 2018):

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.

@Ocramius commented on GitHub (Oct 1, 2018): 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.

admin commented

@dominikzogg commented on GitHub (Oct 2, 2018):

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

@dominikzogg commented on GitHub (Oct 2, 2018): 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

admin commented

@Ocramius commented on GitHub (Oct 2, 2018):

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

@Ocramius commented on GitHub (Oct 2, 2018): As already stated, the default used to be `utf8`, and it changed for good reasons.

admin commented

@dominikzogg commented on GitHub (Oct 2, 2018):

@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

@dominikzogg commented on GitHub (Oct 2, 2018): @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

admin commented

2026-01-22 15:26:26 +01:00

@Ocramius commented on GitHub (Oct 2, 2018):

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

@Ocramius commented on GitHub (Oct 2, 2018): 2.5 won't get fixes unless it's about security issues

admin commented

@dominikzogg commented on GitHub (Oct 2, 2018):

@Ocramius fair enough, thanks for response

@dominikzogg commented on GitHub (Oct 2, 2018): @Ocramius fair enough, thanks for response

admin commented

2026-01-22 15:26:26 +01:00

@himak commented on GitHub (Jul 5, 2019):

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

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

@himak commented on GitHub (Jul 5, 2019): Change Laravel default database (utf8mb4) properties in file config/database.php to: ``` 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', ```

admin commented

@techtunix commented on GitHub (Nov 28, 2019):

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

@techtunix commented on GitHub (Nov 28, 2019): > > > 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`

admin commented

@Black0wI commented on GitHub (Dec 11, 2020):

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

@Black0wI commented on GitHub (Dec 11, 2020): 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

admin commented

@k00ni commented on GitHub (Jun 11, 2021):

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.

@k00ni commented on GitHub (Jun 11, 2021): _In case you used [Symfony/DoctrineMigrationBundle site](https://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html#configuration) 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`: ```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.

admin commented