MySQL Bugs: #52613: Inconsistent and permanent error when attribute name is to long for PK

Bug #52613	Inconsistent and permanent error when attribute name is to long for PK
Submitted:	6 Apr 2010 9:04	Modified:	6 Apr 2010 9:42
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-7.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	attribute, limit, mysql-5.1.44-ndb-7.0.14

Description:
When creating a Primary Key attribute which has a to long name, error 1059 is usually returned. However, With MySQL Cluster, if you specify one character (or byte) to much it will fail with NDB error 4247 or MySQL error 1005.

Worse however, is that when a CREATE TABLE fails with 4247, you can't retry before dropping it, even though creation failed in first place.

How to repeat:
(see something more suitable for copy/paste at end of 'How to repeat')

mysql> CREATE TABLE t1 (♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔  INT KEY)        ENGINE=NDBCluster;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 4247)
mysql> SHOW WARNINGS;
| Error | 1296 | Got error 4247 'Illegal index/trigger create/drop/alter request' from NDB
| Error | 1005 | Can't create table 'test.t1' (errno: 4247)

The length (using MySQL LENGTH()) is 196.

Now, if you add a king:

mysql> CREATE TABLE t1 (♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔  INT KEY)        ENGINE=NDBCluster;
ERROR 1059 (42000): Identifier name '♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔?' is too long

Notice they '?' at the end of the error. Length is 198.

The following works, with attribute length being 99 bytes:

mysql> CREATE TABLE t1 (♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔  INT KEY) ENGINE=NDBCluster;

However, if you do the following sequence of statements, once you get error 4247, you can't create a table named t1 unless you drop it:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔♔  INT KEY)        ENGINE=NDBCluster;
-- Gives error 4247
-- Now create table table with something we know works:
CREATE TABLE t1 (id INT KEY) ENGINE=NDBCluster;
SHOW WARNINGS;

The above gives "Got error 4247 'Illegal index/trigger create/drop/alter request' from NDB".

Verified using MySQL Cluster 7.0 (latest pull from bzr) on Linux. Originally tested on MacOS X 10.6.

The biggest problem is that you can re-try recreating the table without dropping it. So it seems that the table name is there, but ndb_show_tables doesn't mention it.

Workaround is to drop the table before recreating it.
The error should be consistent: if to long, say it is to long.

Posted by developer:
 
This issue is not reproducible in recent releases.