New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Doctrine generates too long index keys for username_canonical & email_canonical columns #1919

Closed
rainulf opened this Issue Aug 11, 2015 · 16 comments

Comments

Projects
None yet
@rainulf

rainulf commented Aug 11, 2015

As of 1.3, it roughly generates the following CREATE TABLE query for mysql:

CREATE TABLE fos_user (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL,
....
....
  username VARCHAR (255) NOT NULL,
  username_canonical VARCHAR (255) NOT NULL,
  email VARCHAR (255) NOT NULL,
  email_canonical VARCHAR (255) NOT NULL,
....
....
  UNIQUE INDEX UNIQ_957A647992FC23A8 (username_canonical),
  UNIQUE INDEX UNIQ_957A6479A0D96FBF (email_canonical),
....
....
)

Which causes the following issue:

Error Code: 1071
Specified key was too long; max key length is 767 bytes

We workaround this by manually updating the UNIQUE INDEX and add their lengths:

....
  UNIQUE INDEX UNIQ_957A647992FC23A8 (username_canonical(20)),
  UNIQUE INDEX UNIQ_957A6479A0D96FBF (email_canonical(20)),
....

@rainulf rainulf changed the title from Doctrine schema CREATE TABLE generates too long index keys for username_canonical & email_canonical columns to Doctrine generates too long index keys for username_canonical & email_canonical columns Aug 11, 2015

@sstok

This comment has been minimized.

Show comment
Hide comment
@sstok

sstok Aug 12, 2015

I can think of one way to fix this, but its a BC break. Instead of storing the actual value you store the sha1 hash (40 bytes) of the original value instead.

sstok commented Aug 12, 2015

I can think of one way to fix this, but its a BC break. Instead of storing the actual value you store the sha1 hash (40 bytes) of the original value instead.

@davidbehler

This comment has been minimized.

Show comment
Hide comment
@davidbehler

davidbehler Dec 16, 2015

The cause of this is that Symfony advises you to use utf8mb4_general_ci/utf8mb4 as collation/charset for your database. utf8mb4 takes 4 bytes per char, meaning a 255 char field needs 1020 bytes for an index (that covers the whole length). InnoDB has a max key length of 767 bytes, so that leaves us with a couple of options:

  • Decrease the field length to 191 which should still be plenty enough for most cases I guess
  • Switch to MyISAM instead of InnoDB. MyISAM's max key length is 1000, meaning we'd would still have to reduce the field length but only to 250 instead of 191 BUT MyISAM does not support foreign keys...which is a huge bummer and not an option for me
  • Reduce the length of the index to 191 (while keeping the column length at 191). Looks like a good solution to me, but I don't know how to do this using the XML declarations/annotations. @rainulf sets length of the key to 20, which means that only the first 20 chars will be checked for uniqueness, which might lead to issues with values that only start to differ at char 21. This is still a huge amount of chars, but might be an issue. So I'd use the 191 length instead.
  • Use @sstok approach which would solve the issue as well, but would break backward-compatibility

davidbehler commented Dec 16, 2015

The cause of this is that Symfony advises you to use utf8mb4_general_ci/utf8mb4 as collation/charset for your database. utf8mb4 takes 4 bytes per char, meaning a 255 char field needs 1020 bytes for an index (that covers the whole length). InnoDB has a max key length of 767 bytes, so that leaves us with a couple of options:

  • Decrease the field length to 191 which should still be plenty enough for most cases I guess
  • Switch to MyISAM instead of InnoDB. MyISAM's max key length is 1000, meaning we'd would still have to reduce the field length but only to 250 instead of 191 BUT MyISAM does not support foreign keys...which is a huge bummer and not an option for me
  • Reduce the length of the index to 191 (while keeping the column length at 191). Looks like a good solution to me, but I don't know how to do this using the XML declarations/annotations. @rainulf sets length of the key to 20, which means that only the first 20 chars will be checked for uniqueness, which might lead to issues with values that only start to differ at char 21. This is still a huge amount of chars, but might be an issue. So I'd use the 191 length instead.
  • Use @sstok approach which would solve the issue as well, but would break backward-compatibility
@HugoHeneault

This comment has been minimized.

Show comment
Hide comment
@HugoHeneault

