Debian Codegraph @

mysql-test/suite/ndb/r/ndb_ddl_dml.result

raw
1 set @@global.ndb_schema_dist_lock_wait_timeout = 240;
2 call mtr.add_suppression(".*NDB_SHARE.*Moving away for safety, but possible memleak");
3 create table parent(a int primary key, b int, key(b)) engine=ndb
4 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
5 create table child(a int, b int, primary key(a,b), key(b)) engine=ndb
6 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
7 insert into parent values (1,1), (2,2), (3,3), (4,4);
8 insert into parent select a+4, b+4 from parent;
9 insert into parent select a+8, b+8 from parent;
10 insert into parent select a+16, b+16 from parent;
11 insert into parent select a+32, b+32 from parent;
12 insert into parent select a+64, b+64 from parent;
13 insert into parent select a+128, b+128 from parent;
14 insert into parent select a+256, b+256 from parent;
15 insert into parent select a+512, b+512 from parent;
16 insert into parent select a+1024, b+1024 from parent;
17 insert into parent select a+2048, b+2048 from parent;
18 insert into parent select a+4096, b+4096 from parent;
19 insert into child select * from parent;
20 analyze table parent, child;
21 Table Op Msg_type Msg_text
22 test.parent analyze status OK
23 test.child analyze status OK
24 set ndb_join_pushdown = off;
25 explain
26 select straight_join count(*)
27 from parent join child
28 on child.a = parent.a and child.b = parent.b
29 where parent.b > 5;
30 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
31 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 5 NULL ### ### Using pushed condition ((`test`.`parent`.`b` > 5) and (`test`.`parent`.`b` is not null)); Using MRR
32 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### NULL
33 Warnings:
34 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
35 explain
36 select straight_join count(*)
37 from parent join child
38 on child.a = parent.a
39 where parent.b > 5;
40 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
41 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 5 NULL ### ### Using pushed condition (`test`.`parent`.`b` > 5); Using MRR
42 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 ref PRIMARY PRIMARY 4 test.parent.a ### ### NULL
43 Warnings:
44 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
45 explain
46 select straight_join count(*)
47 from parent join child
48 on child.b = parent.b
49 where parent.b > 5;
50 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
51 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range b b 5 NULL ### ### Using pushed condition ((`test`.`parent`.`b` > 5) and (`test`.`parent`.`b` is not null)); Using MRR
52 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 ref b b 4 test.parent.b ### ### NULL
53 Warnings:
54 Note 1003 /* select#1 */ select straight_join count(0) AS `count(*)` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`parent`.`b` > 5))
55 show variables like 'server_id';
56 Variable_name Value
57 server_id 1
58 create procedure query_eq_ref_load (seconds int)
59 begin
60 set @x=time_to_sec(current_time()) + seconds;
61 repeat
62 set ndb_join_pushdown = off;
63 select straight_join count(*)
64 from parent join child
65 on child.a = parent.a and child.b = parent.b
66 where parent.b > 5;
67 until @x <= time_to_sec(current_time())
68 end repeat;
69 end%
70 create procedure query_ref_load (seconds int)
71 begin
72 set @x=time_to_sec(current_time()) + seconds;
73 repeat
74 set ndb_join_pushdown = off;
75 select straight_join count(*)
76 from parent join child
77 on child.a = parent.a and child.b = parent.b
78 where parent.b > 5;
79 until @x <= time_to_sec(current_time())
80 end repeat;
81 end%
82 create procedure query_ref_b_load (seconds int)
83 begin
84 set @x=time_to_sec(current_time()) + seconds;
85 repeat
86 set ndb_join_pushdown = off;
87 select straight_join count(*)
88 from parent join child
89 on child.a = parent.a and child.b = parent.b
90 where parent.b > 5;
91 until @x <= time_to_sec(current_time())
92 end repeat;
93 end%
94 Starting background DML load
95 call query_eq_ref_load(10);
96 call query_ref_load(10);
97 call query_ref_b_load(10);
98 show variables like 'server_id';
99 Variable_name Value
100 server_id 2
101 Starting concurrent DDL operations
102 alter table child add column c int;
103 Warnings:
104 Warning 1478 Converted FIXED field 'c' to DYNAMIC to enable online ADD COLUMN
105 alter table child drop column c;
106 alter table child drop primary key;
107 alter table child add primary key(b);
108 alter table child drop primary key;
109 alter table child add primary key(a,b) using hash;
110 alter table child drop primary key;
111 alter table child add primary key(a,b);
112 Completed DDL operations, rejoins with DML load.
113 ====================================================
114 Force a 'trailing share' to be created in order to
115 check correct cleanup in ndbcluster_real_free_share()
116 =====================================================
117 set ndb_join_pushdown = off;
118 explain select straight_join *
119 from parent join child
120 on child.a = parent.a and child.b = parent.b
121 where parent.b > 5;
122 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
123 1 SIMPLE parent p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY,b b 5 NULL ### ### Using pushed condition ((`test`.`parent`.`b` > 5) and (`test`.`parent`.`b` is not null)); Using MRR
124 1 SIMPLE child p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY,b PRIMARY 8 test.parent.a,test.parent.b ### ### NULL
125 Warnings:
126 Note 1003 /* select#1 */ select straight_join `test`.`parent`.`a` AS `a`,`test`.`parent`.`b` AS `b`,`test`.`child`.`a` AS `a`,`test`.`child`.`b` AS `b` from `test`.`parent` join `test`.`child` where ((`test`.`child`.`b` = `test`.`parent`.`b`) and (`test`.`child`.`a` = `test`.`parent`.`a`) and (`test`.`parent`.`b` > 5))
127 alter table child algorithm=copy, drop primary key;
128 commit;
129 drop table child;
130 drop table parent;
131 drop procedure query_eq_ref_load;
132 drop procedure query_ref_load;
133 drop procedure query_ref_b_load;
134 set @@global.ndb_schema_dist_lock_wait_timeout = 30;