[Error] InnoDb not sufficient enough for storing varchar(500)

Hi there Oro folks!

I’ve tried a new setup for both an OroCRM and Oro-platform and I encountered the following error on install:

[Doctrine\DBAL\DBALException] An exception occurred while executing 'CREATE TABLE oro_translation (id INT AUTO_INCREMENT NOT NULL, `key` VARCHAR(500) NOT NULL, value LONGTEXT DEFAU LT NULL, locale VARCHAR(5) NOT NULL, domain VARCHAR(255) NOT NULL, scope SM ALLINT NOT NULL, INDEX MESSAGE_IDX (locale, domain, `key`, scope), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB ': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t oo long; max key length is 767 bytes

1 2 3 4 5 6 7 8 9 10

[Doctrine\DBAL\DBALException]   An exception occurred while executing 'CREATE TABLE oro_translation (id INT    AUTO_INCREMENT NOT NULL, `key` VARCHAR(500) NOT NULL, value LONGTEXT DEFAU   LT NULL, locale VARCHAR(5) NOT NULL, domain VARCHAR(255) NOT NULL, scope SM   ALLINT NOT NULL, INDEX MESSAGE_IDX (locale, domain, `key`, scope), PRIMARY   KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB   ':     SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t   oo long; max key length is 767 bytes

After some googling (not sure if that’s a word), I’ve found that the default storage engine for the Mysql server 5.5.5 and above is InnoDB. And I’m running MacOSX Mavericks with php version 5.4.17 and the MySQL community server version 5.6.17. So I’ve looked up the reference for MySQL 5.5 and it states:

If you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5).

MySQL 5.5 Reference Manual Source

Which is exactly the underlying problem. Since InnoDB storage engines only let you store a max length of 767 bytes. MYISAM on the other hand will let you store up to 1000 bytes. I’m not sure if somebody else ran into this problem but it I can only imagine that this will be seen in the near future (after releasing the first stable release and everybody starts to develop).

In order to fix this bug(?) I changed the length of the key, but I’m not sure what concequenses this will have for the platform as a whole. I changed the length of the key in ../vendor/oro/platform/src/Oro/Bundle/TranslationBundle/Entity/Translation.php. And it completed the install as usual. This is, obviously, not a real solution but a temporary fix. Since I don’t like to edit the core and the whole principal of creating a platform is being diminished.

I think this should be mentioned in the README, somewhere in the installation requirements or change the length of the key since this will be a serious problem for those who will use MySQL 5.5.5 and above. If they are not aware that InnoDB will not store the key field for the ‘oro_translation’ table for having a greater byte length than InnoDB allows you to store.

I look forward to your responds.

Kind Regards,

Jaimy Casteleijn

Topic

[Dima SorokaKeymaster]

Thanks Jaimy, we already resolved this issue and fix is included into upcoming release.

[JaimyParticipant]

Thanks for the quick reply Dima! Looking forward to the next release.

Kind Regards,

Jaimy Casteleijn

[Author]

Replies