MySQL Bugs: #36735: disable option 'new' can break partitioned ndb tables

Bug #36735	disable option 'new' can break partitioned ndb tables
Submitted:	15 May 2008 11:53	Modified:	1 Nov 2009 20:12
Reporter:	Mattias Jonsson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	disabled

Description:
In 5.1.12 hash, range and list partitioning was disabled from ndb, but were still usable if using 'new' option.

This means that one can create a table with 'new' set and with hash/range/list partitioning ndb and after disable 'new' it is impossible to add a column.

This was probably introduced in Bug#20077.

This use of 'new' option is also not documented, from http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#option_mysqld_new 

This variable was used in MySQL 4.0 to turn on some 4.1 behaviors, and is retained for backward compatibility. In MySQL 5.1, its value is always OFF.

How to repeat:
(Tested with latest mysql-5.1-bugteam)
mysqltest: At line 8: query 'alter table t1 add (b int)' failed: 1005: Can't create table 'test.#sql-529f_3' (errno: 138)

The result from queries just before the failure was:
set new=on;
create table t1 (a int)
engine=ndb
partition by hash(a) partitions 2;
set new=off;
alter table t1 add (b int);

More results from queries before failure can be found in /Users/mattiasj/clones/tests_2-51-bugteam/mysql-test/var/log/mj_ndb_test.log

Warnings from just before the error:
Error 1478 Table storage engine 'ndbcluster' does not support the create option 'LIST, RANGE and HASH partition disabled by default, use --new op'

Suggested fix:
Either disable list, range and hash partitioning from ndb completely or always allow it...

Here is another test that fails when using option 'new' (range partitioning and alter delete rows, it works on myisam):
set new=on;
create table t1 (a int)
engine=ndb
partition by range (a)
(partition p0 values less than (15),
partition p1 values less than maxvalue);
insert into t1 values (13), (15);
select * from t1;
a
13
15
alter table t1 reorganize partition p0,p1 into (partition s1 values less than maxvalue);
select * from t1;
a
13
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION s1 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster) */
drop table t1;

Here is another test case that shows problem with range partitioning ndb tables (possible to insert and select values outside specified partition):
set new=on;
CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1)
SUBPARTITION BY HASH(f_int1)
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11 STORAGE ENGINE = 'ndbcluster',
SUBPARTITION subpart12 STORAGE ENGINE = 'ndbcluster'));
insert into t1 set f_int1 = 2147483647, f_int2 = 2147483647, f_charbig = '#2147483647##';
insert into t1 values (2147483647, 2147483647, '2147483647', '2147483647', '#2147483647##');
select * from t1;
f_int1  f_int2  f_char1 f_char2 f_charbig
2147483647      2147483647      NULL    NULL    #2147483647##
2147483647      2147483647      2147483647      2147483647      #2147483647##
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = ndbcluster, SUBPARTITION subpart12 ENGINE = ndbcluster)) */
drop table t1;

Related to bug#18735.

What feedback is needed?

The issue is still, that one can enable features with --new (or 'set new=on') which is not documented and I cannot find if it is supported or not (and if not, why the ability to enable it?)

It would be good if user defined partitioning was documented and supported in NDB.