Fosuserbundle: Doctrine generates too long index keys for username_canonical & email_canonical columns

[rainulf]

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)),
....

[sstok]

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]

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]

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]

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

[HugoHeneault]

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]

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 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]

Thanks @ParisLiakos this solved my problem.

[ghost]

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

[violuke]

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

[ghost]

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]

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]

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

[ghostal]

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]

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

[lysender]

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.

[Thalad]

update your MariaDB at least > 10.2 to update length=255

[Paulo1661]

I just changed the length=255, to length=191 and it was ok for me