ext_translations table giving hard time.

[bogdaniel]

I'm using a simple docker setup generated using phpdocker.io with the latest symfony version nothing too fancy.

Give the following migration i get an error even if i set mysql to run by default using utf8mb4 charset.

        $this->addSql('CREATE TABLE ext_translations (id INT AUTO_INCREMENT NOT NULL, locale VARCHAR(8) NOT NULL, object_class VARCHAR(255) NOT NULL, field VARCHAR(32) NOT NULL, foreign_key VARCHAR(64) NOT NULL, content LONGTEXT DEFAULT NULL, INDEX translations_lookup_idx (locale, object_class, foreign_key), UNIQUE INDEX lookup_unique_idx (locale, object_class, field, foreign_key), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC');
        $this->addSql('CREATE TABLE ext_log_entries (id INT AUTO_INCREMENT NOT NULL, action VARCHAR(8) NOT NULL, logged_at DATETIME NOT NULL, object_id VARCHAR(64) DEFAULT NULL, object_class VARCHAR(255) NOT NULL, version INT NOT NULL, data LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', username VARCHAR(255) DEFAULT NULL, INDEX log_class_lookup_idx (object_class), INDEX log_date_lookup_idx (logged_at), INDEX log_user_lookup_idx (username), INDEX log_version_lookup_idx (object_id, object_class, version), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC');

The error:

Migration 20180824174638 failed during Execution. Error An exception occurred while executing 'CREATE TABLE ext_translations (id INT AUTO_INCREMENT NOT NULL, locale VARCHAR(8) NOT NULL, object_class VARCHAR(255) NOT NULL, field VARCHAR(32) NOT NULL, foreign_key VARCHAR(64) NOT NULL, content LONGTEXT DEFAULT NULL, INDEX translations_lookup_idx (locale, object_class, foreign_key), UNIQUE INDEX lookup_unique_idx (locale, object_class, field, foreign_key), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC':

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

Any advice on how to fix this problem?

[andrewtch]

It's a prefix limitation specific to 5.6. For 5.7 and up, the prefix limit has beed raised to ~3000+ symbols afaik.

[bogdaniel]

For the moment i run the server with different servers
command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci', '--innodb-file-format=Barracuda', '--innodb-file-per-table=ON', '--innodb-large-prefix=1']

but i was wondering if there are any other solutions. :-)

[andrewtch]

What version of mysql did yo uspecify when generating the zipfile from phpdocker?

[bogdaniel]

mariadb:
  image: mariadb:10.1
  container_name: mariadb
  working_dir: /application
  volumes:
    - .:/application
  environment:
    - MYSQL_ROOT_PASSWORD=#
    - MYSQL_DATABASE=#
    - MYSQL_USER=#
    - MYSQL_PASSWORD=#
  ports:
    - "1033:3306"

[oscarnevarezleal]

I´m interested to know if someone else already resolved this.
Is there a way to selectively change the charset & collation for this bundle?

[grimmlink]

Hi,
Having this problem too. Problem is $objectClass field, length is 255 and for utf8mb4 collation limit should be 191 on mysql <= 5.6

[AkenRoberts]

Fixed in master for eventual 3.0 release. Thank you!