1
-- source include/have_ndb.inc
2
-- source include/not_embedded.inc
5
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
6
drop database if exists mysqltest;
9
# workaround for bug#16445
10
# remove to reproduce bug and run tests from ndb start
11
# and with ndb_autodiscover disabled. Fails on Linux 50 % of the times
13
pk1 INT NOT NULL PRIMARY KEY,
21
# Basic test to show that the NDB
22
# table handler is working
26
# Show status and variables
29
SHOW GLOBAL STATUS LIKE 'ndb%';
31
SHOW GLOBAL VARIABLES LIKE 'ndb%';
34
# Create a normal table with primary key
37
pk1 INT NOT NULL PRIMARY KEY,
44
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');
46
SELECT pk1 FROM t1 ORDER BY pk1;
47
SELECT * FROM t1 ORDER BY pk1;
48
SELECT t1.* FROM t1 ORDER BY pk1;
50
# Update on record by primary key
51
UPDATE t1 SET attr1=1 WHERE pk1=9410;
52
SELECT * FROM t1 ORDER BY pk1;
55
UPDATE t1 SET pk1=2 WHERE attr1=1;
56
SELECT * FROM t1 ORDER BY pk1;
57
UPDATE t1 SET pk1=pk1 + 1;
58
SELECT * FROM t1 ORDER BY pk1;
59
UPDATE t1 SET pk1=4 WHERE pk1 = 3;
60
SELECT * FROM t1 ORDER BY pk1;
66
# Insert more records and update them all at once
67
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9408, 8765, NULL, '8765'),
68
(7,8, NULL, NULL), (8,9, NULL, NULL), (9,10, NULL, NULL), (10,11, NULL, NULL), (11,12, NULL, NULL), (12,13, NULL, NULL), (13,14, NULL, NULL);
69
UPDATE t1 SET attr1 = 9999;
70
SELECT * FROM t1 ORDER BY pk1;
72
UPDATE t1 SET attr1 = 9998 WHERE pk1 < 1000;
73
SELECT * FROM t1 ORDER BY pk1;
75
UPDATE t1 SET attr1 = 9997 WHERE attr1 = 9999;
76
SELECT * FROM t1 ORDER BY pk1;
78
# Delete one record by specifying pk
79
DELETE FROM t1 WHERE pk1 = 9410;
80
SELECT * FROM t1 ORDER BY pk1;
82
# Delete all from table
86
# Insert three records with attr1=4 and two with attr1=5
87
# Delete all with attr1=4
88
INSERT INTO t1 values (1, 4, NULL, NULL), (2, 4, NULL, NULL), (3, 5, NULL, NULL), (4, 4, NULL, NULL), (5, 5, NULL, NULL);
89
DELETE FROM t1 WHERE attr1=4;
90
SELECT * FROM t1 order by pk1;
93
# Insert two records and delete one
94
INSERT INTO t1 VALUES (9410,9412, NULL, NULL), (9411, 9413, NULL, NULL);
95
DELETE FROM t1 WHERE pk1 = 9410;
100
# Create table without primary key
101
# a hidden primary key column is created by handler
103
CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster;
104
INSERT INTO t1 values(3456, 7890);
106
UPDATE t1 SET id=2 WHERE id2=12;
108
UPDATE t1 SET id=1234 WHERE id2=7890;
112
INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890), (3454, 7890);
113
SELECT * FROM t1 ORDER BY id;
114
DELETE FROM t1 WHERE id = 3456;
115
SELECT * FROM t1 ORDER BY id;
119
# test create with the keyword "engine=NDBCLUSTER"
121
pk1 INT NOT NULL PRIMARY KEY,
125
INSERT INTO t1 values(1, 9999);
129
# test create with the keyword "engine=NDB"
131
pk1 INT NOT NULL PRIMARY KEY,
135
INSERT INTO t1 values(1, 9999);
141
# A more extensive test with a lot more records
145
a bigint unsigned NOT NULL PRIMARY KEY,
146
b int unsigned not null,
151
a bigint unsigned NOT NULL,
152
b bigint unsigned not null,
158
a bigint unsigned NOT NULL,
159
b bigint unsigned not null,
160
c bigint unsigned NOT NULL,
167
# insert more records into tables
173
eval insert into t2 values($1, $1+9, 5);
174
eval insert into t3 values($1, $1+9, 5);
175
eval insert into t4 values($1, $1+9, 5, $1+26000);
182
# delete every other record in the tables
188
eval delete from t2 where a=$1;
189
eval delete from t3 where a=$1;
190
eval delete from t4 where a=$1 and b=$1+9 and c=5;
197
select * from t2 where a = 7 order by b;
198
select * from t2 where a = 7 order by a;
199
select * from t2 where a = 7 order by 2;
200
select * from t2 where a = 7 order by c;
202
select * from t2 where a = 7 and b = 16 order by b;
203
select * from t2 where a = 7 and b = 16 order by a;
204
select * from t2 where a = 7 and b = 17 order by a;
205
select * from t2 where a = 7 and b != 16 order by b;
207
select * from t2 where a = 7 and b = 16 and c = 5 order by b;
208
select * from t2 where a = 7 and b = 16 and c = 5 order by a;
209
select * from t2 where a = 7 and b = 16 and c = 6 order by a;
210
select * from t2 where a = 7 and b != 16 and c = 5 order by b;
212
select * from t3 where a = 7 order by b;
213
select * from t3 where a = 7 order by a;
214
select * from t3 where a = 7 order by 2;
215
select * from t3 where a = 7 order by c;
217
select * from t3 where a = 7 and b = 16 order by b;
218
select * from t3 where a = 7 and b = 16 order by a;
219
select * from t3 where a = 7 and b = 17 order by a;
220
select * from t3 where a = 7 and b != 16 order by b;
222
select * from t4 where a = 7 order by b;
223
select * from t4 where a = 7 order by a;
224
select * from t4 where a = 7 order by 2;
225
select * from t4 where a = 7 order by c;
227
select * from t4 where a = 7 and b = 16 order by b;
228
select * from t4 where a = 7 and b = 16 order by a;
229
select * from t4 where a = 7 and b = 17 order by a;
230
select * from t4 where a = 7 and b != 16 order by b;
239
eval update t2 set c=$1 where a=$1;
240
eval update t3 set c=7 where a=$1 and b=$1+9 and c=5;
241
eval update t4 set d=$1+21987 where a=$1 and b=$1+9 and c=5;
247
delete from t2 where a > 5;
248
select x1.a, x1.b from t2 x1, t2 x2 where x1.b = x2.b order by x1.a;
249
select a, b FROM t2 outer_table where
250
a = (select a from t2 where b = outer_table.b ) order by a;
262
# Test delete and update from table with 3 keys
266
a bigint unsigned NOT NULL,
267
b bigint unsigned not null,
268
c bigint unsigned NOT NULL,
273
insert into t5 values(10, 19, 5, 26010);
275
delete from t5 where a=10 and b=19 and c=5;
279
insert into t5 values(10, 19, 5, 26010);
281
update t5 set d=21997 where a=10 and b=19 and c=5;
290
# Test using table with a char(255) column first in table
295
a int NOT NULL PRIMARY KEY,
299
insert into t6 values
300
("Nice road 3456", 1, 23),
301
("Street Road 78", 3, 92),
302
("Road street 89C", 5, 71),
304
select * from t6 order by a;
305
select a, b from t6 order by a;
307
update t6 set adress="End of road 09" where a=3;
308
update t6 set b=181, adress="Street 76" where a=7;
309
select * from t6 order by a;
310
select * from t6 where a=1;
311
delete from t6 where a=1;
312
select * from t6 order by a;
313
delete from t6 where b=71;
314
select * from t6 order by a;
319
# Test using table with a char(255) column first in table and a
320
# primary key consisting of two columns
331
insert into t7 values
332
("Highway 3456", 1, 23, 2),
333
("Street Road 78", 3, 92, 3),
334
("Main street 89C", 5, 71, 4),
336
select * from t7 order by a;
337
select a, b from t7 order by a;
339
update t7 set adress="End of road 09" where a=3;
340
update t7 set adress="GatuvƤgen 90C" where a=5 and c=4;
341
update t7 set adress="No adress" where adress is NULL;
342
select * from t7 order by a;
343
select * from t7 where a=1 and c=2;
344
delete from t7 where a=1;
345
delete from t7 where a=3 and c=3;
346
delete from t7 where a=5 and c=4;
348
delete from t7 where b=23;
354
# Test multiple databases in one statement
358
pk1 INT NOT NULL PRIMARY KEY,
364
INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413');
366
create database mysqltest;
370
a bigint unsigned NOT NULL PRIMARY KEY,
371
b int unsigned not null,
375
insert into t2 select pk1,attr1,attr2 from test.t1;
376
select * from t2 order by a;
377
select b from test.t1, t2 where c = test.t1.attr2;
378
select b,test.t1.attr1 from test.t1, t2 where test.t1.pk1 < a;
380
drop table test.t1, t2;
381
drop database mysqltest;
384
# BUG#6031 - DROP DATABASE doesn't drop database on first try
388
drop database if exists ndbtest1;
391
create database ndbtest1;
393
create table t1(id int) engine=ndbcluster;
394
drop database ndbtest1;
396
drop database ndbtest1;
399
# test support of char(0)
403
create table t1 (a int primary key, b char(0));
404
insert into t1 values (1,"");
405
insert into t1 values (2,NULL);
406
select * from t1 order by a;
407
select * from t1 order by b;
408
select * from t1 where b IS NULL;
409
select * from t1 where b IS NOT NULL;
413
# test the limit of no of attributes in one table
415
# also tests bug#17179, more than 31 attributes in
416
# a partitioned table
547
primary key using hash(c1)) engine=ndb partition by key(c1);
551
# test max size of attribute name and truncation
555
a1234567890123456789012345678901234567890 int primary key,
556
a12345678901234567890123456789a1234567890 int,
557
index(a12345678901234567890123456789a1234567890)
560
insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2),(6,1),(7,1);
562
explain select * from t1 where a12345678901234567890123456789a1234567890=2;
563
select * from t1 where a12345678901234567890123456789a1234567890=2;
567
# test fragment creation
569
# first a table with _many_ fragments per node group
570
# then a table with just one fragment per node group
573
(a bigint, b bigint, c bigint, d bigint,
574
primary key (a,b,c,d))
577
insert into t1 values
578
(1,2,3,4),(2,3,4,5),(3,4,5,6),
579
(3,2,3,4),(1,3,4,5),(2,4,5,6),
580
(1,2,3,5),(2,3,4,8),(3,4,5,9),
581
(3,2,3,5),(1,3,4,8),(2,4,5,9),
582
(1,2,3,6),(2,3,4,6),(3,4,5,7),
583
(3,2,3,6),(1,3,4,6),(2,4,5,7),
584
(1,2,3,7),(2,3,4,7),(3,4,5,8),
585
(3,2,3,7),(1,3,4,7),(2,4,5,8),
586
(1,3,3,4),(2,4,4,5),(3,5,5,6),
587
(3,3,3,4),(1,4,4,5),(2,5,5,6),
588
(1,3,3,5),(2,4,4,8),(3,5,5,9),
589
(3,3,3,5),(1,4,4,8),(2,5,5,9),
590
(1,3,3,6),(2,4,4,6),(3,5,5,7),
591
(3,3,3,6),(1,4,4,6),(2,5,5,7),
592
(1,3,3,7),(2,4,4,7),(3,5,5,8),
593
(3,3,3,7),(1,4,4,7),(2,5,5,8);
594
select count(*) from t1;
598
(a bigint, b bigint, c bigint, d bigint,
605
# Test auto_increment
608
connect (con1,localhost,root,,test);
609
connect (con2,localhost,root,,test);
612
(counter int(64) NOT NULL auto_increment,
613
datavalue char(40) default 'XXXX',
614
primary key (counter)
618
insert into t1 (datavalue) values ('newval');
619
insert into t1 (datavalue) values ('newval');
620
select * from t1 order by counter;
621
insert into t1 (datavalue) select datavalue from t1 where counter < 100;
622
insert into t1 (datavalue) select datavalue from t1 where counter < 100;
623
select * from t1 order by counter;
625
insert into t1 (datavalue) select datavalue from t1 where counter < 100;
626
insert into t1 (datavalue) select datavalue from t1 where counter < 100;
627
select * from t1 order by counter;
634
create table t1 (a int primary key auto_increment) engine = ndb;
635
insert into t1() values (),(),(),(),(),(),(),(),(),(),(),();
637
insert into t1(a) values (20),(28);
639
insert into t1() values (),(),(),(),(),(),(),(),(),(),(),();
641
insert into t1() values (21), (22);
647
# BUG#14514 Creating table with packed key fails silently
650
CREATE TABLE t1 ( b INT ) PACK_KEYS = 0 ENGINE = ndb;
655
# Bug #17249 delete statement with join where clause fails
656
# when table do not have pk
659
create table t1 (a int) engine=ndb;
660
create table t2 (a int) engine=ndb;
661
insert into t1 values (1);
662
insert into t2 values (1);
663
delete t1.* from t1, t2 where t1.a = t2.a;
670
# Bug #17257 update fails for inner joins if tables
671
# do not have Primary Key
698
INSERT INTO t1 VALUES ( 1, 2,13,14,15);
699
INSERT INTO t2 VALUES ( 1, 3,23,24,25);
700
INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36);
706
ON a.j = c.j AND b.k = c.k
723
# Test long table name
725
create table atablewithareallylongandirritatingname (a int);
726
insert into atablewithareallylongandirritatingname values (2);
727
select * from atablewithareallylongandirritatingname;
728
drop table atablewithareallylongandirritatingname;
733
create table t1 (f1 varchar(50), f2 text,f3 int, primary key(f1)) engine=NDB;
734
insert into t1 (f1,f2,f3)VALUES("111111","aaaaaa",1);
735
insert into t1 (f1,f2,f3)VALUES("222222","bbbbbb",2);
736
select * from t1 order by f1;
737
select * from t1 order by f2;
738
select * from t1 order by f3;
740
# Bug#16561 Unknown ERROR msg "ERROR 1186 (HY000): Binlog closed" by perror
743
# As long there is no error code 1186 defined by NDB
744
# we should get a message "Illegal ndb error code: 1186"
746
--exec $MY_PERROR --ndb 1186 2>&1
749
# Bug #25746 - VARCHAR UTF8 PK issue
750
# - prior to bugfix 4209, illegal length parameter would be
751
# returned in SELECT *
754
a VARBINARY(40) NOT NULL,
755
b VARCHAR (256) CHARACTER SET UTF8 NOT NULL,
756
c VARCHAR(256) CHARACTER SET UTF8 NOT NULL,
757
PRIMARY KEY (b,c)) ENGINE=ndbcluster;
758
INSERT INTO t1 VALUES
759
("a","ab","abc"),("b","abc","abcd"),("c","abc","ab"),("d","ab","ab"),("e","abc","abc");
760
SELECT * FROM t1 ORDER BY a;
764
create table t1 (a int not null primary key, b int not null) engine=ndb;
765
create table t2 (a int not null primary key, b int not null) engine=ndb;
766
insert into t1 values (1,10), (2,20), (3,30);
767
insert into t2 values (1,10), (2,20), (3,30);
768
select * from t1 order by a;
769
delete from t1 where a > 0 order by a desc limit 1;
770
select * from t1 order by a;
771
delete from t1,t2 using t1,t2 where t1.a = t2.a;
772
select * from t2 order by a;
776
create table t1 (a int not null primary key, b int not null) engine=ndb;
777
insert into t1 values (1,10), (2,20), (3,30);
779
insert into t1 set a=1, b=100;
780
insert ignore into t1 set a=1, b=100;
781
select * from t1 order by a;
782
insert into t1 set a=1, b=1000 on duplicate key update b=b+1;
783
select * from t1 order by a;
787
create table t1 (a int not null primary key, b int not null) engine=ndb;
788
create table t2 (c int not null primary key, d int not null) engine=ndb;
789
insert into t1 values (1,10), (2,10), (3,30), (4, 30);
790
insert into t2 values (1,10), (2,10), (3,30), (4, 30);
792
update t1 set a = 1 where a = 3;
793
select * from t1 order by a;
794
update t1 set b = 1 where a > 1 order by a desc limit 1;
795
select * from t1 order by a;
797
update t1,t2 set a = 1, c = 1 where a = 3 and c = 3;
798
select * from t1 order by a;
799
update ignore t1,t2 set a = 1, c = 1 where a = 3 and c = 3;
800
select * from t1 order by a;
806
create table t1 (a varchar(100) primary key, b varchar(100)) engine = NDB;
807
insert into t1 values
808
('a', 'a'),('b','b'),('c', 'c'),('aa', 'aa'),('bb', 'bb'),('cc', 'cc');
809
replace into t1 values ('a', '-a');
810
replace into t1 values ('b', '-b');
811
replace into t1 values ('c', '-c');
813
replace into t1 values ('aa', '-aa');
814
replace into t1 values ('bb', '-bb');
815
replace into t1 values ('cc', '-cc');
817
replace into t1 values ('aaa', '-aaa');
818
replace into t1 values ('bbb', '-bbb');
819
replace into t1 values ('ccc', '-ccc');
820
select * from t1 order by 1,2;
823
--echo End of 5.0 tests
826
# Bug #18483 Cannot create table with FK constraint
827
# ndb does not support foreign key constraint, it is silently ignored
828
# in line with other storage engines
830
CREATE TABLE t1 (a VARCHAR(255) NOT NULL,
831
CONSTRAINT pk_a PRIMARY KEY (a))engine=ndb;
832
CREATE TABLE t2(a VARCHAR(255) NOT NULL,
833
b VARCHAR(255) NOT NULL,
834
c VARCHAR(255) NOT NULL,
835
CONSTRAINT pk_b_c_id PRIMARY KEY (b,c),
836
CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb;
840
create table t1 (a int not null primary key, b int) engine=ndb;
841
insert into t1 values(1,1),(2,2),(3,3);
842
create table t2 like t1;
843
insert into t2 select * from t1;
844
select * from t1 order by a;
845
select * from t2 order by a;
848
# create table if not exists
850
create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb;
851
create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb;
855
create table t2 like t1;
858
rename table t1 to t10, t2 to t20;
861
--echo End of 5.1 tests