HugoHeneault Mar 30, 2016

I can't figure out a way to override the FOSUserBundle/.../User.orm.xml

I've added a app/Resources/FOSUserBundle/config/doctrine-mapping/User.orm.xml with a field length of 191, but it still add 255 length field...

What is the proper way to change it?

Thanks!

HugoHeneault commented Mar 30, 2016

I can't figure out a way to override the FOSUserBundle/.../User.orm.xml

I've added a app/Resources/FOSUserBundle/config/doctrine-mapping/User.orm.xml with a field length of 191, but it still add 255 length field...

What is the proper way to change it?

Thanks!

@mvrhov

This comment has been minimized.

Show comment
Hide comment
@mvrhov

mvrhov Mar 31, 2016

You create custom mapping for the whole bundle. And don't auto register the default one.

mvrhov commented Mar 31, 2016

You create custom mapping for the whole bundle. And don't auto register the default one.

@HugoHeneault

This comment has been minimized.

Show comment
Hide comment
@HugoHeneault

HugoHeneault Mar 31, 2016

Thanks for your reply.

Can you provide an example of the custom mapping for the whole bundle? I see how to make it for the entity but not the bundle. :(

HugoHeneault commented Mar 31, 2016

Thanks for your reply.

Can you provide an example of the custom mapping for the whole bundle? I see how to make it for the entity but not the bundle. :(

@umstek

This comment has been minimized.

Show comment
Hide comment
@umstek

umstek Apr 10, 2016

Hi,
I'm having the same problem. My software is still in development stage and I need to know whether stepping-down to utf8 will be okay until this issue is fixed.

Thanks.

umstek commented Apr 10, 2016

Hi,
I'm having the same problem. My software is still in development stage and I need to know whether stepping-down to utf8 will be okay until this issue is fixed.

Thanks.

@ParisLiakos

This comment has been minimized.

Show comment
Hide comment
@ParisLiakos

ParisLiakos May 9, 2016

This annotation seems to work on the User object

/**
 * @ORM\Entity
 * @ORM\Table(name="fos_user")
 * @ORM\AttributeOverrides({
 *      @ORM\AttributeOverride(name="usernameCanonical",
 *          column=@ORM\Column(
 *              name     = "username_canonical",
 *              length   = 191,
 *              unique   = true
 *          )
 *      ),
 *      @ORM\AttributeOverride(name="emailCanonical",
 *          column=@ORM\Column(
 *              name     = "email_canonical",
 *              length   = 191,
 *              unique   = true
 *          )
 *      )
 * })
 */
class User extends BaseUser
{
//...
}

ParisLiakos commented May 9, 2016

This annotation seems to work on the User object

/**
 * @ORM\Entity
 * @ORM\Table(name="fos_user")
 * @ORM\AttributeOverrides({
 *      @ORM\AttributeOverride(name="usernameCanonical",
 *          column=@ORM\Column(
 *              name     = "username_canonical",
 *              length   = 191,
 *              unique   = true
 *          )
 *      ),
 *      @ORM\AttributeOverride(name="emailCanonical",
 *          column=@ORM\Column(
 *              name     = "email_canonical",
 *              length   = 191,
 *              unique   = true
 *          )
 *      )
 * })
 */
class User extends BaseUser
{
//...
}
@violuke

This comment has been minimized.

Show comment
Hide comment
@violuke

violuke May 25, 2016

Thanks @ParisLiakos this solved my problem.

violuke commented May 25, 2016

Thanks @ParisLiakos this solved my problem.

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Jun 8, 2016

I had this on a shared host allowing only MyISAM, after not having any problem in my development env using innodb.

ghost commented Jun 8, 2016

I had this on a shared host allowing only MyISAM, after not having any problem in my development env using innodb.

@violuke

This comment has been minimized.

Show comment
Hide comment
@violuke

violuke Jun 8, 2016

@EzekielYovel I don't think MyISAM vs innodb makes any difference here.

violuke commented Jun 8, 2016

@EzekielYovel I don't think MyISAM vs innodb makes any difference here.

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Jun 8, 2016

I may be wrong, but as soon as I added 'options={"engine": "MyISAM"}' to my User class I had the same error message in my development env, so I think myisam vs innodb does make a difference.

ghost commented Jun 8, 2016

I may be wrong, but as soon as I added 'options={"engine": "MyISAM"}' to my User class I had the same error message in my development env, so I think myisam vs innodb does make a difference.

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Jun 8, 2016

For clarity, here is what I did. First I've added the 'options={"engine": "MyISAM"}' to my user class,
then I've executed this script:

#!/bin/bash
php bin/console doctrine:database:drop --force
php bin/console doctrine:database:create
php bin/console doctrine:schema:update --force

here's the output:

Dropped database for connection named `todoatdb`
Created database `todoatdb` for connection named default
Updating database schema...


  [Doctrine\DBAL\Exception\DriverException]                                    
  An exception occurred while executing 'CREATE TABLE fos_user (id INT AUTO_I  
  NCREMENT NOT NULL, username VARCHAR(255) NOT NULL, username_canonical VARCH  
  AR(255) NOT NULL, email VARCHAR(255) NOT NULL, email_canonical VARCHAR(255)  
   NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) NOT NULL, passwor  
  d VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked TINYINT(1  
  ) NOT NULL, expired TINYINT(1) NOT NULL, expires_at DATETIME DEFAULT NULL,   
  confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIM  
  E DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT '(DC2Type:array)', credenti  
  als_expired TINYINT(1) NOT NULL, credentials_expire_at DATETIME DEFAULT NUL  
  L, UNIQUE INDEX UNIQ_957A647992FC23A8 (username_canonical), UNIQUE INDEX UN  
  IQ_957A6479A0D96FBF (email_canonical), PRIMARY KEY(id)) DEFAULT CHARACTER S  
  ET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = MyISAM':                      
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t  
  oo long; max key length is 1000 bytes                                        



  [Doctrine\DBAL\Driver\PDOException]                                          
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t  
  oo long; max key length is 1000 bytes                                        



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


