ndb_partition_key.test 5.18 KB
Newer Older
jmiller@mysql.com's avatar
jmiller@mysql.com committed
1
-- source include/have_ndb.inc
2
-- source include/have_binlog_format_row.inc
jmiller@mysql.com's avatar
jmiller@mysql.com committed
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

#
# Basic syntax test
#

# Support for partition key verified
CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b,c))
  ENGINE = NDB
  PARTITION BY KEY (a,b);

insert into t1 values (1,1,1,1);
select * from t1;
update t1 set d = 2 where a = 1 and b = 1 and c = 1;
select * from t1;
delete from t1;
select * from t1;

drop table t1;

# only support for partition key on primary key
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b))
  ENGINE = NDB
  PARTITION BY KEY (c);

CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY(a,b))
  ENGINE = NDB
  PARTITION BY KEY (a);

insert into t1 values 
       (1,1,3),(1,2,3),(1,3,3),(1,4,3),(1,5,3),(1,6,3),
       (1,7,3),(1,8,3),(1,9,3),(1,10,3),(1,11,3),(1,12,3);

select * from t1 order by b;

DROP TABLE t1;

#
# Test partition and char support
#

CREATE TABLE t1 (a INT, b CHAR(10) COLLATE latin1_bin, c INT, d INT,
                 PRIMARY KEY (a,b,c) USING HASH)
                 ENGINE=NDB
                 DEFAULT CHARSET=latin1
                 PARTITION BY KEY (b);

insert into t1 values (1,"a",1,1),(2,"a",1,1),(3,"a",1,1);

# should show only one attribute with DISTRIBUTION KEY
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | sed 's/Version: [0-9]*//' | sed 's/\(Length of frm data: \)[0-9]*/\1#/'

#
# Test that explicit partition info is not shown in show create table
# result should not contain (PARTITION P0 ... etc) since this is what shows up in
# mysqldump, and we don't want that info there
#
show create table t1;

DROP TABLE t1;

#
# Bug #13155: Problem in Create Table using SHOW CREATE TABLE syntax
#
CREATE TABLE t1 (a int not null primary key)
PARTITION BY KEY(a)
(PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);

drop table t1;

CREATE TABLE t1 (a int not null primary key);
ALTER TABLE t1
PARTITION BY KEY(a)
(PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);

drop table t1;

#
# Bug #17754 Improper handling of removal of partitioning in ALTER TABLE
# Also added a number of general test cases in the same area
#
create table t1 (a int)
engine=ndb
partition by key(a)
(partition p0, partition p1);
show create table t1;

alter table t1 engine=heap;
show create table t1;

alter table t1 engine=ndb;
show create table t1;

alter table t1 engine=heap remove partitioning;
show create table t1;

alter table t1 engine=ndb
partition by key(a)
(partition p0, partition p1 engine = ndb);
show create table t1;

alter table t1
partition by key (a)
(partition p0 engine=ndb, partition p1 engine=ndb);
show create table t1;

alter table t1 remove partitioning;
show create table t1;

--error ER_MIX_HANDLER_ERROR
alter table t1
partition by key(a)
(partition p0 engine=ndb, partition p1);

alter table t1
engine=ndb
partition by key(a)
(partition p0 engine=ndb, partition p1 engine = ndb);
show create table t1;

drop table t1;

#
# BUG 16810 Out of memory when coalesce partition
#
CREATE TABLE t1 (
  c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
  c2 TEXT NOT NULL,
  c3 INT NOT NULL,
  c4 BIT NOT NULL,
  c5 FLOAT,
  c6 VARCHAR(255),
  c7 TIMESTAMP,
  PRIMARY KEY(c1,c3))
  ENGINE=NDB
  PARTITION BY KEY(c3) PARTITIONS 5;

let $j= 11;
--disable_query_log
while ($j)
{
  eval INSERT INTO t1 VALUES (NULL, "Tested Remotely from Texas, USA", $j,
b'0',
                                   $j.00,"By JBM $j","2006-01-26");
  dec $j;
}
--enable_query_log
ALTER TABLE t1 COALESCE PARTITION 4;

DROP TABLE t1;

#
# Bug 16822: OPTIMIZE TABLE hangs test
#
CREATE TABLE t1 (a int primary key)
ENGINE=NDB
PARTITION BY KEY(a);
--error 1031
ALTER TABLE t1 OPTIMIZE PARTITION p0;
--error 1031
ALTER TABLE t1 CHECK PARTITION p0;
--error 1031
ALTER TABLE t1 REPAIR PARTITION p0;
--error 1031
ALTER TABLE t1 ANALYZE PARTITION p0;
--error 1031
ALTER TABLE t1 REBUILD PARTITION p0;
DROP TABLE t1;

#
# BUG 16806: ALTER TABLE fails
#
CREATE TABLE t1 (
  c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
  c2 TEXT NOT NULL,
  c3 INT NOT NULL,
  PRIMARY KEY(c1,c3))
  ENGINE=NDB
  PARTITION BY KEY(c3) PARTITIONS 5;

ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
DROP TABLE t1;

CREATE TABLE t1 (
  c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
  c2 TEXT NOT NULL,
  c3 INT NOT NULL,
  PRIMARY KEY(c1,c3))
  ENGINE=NDB
196 197
  PARTITION BY KEY(c3)
  (PARTITION p0 NODEGROUP 0, PARTITION p1 NODEGROUP 0);
jmiller@mysql.com's avatar
jmiller@mysql.com committed
198 199

ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
200 201
SELECT NODEGROUP,PARTITION_NAME FROM information_schema.partitions WHERE
table_name = "t1";
jmiller@mysql.com's avatar
jmiller@mysql.com committed
202
DROP TABLE t1;
jonas@perch.ndb.mysql.com's avatar
jonas@perch.ndb.mysql.com committed
203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230

# bug#25587

CREATE TABLE t1 (
a tinyint unsigned NOT NULL,
b bigint(20) unsigned NOT NULL,
c char(12),
PRIMARY KEY (a,b)
) ENGINE ndb DEFAULT CHARSET=latin1 PARTITION BY KEY (a);

insert into t1 values(1,1,'1'), (2,2,'2'), (3,3,'3'), (4,4,'4'), (5,5,'5');
select * from t1 where a = 1;
select * from t1 where a = 2;
select * from t1 where a = 3;
select * from t1 where a = 4;
select * from t1 where a = 5;
delete from t1 where a = 1;
select * from t1 order by 1;
delete from t1 where a = 2;
select * from t1 order by 1;
delete from t1 where a = 3;
select * from t1 order by 1;
delete from t1 where a = 4;
select * from t1 order by 1;
delete from t1 where a = 5;
select * from t1 order by 1;

drop table t1;