MySQL Bugs: #46630: The maximum key length for MySQL Cluster does not appear to be documented

Bug #46630	The maximum key length for MySQL Cluster does not appear to be documented
Submitted:	10 Aug 2009 13:28	Modified:	20 Aug 2009 14:18
Reporter:	Andrew Hutchings	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
The maximum key length for the MySQL Cluster does not appear to be documented as it is for MyISAM and InnoDB.

From tests with 7.0.6 this appears to be at 3072 bytes

How to repeat:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html

"Prefix lengths are storage engine-dependent (for example, a prefix can be up to 1000 bytes long for MyISAM tables, 767 bytes for InnoDB tables)."

Suggested fix:
Add to the above URL about NDB's max index length and/or put it in the cluster limitations section of the manual.

How did you verify this, Andrew?

My testing shows that the error message referring to the 3072 limit (which I'm guessing is where you got the 3072 figure) is actually triggered when the *column* size is greater than 3072, without regard to the size of the key.

Observe:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.35-ndb-7.0.7 |
+------------------+
1 row in set (0.00 sec)

mysql> create table t1 (c1 varchar(500), key k1 (c1(20)))engine ndb;
Query OK, 0 rows affected (1.86 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.88 sec)

mysql> create table t1 (c1 varchar(3000), key k1 (c1(20)))engine ndb;
Query OK, 0 rows affected (2.57 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.86 sec)

mysql> create table t1 (c1 varchar(3071), key k1 (c1(20)))engine ndb;
Query OK, 0 rows affected (1.89 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.97 sec)

mysql> create table t1 (c1 varchar(3072), key k1 (c1(20)))engine ndb;
Query OK, 0 rows affected (1.81 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.90 sec)

mysql> create table t1 (c1 varchar(3073), key k1 (c1(20)))engine ndb;
Query OK, 0 rows affected, 1 warning (1.82 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 3072 bytes |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

This isn't a Docs issue (until it's fixed), since I can't conclusively verify the behaviour I was originally asked to document. Not sure if this is specific to Cluster or not; someone else will need to make that determination. Synopsis also needs to be updated.

I didn't bother to test, but I suspect this misbehaviour will also be found in the other Cluster trees.

Set Category/Status to Server/Open, removed myself and Stefan.

Wow, ok.

I was testing using multiple UTF8 columns as keys.  But this behaviour does not seem correct as you say.

Hi Jon,

The reason why your last test failed is cluster does not support index prefixing (couldn't find that in the docs either, but must be there somewhere):

mysql> create table t1 (c1 varchar(3072), key k1 (c1(20)))engine ndb;
Query OK, 0 rows affected (0.71 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` varchar(3072) DEFAULT NULL,
  KEY `k1` (`c1`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Let me know if you need any more tests/information, bouncing back your way ;)

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.