Bug #28347 Partitioning fails with composite primary key having VARCHAR field
Submitted: 10 May 2007 9:05 Modified: 25 Apr 2008 8:23
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.1.17 OS:Any
Assigned to: Jonas Oreland CPU Architecture:Any
Tags: ndb, partitioning

[10 May 2007 9:05] Geert Vanderkelen 
Description:
Creating a NDB table with a partition key set to the first field of the primary key consisting of a VARCHAR and another field, fails.

It works when using BINARY or VARBINARY, just like the error message says to do. Other 'workaround' is to just use PARTITION BY KEY () to get the default behavior or specifying all columns in the KEY () part.

How to repeat:
-- This fails:
CREATE TABLE `TableB` (
`keyName` varchar(64) NOT NULL,
`col1` int(5) unsigned NOT NULL,
`col2` char(15) NOT NULL,
`col3` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`keyName`, `col1`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY (`keyName`);

-- This works:
CREATE TABLE `TableB` (
`keyName` varbinary(64) NOT NULL,
`col1` int(5) unsigned NOT NULL,
`col2` char(15) NOT NULL,
`col3` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`keyName`, `col1`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY (`keyName`);

-- This works as well:
CREATE TABLE `TableB` (
`keyName` varchar(64) NOT NULL,
`col1` int(5) unsigned NOT NULL,
`col2` char(15) NOT NULL,
`col3` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`keyName`, `col1`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY (`keyName`,`col1`);
[10 May 2007 9:06] Geert Vanderkelen 
Verified using 5.1.17.
[11 Aug 2007 10:41] Geert Vanderkelen 
Fixed as of CGE 6.2.3.
Still a problem in latest 5.1bk (5.1.22).
[22 Feb 2008 14:09] Jonas Oreland 
Fix

Attachment: 28347.patch (text/x-patch), 1.21 KiB.
[22 Feb 2008 14:10] Jonas Oreland 
this fix (for drop6)
make it possible to create table,
but disables the "smart scan" feature for such table.
[27 Feb 2008 13:45] 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/43055

ChangeSet@1.2192, 2008-02-27 14:45:29+01:00, jonas@perch.ndb.mysql.com +2 -0
  ndb - bug#28347
    backport to drop6
    allow create table of tables that we don't support partition pruning on
[25 Apr 2008 8:23] Jonas Oreland 
already fixed in 5.1, and delivered in drop6,
closing