MySQL Bugs: #40709: weird schema created with partition by key + mysqld core on insert

Bug #40709	weird schema created with partition by key + mysqld core on insert
Submitted:	13 Nov 2008 17:31	Modified:	19 May 2009 19:15
Reporter:	Tomas Ulin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	*	OS:	Any
Assigned to:	Jonas Oreland	CPU Architecture:	Any

Description:
master> create table t1 (a int, b int, c int) engine ndb partition by key(a);
Query OK, 0 rows affected (0.71 sec)

master> insert into t1 values (1,1,1);
ERROR 2013 (HY000): Lost connection to MySQL server during query

ndb_desc ->
-- Attributes -- 
a Int NULL DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Int NULL AT=FIXED ST=MEMORY
c Int NULL AT=FIXED ST=MEMORY
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR

How to repeat:
.

R2: if just refusing to create that table

Marked bug#41332 as a duplicate of this.

The test parts.partition_mgm_lc[0-2]_ndb is affected by this bug. (see bug#41332)
(there is a workaround to get the test working for NDB by adding a key.)

This statement works for MyISAM and InnoDB.

It seams like the problem is in how the hash of the columns in the partitioning function is computed.

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

2922 Jonas Oreland	2009-05-14
      ndb - bug#40709 - refuse to create tables with distribution key on none primary key

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

2924 Jonas Oreland	2009-05-15
      ndb - bug#40709
        Yet another fix, this time in ha_ndbcluster
        make sure that fields in partioning function
        are correctly marked using setPartitionKey(TRUE)

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

2925 Jonas Oreland	2009-05-15
      ndb - bug#40709
        Update a surprisingly large no of testcases that due to missing check in
        ha_ndbcluster run code which is not handled by ndb (and not supported)

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090515140051-i7gcqzrg3bhashsc) (version source revid:jonas@mysql.com-20090515140051-i7gcqzrg3bhashsc) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090516082643-pmyntkq0z3xw32vj) (version source revid:jonas@mysql.com-20090516082643-pmyntkq0z3xw32vj) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090516092717-2dmjwotq7xflffvg) (version source revid:jonas@mysql.com-20090516092717-2dmjwotq7xflffvg) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)

docs: ha_ndbcluster now prevents the creation of tables with partition-function/columns not being part of primary key.
4327 'Distribution key is only supported on part of primary
key' from NDB

---

Currently here is to my knowledge no plans of lifting this limitation,
although it is possible.

Documented bugfix and behaviour change in the NDB-6.2.18, 6.3.25, and 7.0.6 changelogs as follows:

        User-defined partitioning of an NDBCLUSTER table without any
        primary key sometimes failed, and could cause mysqld to crash

        Now, if you wish to create an NDBCLUSTER table with user-defined
        partitioning, the table must have an explicit primary key, and
        all columns listed in the partitioning expression must be
        part of the primary key. The hidden primary key used by the
        NDBCLUSTER storage engine is not sufficient for this purpose.
        However, if the list of columns is empty (that is, the table is
        defined using PARTITION BY KEY()), then no explicit primary key
        is required.

        This change does not effect the partitioning of tables using any
        storage engine other than NDBCLUSTER.

Also updated mysql-cluster-limitations-syntax and partitioning-limitations-storage-engines sections of the 5.1 Manual.