Bug #7396 Primary Key not working in NDB Mysql Clustered table

• Submitted: 18 Dec 2004 0:48
• Modified: 22 Dec 2004 4:58
• Reporter: Todd Carter
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.7
• OS: Solaris (Solaris 2.8)
• Assigned to: Jonas Oreland
• CPU Architecture: Any

[18 Dec 2004 0:48] Todd Carter

Description:
When I create a table with a primary key using ENGINE=NDB the table will not allow inserts. After doing some troubleshooting and build the same schema (create table command) using the myisam engine the insert queries work just fine.

Is there a known issue with primary key's and mysql clustering ?

Any help around this area would be greatly appreciated.

How to repeat:
Run these commands:

mysql> create table tri (i int) engine=ndbcluster;
Query OK, 0 rows affected (0.52 sec)

mysql> show create table tri;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+--------------------------------------------------------------------------------------------+
| tri   | CREATE TABLE `tri` (
  `i` int(11) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------+
ALTER TABLE tri ADD PRIMARY KEY ( `i` );
Query OK, 0 rows affected (1.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tri (i) values (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> ALTER TABLE tri DROP PRIMARY KEY;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 0 rows affected (1.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tri (i) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tri;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

mysql> show create table tri;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                       |
+-------+----------------------------------------------------------------------------------------------------+
| tri   | CREATE TABLE `tri` (
  `i` int(11) NOT NULL default '0'
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[18 Dec 2004 8:14] Jonas Oreland

As a workaround:
You could always use this syntax instead.

>create table t (i int primary key) engine = ndb;

instead of adding a primary key via alter table

[18 Dec 2004 9:31] Jonas Oreland

Hi,

I could not reproduce this with 4.1.8 (but on linux)
Could you retry the test with newly released 4.1.8 and
  see if the problem still remains.

[22 Dec 2004 1:19] Todd Carter

It is fixed in 4.1.8 snap shot from 12-12-2004. I went back to previous snap shots to see when it got fixed and the first version is that one.

Thank you for your help.