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

[7 Mar 2007 9:27] li pickup
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.
[7 Mar 2007 12:14] Sveta Smirnova
Thank you for the report.

Verified as described.

All versions are affected.
[7 Mar 2007 12:15] Sveta Smirnova
test case

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

[3 May 2007 16:57] Harrison Fisk
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.
[3 May 2007 18:48] Harrison Fisk
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
[3 May 2007 19:04] Harrison Fisk
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.
[12 Jul 2007 19:13] Bugs System
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.
[18 Jul 2007 16:26] Antony Curtis
patch looks trivial. Ok from me.
[18 Jul 2007 16:55] Ingo Strüwing
Approved with small changes. Please see email.
[18 Jul 2007 17:58] Bugs System
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.
[18 Jul 2007 18:05] Bugs System
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.
[19 Jul 2007 19:41] Chad MILLER
Queued to 5.0-maint and 5.1-maint.
[2 Aug 2007 19:12] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:14] Bugs System
Pushed into 5.0.48
[3 Aug 2007 17:03] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.