Installation fails with MyISAM - key too long [#1969680]

Problem/Motivation

8.x fails to install if you are using MyISAM as database driver. The key/index of the {file_usage} table is too long (longer than 1000bytes) for MyISAM.

Proposed resolution

According to comment #7, one of the the following two issues would fix this problem:

Remaining tasks

Fixing #1852454: Restrict module and theme name lengths to 50 characters will solve this issue as a side effect.

#1852896: Throw an exception if a schema defines a key that would be over 1000 bytes in MySQL

Original report by patrickd

Currently 8.x fails to install if you are using MyISAM as database driver.

Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: CREATE TABLE {file_usage} ( `fid` INT unsigned NOT NULL COMMENT 'File ID.', `module` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'The name of the module that is using the file.', `type` VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'The name of the object type in which the file is used.', `id` VARCHAR(64) NOT NULL DEFAULT 0 COMMENT 'The primary key of the object using the file.', `count` INT unsigned NOT NULL DEFAULT 0 COMMENT 'The number of times this file is used by this object.', PRIMARY KEY (`fid`, `type`, `id`, `module`), INDEX `type_id` (`type`, `id`), INDEX `fid_count` (`fid`, `count`), INDEX `fid_module` (`fid`, `module`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Track where a file is used.'; Array ( )

This means that the key/index of the file_usage table is too long (longer than 1000bytes) for MyISAM.

This can be easily reproduced on simplytest.me as it uses MyISAM: http://simplytest.me/project/drupal/8.x

Comments

Comment #1

patrickd CreditAttribution: patrickd commented 13 April 2013 at 19:28

If we restrict the module shortname size to 40 or 64 characters this issue would probably be resolved too.

Currently the key of this table consists of
- fid: "int"
- module: varchar(255)
- type: varchar(64)
- id: varchar(64)

Comment #2

xjm

she/her

English

CreditAttribution: xjm commented 13 April 2013 at 20:43

Priority:

Major

» Critical

See: #1852896: Throw an exception if a schema defines a key that would be over 1000 bytes in MySQL. Bumping that to critical.

Leaving this open in parallel for the moment so we can fix the problematic index to unbreak MyISAM AGAIN.

Comment #3

Bojhan CreditAttribution: Bojhan commented 20 April 2013 at 09:45

Thanks for opening this, this is quite a bug.

Comment #4

catch

he/him

English

CreditAttribution: catch commented 26 April 2013 at 13:24

Issue tags:

+Needs backport to D7

I'm confused why this has only just shown up on 8.x, the schema and indexes are exactly the same as 7.x, no changes at all. The only difference is that file module is optional is 7.x so wouldn't necessarily blow up the installer depending on which install profile is used.

http://api.drupal.org/api/drupal/core%21modules%21file%21file.install/fu...

http://api.drupal.org/api/drupal/modules%21system%21system.install/funct...

Because module is the primary key, we can't just index on a substring, so this is probably going to require #1852454: Restrict module and theme name lengths to 50 characters.

Adding D7 backport tag.

Comment #5

patrickd CreditAttribution: patrickd commented 26 April 2013 at 13:50

no they are not:

D7:
'id' => array(
'type' => 'int',

D8
'id' => array(
'type' => 'varchar',
'length' => 64,

That explains it pretty good ;-)

I guess we can remove the backport tag? setting a module name limit in D7 would be kind of an API break?

Comment #6

catch

he/him

English

CreditAttribution: catch commented 26 April 2013 at 15:52

Issue tags:

-Needs backport to D7

Doh, I looked more than once too...

Comment #7

xjm

she/her

English

CreditAttribution: xjm commented 11 May 2013 at 23:10

So we probably need to do at least one of #1852454: Restrict module and theme name lengths to 50 characters and #1709960: declare a maximum length for entity and bundle machine names to fix this properly.

Comment #8

xjm

she/her

English

CreditAttribution: xjm commented 12 May 2013 at 21:11

Note that it also affects the upgrade path: #1976110: D8 upgrade path: file module; Update #8001. So we will need to make sure the update adding the key doesn't happen before the module name field size is reduced. Fortunately, we don't support the 8-to-8 upgrade path yet.