| Bug #30417 | Cluster misbehaves on auto-inc w/o PK | ||
|---|---|---|---|
| Submitted: | 14 Aug 2007 18:25 | Modified: | 20 Feb 2008 21:01 |
| Reporter: | Kolbe Kegel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.20 | OS: | Linux |
| Assigned to: | Guangbao Ni | CPU Architecture: | Any |
| Tags: | cluster | ||
[29 Nov 2007 2:29]
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/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.
[29 Nov 2007 11:34]
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/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.
[10 Jan 2008 5:40]
Guangbao Ni
pushed into mysql-5.1-ndb-bj
[10 Jan 2008 9:39]
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/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
[1 Feb 2008 15:17]
Jon Stephens
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.
[2 Feb 2008 11:13]
Jon Stephens
Also documented fix for 5.1.23-ndb-6.2.11; left status unchanged.
[20 Feb 2008 16:04]
Bugs System
Pushed into 5.1.24-rc
[20 Feb 2008 16:04]
Bugs System
Pushed into 6.0.5-alpha
[20 Feb 2008 21:01]
Jon Stephens
Also documented for 5.1.24 and 6.0.5.
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`;