Drupal 7 Jan 1, 2010. using MyISAM database. I get the following error message that says there is a limitation in InnoDB.
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: CREATE TABLE {example} ( `directory` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Directory to search', `path` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Path from directory to file', `name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Name of the file', `creation_date` INT NOT NULL DEFAULT 0 COMMENT 'Creation date from file system' PRIMARY KEY (`directory`, `path`, `name`) ) ENGINE = InnoDB DEFAULT CHARACTER SET UTF8; Array ( ) in db_create_table() (line 2356 of H:\home\example\public_html\includes\database\database.inc).

* The Drupal PDO code has engine = InnoDB despite the database using MyISAM.
* The tables are successfully created as MyISAM when I reduce the keylength.

Comments

Crell’s picture

Crell CreditAttribution: Crell commented
Component: database system » mysql database

Hm. Interesting. Is that table coming out of the simple tests, or some module, or...?

Drupal now defaults to InnoDB, but MySQL will silently fall back to MyISAM if it's not available (because MySQL will do everything silently). So my guess is that Drupal is requesting InnoDB, but MySQL is giving it MyISAM, and the keylength is then too long.

Or is someone getting this on an InnoDB-capable system as well?

peterx’s picture

peterx CreditAttribution: peterx commented

My test is on a system with MyISAM installed. No InnoDB. The system contains a copy of a Drupal 6 site I used for testing D6 with PHP 5.3. Now I am starting on a small D7 test using Jan 01 and the standard profile. The example table is from my experiment with converting one of my D7 modules. If people cannot use MyISAM, the conversion documentation will have to describe the differences with InnoDB including this key length difference. In my case, I will reduce the key length by using a structure of multiple tables with integer ids. That will require joins for most accesses.

* The key length is less than 1000 characters, only 765 characters, but the database is set to UTF with each of the 765 characters taking up a t least 2 bytes and possibly 3 bytes, making the byte length greater than 1000 bytes. Many MySQL users do not know the difference between the character length and the byte length. If they have a site running on an old system and set up a new system for their D7 version of the site, they might also change from ASCII to UTF in the process and run into similar byte length anomalies. Worth a note somewhere in a conversion guide.

Crell’s picture

Crell CreditAttribution: Crell commented

Hm. OK, yeah, this does sound like mostly a documentation problem then. I didn't realize that InnoDB had a larger allowed keysize. Drupal has forced UTF-8 in the database for several versions, though.

Where/how should we best document this to warn module devs to be careful of their key sizes?

peterx’s picture

peterx CreditAttribution: peterx commented

I suggest pages something like:
Converting from MyISAM to InnoDB
Converting to UTF8
Link to them from the pages describing the D6 to D7 conversion and the pages describing module development for D7. People with experience in those areas can contribute whatever they find during the first D6 to D7 conversions. We can add links back to the definitive MySQL and InnoDB documentation but we need a summary in the drupal.org site so that the keywords will appear in a search of Drupal documentation.

I do not know if there are D7 pages for the D7 ways to keep databases compatible across MySQL and PostgreSQL. They should be crosslinked with the conversion pages so a person contemplating a D6 to D7 conversion can read about all the possible changes. With Sun making a botch of MySQL development and Oracle running a big sword through Sun, a conversion to PostgreSQL is sounding nice.

10basetom’s picture

10basetom CreditAttribution: 10basetom commented

I just tried upgrading from v6.20 to v7.0 today and failed because it seems Drupal 7 *requires* InnoDB?

If this is true, it will prevent many people from upgrading to v7 because most shared hosts do not support InnoDB. Is there a way to force Drupal 7 to use MyISAM?

UPDATE: I realized the installation failed because "skip-innodb" was uncommented in the MySQL config that came with UniServer. Once I commented that out and also uncommented "extension=php_pdo_mysql.dll" then the installation proceeded without issues.

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented

Drupal 7 does not required InnoDB, and most shared hosts actually do indeed support InnoDB anyway.