| Bug #15918 | Unique Key Limit in NDB Engine | ||
|---|---|---|---|
| Submitted: | 21 Dec 2005 23:31 | Modified: | 23 Feb 2006 9:41 |
| Reporter: | Doug Hunting | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1-BK, 5.0.16 | OS: | Linux (Linux, FreeBSD 5.4) |
| Assigned to: | Pekka Nousiainen | CPU Architecture: | Any |
[21 Dec 2005 23:31]
Doug Hunting
[22 Dec 2005 12:08]
Valeriy Kravchuk
Thank you for a problem report. According to the information from the cluster team, internally ndb supports keys up to 4K in size. The size is limited (to 255) by MySQL for ndbcluster table handler, for some reason. So, I mark this report as a verified feature request. I was also not able to find any information about key size for ndb in the manual, so I'll add a documentation request for this also.
[22 Dec 2005 12:22]
Jonathan Miller
Mr Hunting, Unless I am mistaken, did you not get this to work using DEFAULT CHARSET=latin1 and then have to do alter table to back to UTF8? Please add any other information you can on dealing with this issue. Thanks Jonathan.
[22 Dec 2005 20:20]
Doug Hunting
Yes it does work when I create the table with DEFAULT CHARSET=latin1 and then ALTER TABLE to utf8 however I was concerned that index would not get create properly and that because of the key limit, there may be a corruption of the data stored in the key field
[22 Dec 2005 20:30]
Rick Updegrove
Another way to fix: change xar_name varchar(255) to xar_name varchar(63) CREATE TABLE xar_block_groups ( xar_id int(11) NOT NULL auto_increment, xar_name varchar(63) NOT NULL default '', xar_template varchar(255) NOT NULL default '', PRIMARY KEY (xar_id), UNIQUE KEY i_xar_block_groups (xar_name) )ENGINE=NDB DEFAULT CHARSET=utf8; No longer results in: ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes Because of course it would be 63*3 = 189 below the 255 limit. However, this worked in this case but I would really appreciate a way to increase the max key length to any size via my.cnf somehow.
[23 Dec 2005 16:31]
Jonathan Miller
On Wed, 2005-12-21 at 14:29 -0800, Doug Hunting wrote: > I have been able to create the table by adding: ' DEFAULT > CHARSET=latin1' and then > immediately after the create statement, alter the table back to UTF8, > but I worry that the index would not have gotten created properly after > the change to the CHARSET for the table... > Unfortunately, changing the database design is NOT an acceptable solution. ALTER actually creates a new table and copies the data to it, deletes the old one and renames the new one. So there shouldn't be a problem... Certainly should be reported as a bug though - things should work consistently. -- Stewart Smith, Software Engineer
[4 Jan 2006 14:10]
Valeriy Kravchuk
The limitation is still 255, anyway. Even on the newest 5.1.5-BK (ChangeSet@1.2029, 2006-01-04 10:36:54+01:00) on Linux. Please, look:
mysql> CREATE TABLE xar_block_groups (
-> xar_id int(11) NOT NULL auto_increment,
-> xar_name varchar(255) NOT NULL default '',
-> xar_template varchar(255) NOT NULL default '',
-> PRIMARY KEY (xar_id),
-> UNIQUE KEY i_xar_block_groups (xar_name)
-> )ENGINE=NDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes
Yes, latin1 is OK:
mysql> CREATE TABLE xar_block_groups ( xar_id int(11) NOT NULL auto_increment, xar_name varchar(255) NOT NULL default '', xar_template varchar(255) NOT NULL default '', PRIMARY KEY (xar_id), UNIQUE KEY i_xar_block_groups (xar_name) )ENGINE=NDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (1.13 sec)
mysql> show index from xar_block_groups;
+------------------+------------+--------------------+--------------+-----------
--+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_nam
e | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+--------------------+--------------+-----------
--+-----------+-------------+----------+--------+------+------------+---------+
| xar_block_groups | 0 | PRIMARY | 1 | xar_id
| A | 0 | NULL | NULL | | BTREE | |
| xar_block_groups | 0 | i_xar_block_groups | 1 | xar_name
| A | 0 | NULL | NULL | | BTREE | |
+------------------+------------+--------------------+--------------+-----------
--+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)
mysql> alter table xar_block_groups default charset=utf8;
Query OK, 0 rows affected (2.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from xar_block_groups;
+------------------+------------+--------------------+--------------+-----------
--+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_nam
e | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+--------------------+--------------+-----------
--+-----------+-------------+----------+--------+------+------------+---------+
| xar_block_groups | 0 | PRIMARY | 1 | xar_id
| A | 0 | NULL | NULL | | BTREE | |
| xar_block_groups | 0 | i_xar_block_groups | 1 | xar_name
| A | 0 | NULL | NULL | | BTREE | |
+------------------+------------+--------------------+--------------+-----------
--+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
Yes, this works, but:
mysql> show create table xar_block_groups;
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------+
| Table | Create Table |
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------+
| xar_block_groups | CREATE TABLE `xar_block_groups` (
`xar_id` int(11) NOT NULL auto_increment,
`xar_name` varchar(255) character set latin1 NOT NULL default '',
`xar_template` varchar(255) character set latin1 NOT NULL default '',
PRIMARY KEY (`xar_id`),
UNIQUE KEY `i_xar_block_groups` (`xar_name`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8 |
+------------------+------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------+
1 row in set (0.01 sec)
So, indexed column is still latin1! And if one try to modify it explicitely:
mysql> alter table xar_block_groups modify xar_name varchar(255) character set utf8 NOT NULL default '';
ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes
mysql> select version();
+-------------+
| version() |
+-------------+
| 5.1.5-alpha |
+-------------+
1 row in set (0.00 sec)
it is obvious, that 225 bytes limitation is still there. It should be relaxed (or, at least, clearly described in the manual) to the limits of other engines.
[7 Feb 2006 18:59]
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/2269
[14 Feb 2006 8:41]
Jon Stephens
Need 3-part version number(s) this fix pushed to. (Was this also fixed in 5.0 tree?) Thanks!
[23 Feb 2006 9:41]
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:
Documented in 5.0.19 and 5.1.7 changelogs. Closed.
