MySQL Bugs: #17190: CREATE TABLE with just KEY (a) returns failed: 1005: Can't create table

Bug #17190	CREATE TABLE with just KEY (a) returns failed: 1005: Can't create table
Submitted:	7 Feb 2006 14:07	Modified:	29 Mar 2006 11:01
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.7-alpha	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
Investigating rpl_insert_id.test failure, I found that the following create statement fails on NDB where is passes in MyISAM and Innodb.
create table t1(a int auto_increment, key(a));

output from NDB run
mysqltest: At line 5: query 'create table t1(a int auto_increment, key(a))' failed: 1005: Can't create table 'test.t1' (errno: 4335)

output from MyISAM run:
+ create table t1(a int auto_increment, key(a));
+ show create table t1;
+ Table Create Table
+ t1    CREATE TABLE `t1` (
+   `a` int(11) NOT NULL auto_increment,
+   KEY `a` (`a`)
+ ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ DROP TABLE t1;

output from InnoDB run:
+ create table t1(a int auto_increment, key(a))ENGINE=INNODB;
+ show create table t1;
+ Table Create Table
+ t1    CREATE TABLE `t1` (
+   `a` int(11) NOT NULL auto_increment,
+   KEY `a` (`a`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ DROP TABLE t1;

How to repeat:
See above

Suggested fix:
NDB should support SQL syntax that the other storage engines support.

this is a known limitation in ndb, it should be documented in limitations, if not it should be added

limitation is you cannot have a table without primary key and autoincrement at the same time

And so why is (a) not treated as a PK?

This seems to be what the other engines are doing.

Why is NDB so special that it can not behave in the same manner?

Thanks
JBM

If you want the key to be a primary key then state "PRIMARY KEY" instead.

The "other" handlers do not treat the KEY as a primary key that I know of...

Okay, I was wrong on this one. Jon please ensure Limitations section gets updated.

master> create table t1(a int auto_increment, key(a));
	tomas	ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 4335)
	tomas	master> show warnings;
	tomas	+-------+------+--------------------------------------------------------------------------------------------------------------------------------+
	tomas	| Level | Code | Message |
	tomas	+-------+------+--------------------------------------------------------------------------------------------------------------------------------+
	tomas	| Error | 1296 | Got error 4335 'Only one autoincrement column allowed per table. Having a table without primary key uses an autoincr' from NDB |
	tomas	| Error | 1005 | Can't create table 'test.t1' (errno: 4335) |
	tomas	+-------+------+--------------------------------------------------------------------------------------------------------------------------------+

Per http://dev.mysql.com/doc/refman/5.0/en/create-table.html:
"The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition."

Note the phrase "when given in a column definition". This illustrates the difference:

mysql> create table t(id int not null auto_increment, key(id));
Query OK, 0 rows affected (0.07 sec)

mysql> describe t;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | MUL | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> create table t(id int not null auto_increment key);
Query OK, 0 rows affected (0.06 sec)

mysql> describe t;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

So what's the real issue here? Do we need to say that an AUTO_INCREMENT column in an NDB table must be a PK (and not merely an index)? And must it be the only column in the PK, or can this column be part of a multi-column PK?

Please test this and let me know the results.

Thanks!

master> create table t(id int not null auto_increment, key(id)) engine=ndb;
ERROR 1005 (HY000): Can't create table 'test.t' (errno: 4335)
master> create table t(id int not null auto_increment key) engine=ndb;
Query OK, 0 rows affected (0.90 sec)

The NDB storage engine can only handle one autoincrement column per table.  An autoincrement column is used as a hidden primary key when a table is defined without a primary key.  Hence, one cannot define a sql table that has _no_ primary key and an autoincrement at the same time.

We need to make sure this gets into limitations. :-(

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
product(s).

Additional info:

Added to 4.1/5.0/5.1 Cluster Limitations section. Closed.