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

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
{
//...
}

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

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.

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