Re: creating table engine fails with - Mikael Ronström - com.mysql.lists.cluster

9 messages in com.mysql.lists.clusterRe: creating table engine fails with

From	Sent On	Attachments
John Stile	26 Oct 2004 12:36
John Stile	26 Oct 2004 13:25
Mikael Ronström	27 Oct 2004 00:31
John Stile	27 Oct 2004 01:53
Mikael Ronström	27 Oct 2004 02:12
pek...@mysql.com	27 Oct 2004 02:24
John Stile	27 Oct 2004 03:41
pek...@mysql.com	27 Oct 2004 03:48
Ted Schundler	09 Nov 2004 17:34

Subject:	Re: creating table engine fails with
From:	Mikael Ronström (mik...@mysql.com)
Date:	10/27/2004 12:31:03 AM
List:	com.mysql.lists.cluster

Hi John,

2004-10-26 kl. 21.37 skrev John Stile:

Using mysql-1.4.6-gamma on Debian stable, 2GB RAM, with dual 2.4G Xeon, running MGM, NDB, and API with 2 replicas.

Creating a table as type myisam works, but of type ndbcluster fails. I need some help to understand this.

create table test1 (a varchar(255),b longblob, PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=MyISAM;

Query OK, 0 rows affected (0.03 sec)

create table test2 (a varchar(255),b longblob, PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=ndbcluster;

ERROR 1005 (HY000): Can't create table './yourpassionconsultant_com_messenger/test2.frm' (errno: 738)

Haven't checked up on the reasons of the error but I must ask the question why you want to have a unique key on the primary key attribute. Essentially every Primary key is a hash index on the real table which ensures uniqueness and an ordered index (for the NDB storage engine). Adding a unique index on the same attribute simply adds lots of memory and processing time to keep things up-to-date providing no benefits.

The cause of the problem is most likely a naming conflict somewhere.

Rgrds Mikael

I can create tables without a problem, using engine=ndb like this: create table t1 (a int, b longblob, PRIMARY KEY (`a`)) engine=ndb; create table t2 (a int, b longblob, PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=ndb; create table t3 (a int, b longblob, PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=ndb; create table t4 (a char, b longblob, PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=ndb; create table t5 (a varchar(255), PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=ndb; create table t6 (a char, b longblob, PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=ndb; create table t7 (a varchar(255),b MEDIUMBLOB,PRIMARY KEY (`a`), UNIQUE KEY `PRI` (`a`)) engine=ndb;

my config.ini [DB DEFAULT] NoOfReplicas: 2 DataMemory:800M IndexMemory:700M MaxNoOfConcurrentTransactions:75 MaxNoOfConcurrentOperations:120000 TimeBetweenWatchDogCheck:2000 MaxNoOfOrderedIndexes:50000 MaxNoOfUniqueHashIndexes:8000 MaxNoOfTables:10000 MaxNoOfAttributes:40000 LockPagesInMainMemory: N StopOnError: Y

Mikael Ronström, Senior Software Architect MySQL AB, www.mysql.com

Clustering: http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html

http://www.eweek.com/article2/0,1759,1567546,00.asp