Bug #10566 can not create primary key on memory table
Submitted: 12 May 2005 5:47 Modified: 24 Jun 2005 13:54
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:Microsoft Windows (Windows xp)
Assigned to: Jim Winstead CPU Architecture:Any

[12 May 2005 5:47] Disha
Description:
I failed to crate primary key on char column width 255 using engine type memory. The lenth of primary key is accepted upto only 85. After that its give errors. 

How to repeat:
A) Execute the following command.
      1. delimiter //
      2. set @@sql_mode='traditional'//
      3. Create table t1 (c1 CHAR(255)  CHARACTER SET utf8 , PRIMARY KEY (c1(90))) Engine=memory//

B) Expected Result: Index created sucessfully
   
C) Actual Result: ERROR 1071 (42000): Specified key was too long; max key  
                        length is 255 bytes.
[12 May 2005 7:19] Hartmut Holzgraefe
Characters != Bytes, a character may take up to 3 bytes if UT-8 encoded.
So a 255 character column may take up to 765 bytes whereas the max.
length for an index is 255.
[12 May 2005 12:39] Miguel Solorzano
Something needs to be clarified because the behavior is related
to the memory engine:

mysql> Create table t1 (c1 CHAR(255)  CHARACTER SET utf8 , PRIMARY KEY
    -> (c1(90)));
Query OK, 0 rows affected (0.08 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` char(255) character set utf8 NOT NULL default '',
  PRIMARY KEY  (`c1`(90))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> Create table t2 (c1 CHAR(255)  CHARACTER SET utf8 , PRIMARY KEY
    -> (c1(90))) engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> Create table t3 (c1 CHAR(255)  CHARACTER SET utf8 , PRIMARY KEY
    -> (c1(90))) engine=memory;
ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes
mysql>
[12 May 2005 12:43] Miguel Solorzano
Acording the Manual:

MEMORY tables allow up to 32 indexes per table, 16 columns per index, and a maximum key length of 500 bytes.
[24 May 2005 19:21] 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/internals/25236
[2 Jun 2005 1:16] Jim Winstead
Updating version, as the problem actually exists in 4.1 as well.
[10 Jun 2005 4:56] Jim Winstead
Fixed in 4.1.13 and 5.0.8.
[24 Jun 2005 13:54] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Updated 4.1.13 and 5.0.8 changelogs, MEMORY Storage Engine section of Manual. Bug closed.