MySQL Bugs: #26909: Specified key was too long; max key length is 255 bytes when create table

Bug #26909	Specified key was too long; max key length is 255 bytes when create table
Submitted:	7 Mar 2007 9:27	Modified:	3 Aug 2007 17:03
Reporter:	li pickup (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.0.24, 5.1 BK, 5.2-falcon	OS:	Linux (linux)
Assigned to:	Chad MILLER	CPU Architecture:	Any
Tags:	bfsm_2007_05_31, CREATE TABLE, federated

Description:
The are some error when I create a federated table. the error message is: "ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes". 

But I have no problems when it is not federated table.

The table on the remote host only has 5 row in it.

How to repeat:
on remote host:
CREATE TABLE frm_category (
  categoryId int(11) NOT NULL auto_increment,
  domainId varchar(40) NOT NULL default '',
  parentCategoryId int(11) default '0',
  categoryName varchar(255) NOT NULL default '',
  categoryDesc text,
  categoryCreationDate datetime default NULL,
  categoryModifiedDate datetime default NULL,
  categoryOrder int(11) default '0',
  categoryOption int(11) default NULL,
  categoryStatus int(11) default NULL,
  resource_uuid varchar(32) default NULL,
  lastUpdDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (categoryId),
  UNIQUE KEY idx_unique_category_categoryName (domainId,categoryName),
  KEY idx_category_domainId (domainId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='category'

on local host:
 CREATE TABLE frm_category (
  categoryId int(11) NOT NULL auto_increment,
  domainId varchar(40) NOT NULL default '',
  parentCategoryId int(11) default '0',
  categoryName varchar(255) NOT NULL default '',
  categoryDesc text,
  categoryCreationDate datetime default NULL,
  categoryModifiedDate datetime default NULL,
  categoryOrder int(11) default '0',
  categoryOption int(11) default NULL,
  categoryStatus int(11) default NULL,
  resource_uuid varchar(32) default NULL,
  lastUpdDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (categoryId),
  UNIQUE KEY idx_unique_category_categoryName (domainId,categoryName),
  KEY idx_category_domainId (domainId)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='category'
 connection='mysql://root:password@192.168.1.114:3306/wp_forum/frm_category';

So you can see the error message "ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes"

Suggested fix:
I have delete the "UNIQUE KEY idx_unique_category_categoryName (domainId,categoryName)," and execute the create schema with no problems.

Thank you for the report.

Verified as described.

All versions are affected.

test case

Attachment: federated_bug26909.test (application/octet-stream, text), 1.72 KiB.

This actually appears to be caused by having a single part of a key larger than 255 bytes.  The total size of the index has been bumped up to 3500, but each piece is still restricted to 255.  This is due to the federated handler not declaring a max_supported_key_part_length() value.  Since it doesn't, it uses the default of 255 which causes this error.

To fix:

Simply define max_supported_key_part_length() to return a larger value, perhaps FEDERATED_MAX_KEY_LENGTH.

Patch to fix the problem:

===== storage/federated/ha_federated.h 1.45 vs edited =====
--- 1.45/storage/federated/ha_federated.h       2007-05-03 14:47:46 -04:00
+++ edited/storage/federated/ha_federated.h     2007-05-03 14:23:59 -04:00
@@ -150,6 +150,7 @@
   uint max_supported_keys()          const { return MAX_KEY; }
   uint max_supported_key_parts()     const { return MAX_REF_PARTS; }
   uint max_supported_key_length()    const { return FEDERATED_MAX_KEY_LENGTH; }+  uint max_supported_key_length_part() const { return FEDERATED_MAX_KEY_LENGTH; }
   /*
     Called in test_quick_select to determine if indexes should be used.
     Normally, we need to know number of blocks . For federated we need to

Hrm, my patch doesn't seem to fix it for unique/primary keys, but works for regular indexes, not sure what value those are using.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30829

ChangeSet@1.2503, 2007-07-12 15:11:49-04:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#26909: Specified key was too long; max key length is 255 bytes \
  	when creating table
  
  Federated tables have an artificially low maximum of key length, 
  because the handler fails to implement a method to return it and 
  the default value is taked from the prototype handler.

patch looks trivial. Ok from me.

Approved with small changes. Please see email.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31112

ChangeSet@1.2503, 2007-07-18 13:56:56-04:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#26909: Specified key was too long; max key length is 255 bytes \
  	when creating table
  
  Federated tables had an artificially low maximum of key length, 
  because the handler failed to implement a method to return it and 
  the default value is taked from the prototype handler.
  
  Now, implement that method and return the maximum possible key
  length, which is that of InnoDB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31113

ChangeSet@1.2503, 2007-07-18 14:03:58-04:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#26909: Specified key was too long; max key length is 255 bytes \
  	when creating table
  
  Federated tables had an artificially low maximum of key length, 
  because the handler failed to implement a method to return it and 
  the default value is taked from the prototype handler.
  
  Now, implement that method and return the maximum possible key
  length, which is that of InnoDB.

Queued to 5.0-maint and 5.1-maint.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.