security-acl Problems with some ACL tables and utf8mb4 encoding

see symfony/symfony#14560 for the previous discussion

I'm in the process of converting our database from 'utf8' to 'utf8mb4' to support 4-byte unicode chars (emojis for example) and running the conversion queries a problem shows up with the length of some unique keys in the ACL schema which I can't control myself (well, of course I can but then in every migration I make afterwards these will come up and try to revert any ALTER TABLE I make to these tables)

mysql> ALTER TABLE acl_security_identities CONVERT TO CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

By default InnoDB can have a maximum index size of 767 bytes. With utf8 encoding, which uses at most 3 bytes per char, you get 255 characters. But in utf8mb4 you now can only index string columns with at most 191 characters. This InnoDB setting can be changed but you have to change all the database and table file formats (see http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/).

So my question would be if the default string lengths of 200 chars that the acl_security_identities and acl_classes unique keys have could be tuned to 191 chars, it's only 9 chars less, I guess it would not be a big problem.

Thanks!

Oct 05 '15 10:10 acasademont

:+1: Getting this error as well. Downgrading temporarily back to utf8 for a quick fix/restore.

Jan 04 '16 06:01 The-Don-Himself

Me too. Need to set a limit to the index length for mysql since utf8mb4 200 character string is 800 bytes and max index is 767 bytes.

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'CREATE TABLE acl_classes (id INT UNSIGNED AUTO_INCREMENT NOT NULL, class_type VARCHAR(200) NOT NULL, UNIQUE INDEX UNIQ_69DD750638A36066 (class_type), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB':
Specified key was too long; max key length is 767 bytes

[Doctrine\DBAL\Driver\Mysqli\MysqliException]
Specified key was too long; max key length is 767 bytes

Fix is in /vendor/symfony/security-acl/Dbal/Schema.php line 70 $table->addColumn('class_type', 'string', array('length' => 200)); change to $table->addColumn('class_type', 'string', array('length' => 190));

Or use utf8 until it is fixed.

May 24 '16 09:05 oisvidi

Another solution which works for me, ensure :

mysql >= 5.6
innodb_file_format is BARRACUDA
innodb_large_prefix is ON

which gives in my.cnf :

innodb_large_prefix = 1
innodb_file_format = BARRACUDA

in config.yml, add dynamic (or compress) row format to dbal default_table_options:

doctrine:
    dbal:
        driver:   pdo_mysql
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci
            row_format: DYNAMIC