doctrine:schema:update [--complete] [--dump-sql] [-f|--force] [--em [EM]] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

ghost commented Jun 8, 2016

For clarity, here is what I did. First I've added the 'options={"engine": "MyISAM"}' to my user class,
then I've executed this script:

#!/bin/bash
php bin/console doctrine:database:drop --force
php bin/console doctrine:database:create
php bin/console doctrine:schema:update --force

here's the output:

Dropped database for connection named `todoatdb`
Created database `todoatdb` for connection named default
Updating database schema...


  [Doctrine\DBAL\Exception\DriverException]                                    
  An exception occurred while executing 'CREATE TABLE fos_user (id INT AUTO_I  
  NCREMENT NOT NULL, username VARCHAR(255) NOT NULL, username_canonical VARCH  
  AR(255) NOT NULL, email VARCHAR(255) NOT NULL, email_canonical VARCHAR(255)  
   NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) NOT NULL, passwor  
  d VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked TINYINT(1  
  ) NOT NULL, expired TINYINT(1) NOT NULL, expires_at DATETIME DEFAULT NULL,   
  confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIM  
  E DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT '(DC2Type:array)', credenti  
  als_expired TINYINT(1) NOT NULL, credentials_expire_at DATETIME DEFAULT NUL  
  L, UNIQUE INDEX UNIQ_957A647992FC23A8 (username_canonical), UNIQUE INDEX UN  
  IQ_957A6479A0D96FBF (email_canonical), PRIMARY KEY(id)) DEFAULT CHARACTER S  
  ET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = MyISAM':                      
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t  
  oo long; max key length is 1000 bytes                                        



  [Doctrine\DBAL\Driver\PDOException]                                          
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t  
  oo long; max key length is 1000 bytes                                        



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


doctrine:schema:update [--complete] [--dump-sql] [-f|--force] [--em [EM]] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>
@violuke

This comment has been minimized.

Show comment
Hide comment
@violuke

violuke Jun 8, 2016

I stand corrected, the max length varies between engines. Thanks, for your explanation though. http://stackoverflow.com/a/3489331

violuke commented Jun 8, 2016

I stand corrected, the max length varies between engines. Thanks, for your explanation though. http://stackoverflow.com/a/3489331

@ghostal

This comment has been minimized.

Show comment
Hide comment
@ghostal

