Environment:
Drupal 6.6
Vista
Apache 2.2
MySQL Ver 14.14 Distrib 6.0.5-alpha, for Win32 (ia32) using innodb
PHP 5.2.6

Problem encountered:

During Drupal install, eleven tables were not created, error messages were as follows

Warning: Specified key was too long; max key length is 767 bytes query: CREATE TABLE actions ( `aid` VARCHAR(255) NOT NULL DEFAULT '0', `type` VARCHAR(32) NOT NULL DEFAULT '', `callback` VARCHAR(255) NOT NULL DEFAULT '', `parameters` LONGTEXT NOT NULL, `description` VARCHAR(255) NOT NULL DEFAULT '0', PRIMARY KEY (aid) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in [==path redacted==]\drupal\includes\database.mysql.inc on line 128

Tables not created:
actions cache cache_form cache_page cache_menu menu_router system filter_formats cache_filter cache_block node_access

My understanding (so far) of issue causes:
UTF-8 is a multi-byte encoding, and since the Drupal install attempts to create indexes on VARCHAR(255) columns, the key length exceeds what is allowed by MySQL.

Questions:

1) What is the optimal work-around?
a) For development purposes, if I need not support non-English character sets, is there a place I can set the default encoding to "latin1"?
b) If I need to modify the table create statements to reduce the size of the varchar columns, which file is the appropriate place to modify? Also, will this impact any code anywhere that is expecting a varchar(255)?

2) Does Drupal perform joins on tables having primary keys and indexes on large varchar columns? I can dig through the code to find out more, but I thought I would ask first in the hope that someone might provide insight. How many rows are these tables expected to contain? Over the years I've seen many a programming team re-develop a database schema to use numeric id's instead of character fields for indexes. Scalability improvement and performance increases can be dramatic, depending upon how the tables are used.

3) For the re-install, should I do anything other than drop the tables and re-run install.php?

Background information: How I attempted Install
Extracted Drupal files into htdocs under Apache
Copied the default.settings.php to settings.php
Created the Drupal database and user (followed instructions in INSTALL.mysql.txt
Tested a create statement and verified that the table created defaulted to using "latin1" as the charset
Ran http://localhost/drupal/install.php from the browser (IE)
Reviewed logged bugs and issues, and found several related, but did not find one close enough to what I've encountered.

Thanks in advance for replies. I appreciate all the work the Drupal team has done so that someone like me can save time on development.

Best regards,

John
Austin, Texas

Comments

ifmxaus’s picture

ifmxaus commented
Version: 6.6 » 6.8

Problem still exists in Drupal 6.8.
If anyone knows an easier solution pls let me know--I am going to try modifying table definitions.
That will involve digging through the subdirectories of the Modules directory, looking for table definitions in the *.install files (look for "hook_install"), and decreasing the size of the columns that are causing the failures.
As I am prototyping on Vista, this is a challenge because I don't have an equivalent of "grep".
So far I found the "actions" table in the modules/system/system.install
I hope this works...

ifmxaus’s picture

ifmxaus commented

Status: In system.install, for schema "actions" I decreased the length of column "aid" from 255 down to 191 and now the install creates the table instead of failing. Why did I choose 191? Because MySQL appears to be allocating 4 bytes for every char, and the max length on a key is 767 bytes.

After I hunt down all the tables, I'll give an update on whether or not Drupal installs and seems to function properly.

ifmxaus’s picture

ifmxaus commented

I also had to modify a schema in node.install and a schema in filter.install so that the tables node_access and filter_formats would create.
Now Drupal appears to have installed.

gbaudoin’s picture

gbaudoin commented
Status: Active » Closed (won't fix)

Please retry on the latest 6.x version and re-open if necessary.