Debian Codegraph @

mysql-test/suite/ndb/t/ndb_alter_table3.test

raw
1 -- source include/have_multi_ndb.inc
2
3 --disable_warnings
4 DROP TABLE IF EXISTS t1;
5 --enable_warnings
6
7 connection server1;
8 create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) )
9 engine=ndb;
10 insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three');
11 create index c on t1(c);
12 connection server2;
13 show indexes from t1;
14 select * from t1 where c = 'two';
15 connection server1;
16 alter table t1 drop index c;
17 connection server2;
18 show indexes from t1;
19 select * from t1 where c = 'two';
20 connection server1;
21 drop table t1;
22
23 connection server1;
24 create table t3 (a int primary key) engine=ndbcluster;
25
26 connection server2;
27 insert into t3 values (1);
28
29 connection server1;
30 alter table t3 rename t4;
31
32 connection server2;
33 # with rbr the below will not work as the "alter" event
34 # explicitly invalidates the dictionary cache.
35 ## This should work as transaction is ongoing...
36 #delete from t3;
37 #insert into t3 values (1);
38
39 # This should fail as its a new transaction
40 --error 1146
41 select * from t3;
42 select * from t4;
43 drop table t4;
44 show tables;
45 connection server1;
46
47 --disable_warnings
48 drop table if exists t6;
49 --enable_warnings
50 create table t6 (a int primary key ,b int) engine=ndbcluster;
51 insert into t6 values(1,12), (2,12);
52 --error 1169
53 alter table t6 add unique key ui_t(b);
54 delete from t6;
55 --disable_result_log
56 insert into t6 values(1,12), (2,12);
57 --enable_result_log
58 --error 1169
59 alter table t6 add unique key ui_t(b);
60 delete from t6;
61 drop table t6;
62
63 --echo #
64 --echo # Bug#22173891 : CIRCULAR FOREIGN KEYS + INDEX = FAILURE
65 --echo #
66
67 --echo #create table t1
68 create table t1(
69 a int primary key,
70 b int unique key
71 )engine = ndb;
72
73 --echo #create table t2 with fks referring columns from t1
74 create table t2(
75 a int,
76 b int,
77 c int unique key,
78 constraint fk1 foreign key (a) references t1(a),
79 constraint fk2 foreign key (b) references t1(b)
80 )engine = ndb;
81
82 --echo #t2 now has additional keys on col `a` and `b` to support fks.
83 --disable_warnings
84 show create table t2;
85 --enable_warnings
86
87 --echo #create an index on t2(a) using 'create index'
88 create index id1 on t2(a);
89
90 --echo #verify that the implicit key on col `a` is gone now and fk is intact.
91 --disable_warnings
92 show create table t2;
93 --enable_warnings
94 insert into t1 values (1,10), (2,20), (3,30), (4,40);
95 insert into t2 values (1,10,100), (2,20,200);
96 # Mask the column names and fk names, as the following query violates
97 # multiple FK constraints and the fk getting printed in the error might
98 # vary based on which FK trigger gets executed first.
99 --replace_regex /fk1/fk_name/ /fk2/fk_name/ /`a`/`col_name`/ /`b`/`col_name`/
100 --error ER_NO_REFERENCED_ROW_2
101 insert into t2 values (5,50,500);
102
103 --echo #create a table t3 with an fk refering to t2(c)
104 create table t3(
105 a int,
106 constraint fk3 foreign key (a) references t2(c)
107 )engine = ndb;
108
109 --echo #create an index on col `b` on t2
110 create unique index id2 on t2(b);
111
112 --echo #verify that the implicit key on col `b` is also gone now.
113 --disable_warnings
114 show create table t2;
115 --enable_warnings
116
117 --echo #verify that the fks are intact in t2 and t3
118 insert into t2 values (3,30,300), (4,40,400);
119 # Mask the column names and fk names, as the following query violates
120 # multiple FK constraints and the fk getting printed in the error might
121 # vary based on which FK trigger gets executed first.
122 --replace_regex /fk1/fk_name/ /fk2/fk_name/ /`a`/`col_name`/ /`b`/`col_name`/
123 --error ER_NO_REFERENCED_ROW_2
124 insert into t2 values (5,50,500);
125 insert into t3 values (100), (200);
126 --error ER_NO_REFERENCED_ROW_2
127 insert into t3 values (500);
128
129 --echo #cleanup
130 drop table t3, t2, t1;
131
132 # end of tests