Bug #30417 Cluster misbehaves on auto-inc w/o PK

Description:
When trying to CREATE or ALTER an NDB table to have an auto-increment column without a primary key, various bad things happen. Even if this configuration is not *supported*, or is even *prohibited*, it should be prevented or cleaned up properly, rather than blowing up and leaving things strewn around.

There are two scenarios:

1.
  create table t1 (id int auto_increment, key(id)) engine=ndbcluster;

Scenario #1 will half-way create a table instead of failing cleanly, but can fortunately be cleaned up simply by dropping the table and trying again w/ a proper schema.

2.
  create table t1 (id int auto_increment, key(id)) engine=myisam;
  alter table t1 engine=ndbcluster;

Scenario #2 will cause more problems, because it leaves a temporary table lying around that can prevent future ALTER TABLE attempts.

How to repeat:
Scenario #1 will half-way create a table instead of failing cleanly:

mysql> create table t1 (id int auto_increment, key(id)) engine=ndbcluster;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 4335)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.07 sec)

mysql> select * from t1;
ERROR 1296 (HY000): Got error 4243 'Index not found' from NDBCLUSTER

# ndb_show_tables
5     UserTable            Online   Yes     test         def      t1

Scenario #2 causes many more problems:

mysql> create table t1 (id int auto_increment, key(id)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t1 engine=ndbcluster;
ERROR 1005 (HY000): Can't create table 'test.#sql-100b_b' (errno: 4335)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table t1 add primary key (id);
ERROR 1050 (42S01): Table '#sql-100b_b' already exists

[root@training5 mysql]# ndb_show_tables
id    type                 state    logging database     schema   name
5     UserTable            Online   Yes     test         def      #sql-100b_b

So, it seems that any attempt to alter this table, even if it remains MyISAM, will fail! (See below for possible "fix".)

Suggested fix:
If auto-increment columns on tables w/o PKs are prohibited, then they should be prohibited outright instead of being allowed to cause these various side-effects.

It is possible to remove lingering temporary NDB table like this:

mysql> CREATE TABLE `#sql-100b_XXX` (id int) engine=ndbcluster;

shell> cp \@0023sql\@002d100b_XXX.frm \@0023sql\@002d100b_b.frm
shell> cp \@0023sql\@002d100b_XXX.ndb \@0023sql\@002d100b_b.ndb

mysql> drop table `#sql-100b_b`;
mysql> drop table `#sql-100b_XXX`;

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/38771

ChangeSet@1.2555, 2007-11-29 10:15:07+08:00, gni@dev3-221.dev.cn.tlan +1 -0
  BUG#30417 Cluster misbehaves on auto-inc w/o PK.

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/38816

ChangeSet@1.2555, 2007-11-29 19:19:55+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#30417 Cluster misbehaves on auto-inc w/o PK.

pushed into mysql-5.1-ndb-bj

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/40829

ChangeSet@1.2557, 2008-01-10 17:39:50+08:00, gni@dev3-221.dev.cn.tlan +2 -0
  Shouldn't let the non-deterministic temperary filename output in result file for bug#30417

Documented in 5.1.23-ndb-6.3.8 changelog as follows:

      The server failed to reject properly the creation of an NDB table having an
      unindexed AUTO_INCREMENT column.

Left in PQ status pending further merges.

Also documented fix for 5.1.23-ndb-6.2.11; left status unchanged.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Also documented for 5.1.24 and 6.0.5.