ghostal Jun 10, 2016

The annotations suggested by @ParisLiakos solve the problem, but be aware it may cause you to run into what seems to be a Doctrine bug, when using doctrine:generate:entitites:

$ console doctrine:generate:entities AppBundle
Generating entities for bundle "AppBundle"

  [Doctrine\ORM\Mapping\MappingException]
  Invalid field override named 'emailCanonical' for class 'AppBundle\Entity\User'.

Seems like it's related to this bug, and various people are having a similar issue with it.

ghostal commented Jun 10, 2016

The annotations suggested by @ParisLiakos solve the problem, but be aware it may cause you to run into what seems to be a Doctrine bug, when using doctrine:generate:entitites:

$ console doctrine:generate:entities AppBundle
Generating entities for bundle "AppBundle"

  [Doctrine\ORM\Mapping\MappingException]
  Invalid field override named 'emailCanonical' for class 'AppBundle\Entity\User'.

Seems like it's related to this bug, and various people are having a similar issue with it.

@Bladefidz

This comment has been minimized.

Show comment
Hide comment
@Bladefidz

Bladefidz Jun 20, 2016

Just follow @umstek suggestion, and it's seems solve the problem. Since utf8 required 3 byte to encode each char, have varchar(255) as unique index (3 * 255) not reach the innodb limit (767).

CREATE TABLEuser(id INT UNSIGNED AUTO_INCREMENT NOT NULL, username VARCHAR(255) NOT NULL, username_canonical VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, email_canonical VARCHAR(255) NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked TINYINT(1) NOT NULL, expired TINYINT(1) NOT NULL, expires_at DATETIME DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT '(DC2Type:array)', credentials_expired TINYINT(1) NOT NULL, credentials_expire_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_8D93D64992FC23A8 (username_canonical), UNIQUE INDEX UNIQ_8D93D649A0D96FBF (email_canonical), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = InnoDB

But, if user typing accent is your concern, then utf8mb4 is more reliable choice. So, just decrease the username_connocial and email_connocial length or use subset index length. Good read: https://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql

Bladefidz commented Jun 20, 2016

Just follow @umstek suggestion, and it's seems solve the problem. Since utf8 required 3 byte to encode each char, have varchar(255) as unique index (3 * 255) not reach the innodb limit (767).

CREATE TABLEuser(id INT UNSIGNED AUTO_INCREMENT NOT NULL, username VARCHAR(255) NOT NULL, username_canonical VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, email_canonical VARCHAR(255) NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked TINYINT(1) NOT NULL, expired TINYINT(1) NOT NULL, expires_at DATETIME DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT '(DC2Type:array)', credentials_expired TINYINT(1) NOT NULL, credentials_expire_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_8D93D64992FC23A8 (username_canonical), UNIQUE INDEX UNIQ_8D93D649A0D96FBF (email_canonical), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = InnoDB

But, if user typing accent is your concern, then utf8mb4 is more reliable choice. So, just decrease the username_connocial and email_connocial length or use subset index length. Good read: https://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql

@afilina afilina referenced this issue Sep 18, 2016

Merged

Fixes #1919 #2226

@lysender

This comment has been minimized.

Show comment
Hide comment
@lysender

lysender Sep 26, 2016

I'm not sure if there is a related issue, but confirmation_token "varchar(255)" also has this index length issue.

I've added override:

 * @ORM\AttributeOverrides({
 *      @ORM\AttributeOverride(name="confirmationToken",
 *          column=@ORM\Column(
 *              name     = "confirmation_token",
 *              length   = 191,
 *              unique   = true
 *          )
 *      )
 * })

but it seems too short (191 vs 255). Haven't actually registered a user so I can't confirm.

lysender commented Sep 26, 2016

I'm not sure if there is a related issue, but confirmation_token "varchar(255)" also has this index length issue.

I've added override:

 * @ORM\AttributeOverrides({
 *      @ORM\AttributeOverride(name="confirmationToken",
 *          column=@ORM\Column(
 *              name     = "confirmation_token",
 *              length   = 191,
 *              unique   = true
 *          )
 *      )
 * })

but it seems too short (191 vs 255). Haven't actually registered a user so I can't confirm.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment