· 8 years ago · May 21, 2018, 12:02 AM
1## Reproduction steps
2
31. Create a database using the following DDL and use it as default:
4
5```sql
6CREATE DATABASE toku_issue
7DEFAULT CHARACTER SET 'latin1'
8DEFAULT COLLATE 'latin1_swedish_ci';
9USE toku_issue;
10```
11
122. Create some tables and fill it with data:
13
14```sql
15DROP TABLE IF EXISTS `test_raw_data`;
16CREATE TABLE `test_raw_data` (
17 `PERIOD_START_TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
18 `DATEDAY` date NOT NULL DEFAULT '0000-00-00',
19 `PERIOD_DURATION` int(11) DEFAULT NULL,
20 `DN_RNC` int(11) NOT NULL DEFAULT '0',
21 `DN_WBTS` int(11) NOT NULL DEFAULT '0',
22 `DN_WCEL` int(11) NOT NULL DEFAULT '0',
23 `M1023C0` int(11) DEFAULT NULL,
24 `M1023C1` int(11) DEFAULT NULL,
25 `M1023C10` int(11) DEFAULT NULL,
26 `M1023C11` int(11) DEFAULT NULL,
27 `M1023C12` int(11) DEFAULT NULL,
28 `M1023C13` int(11) DEFAULT NULL,
29 `M1023C14` int(11) DEFAULT NULL,
30 `M1023C15` int(11) DEFAULT NULL,
31 `M1023C16` int(11) DEFAULT NULL,
32 `M1023C17` int(11) DEFAULT NULL,
33 `M1023C18` int(11) DEFAULT NULL,
34 `M1023C19` int(11) DEFAULT NULL,
35 `M1023C2` int(11) DEFAULT NULL,
36 `M1023C20` int(11) DEFAULT NULL,
37 `M1023C21` int(11) DEFAULT NULL,
38 `M1023C22` bigint(20) DEFAULT NULL,
39 `M1023C23` int(11) DEFAULT NULL,
40 `M1023C24` int(11) DEFAULT NULL,
41 `M1023C25` int(11) DEFAULT NULL,
42 `M1023C26` int(11) DEFAULT NULL,
43 `M1023C27` int(11) DEFAULT NULL,
44 `M1023C3` int(11) DEFAULT NULL,
45 `M1023C4` int(11) DEFAULT NULL,
46 `M1023C5` int(11) DEFAULT NULL,
47 `M1023C6` int(11) DEFAULT NULL,
48 `M1023C7` int(11) DEFAULT NULL,
49 `M1023C8` bigint(20) DEFAULT NULL,
50 `M1023C9` int(11) DEFAULT NULL,
51 `M1023C28` int(11) DEFAULT NULL,
52 `M1023C29` int(11) DEFAULT NULL,
53 `n_count` int(11) DEFAULT '1',
54 PRIMARY KEY (`DATEDAY`,`DN_RNC`,`DN_WBTS`,`DN_WCEL`,`PERIOD_START_TIME`),
55 KEY `alarm_index` (`PERIOD_START_TIME`,`DN_RNC`,`DN_WBTS`,`DN_WCEL`),
56 KEY `rncgroup` (`DN_RNC`,`DATEDAY`)
57) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=TOKUDB_QUICKLZ;
58
59DROP TABLE IF EXISTS `help_table`;
60CREATE TABLE `help_table` (
61`RegionName` text NOT NULL,
62`controllerGID` bigint(20) NOT NULL,
63`siteGID` bigint(20) NOT NULL,
64`cellGID` bigint(20) NOT NULL,
65`dateday` date NOT NULL,
66PRIMARY KEY (`controllerGID`,`siteGID`,`cellGID`,`dateday`)
67) ENGINE=MyISAM DEFAULT CHARSET=latin1;
68
69INSERT IGNORE INTO help_table
70SELECT CONCAT(co1.N, s2.n, s1.N) as `RegionName`,
71CONCAT(co1.N, '000') as `controllerGID`,
72CONCAT(co1.N, s2.n, s1.N, '0') as `siteGID`,
73CONCAT(co1.N, s2.n, s1.N, ce.N) as `cellGID`,
74DATE_SUB(CURDATE(), interval d.N DAY) as `dateday`
75from
76(select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) co1,
77(select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) s1,
78(select 0 as N union all select 1 union all select 12 union all select 3) s2,
79(select 1 as N union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7) ce,
80(select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
81ORDER BY dateday, controllerGID, siteGID, cellGID;
82
83INSERT IGNORE INTO test_raw_data
84SELECT
85 CONCAT(DATE_SUB(CURDATE(), interval d.N DAY), " ", h.N, ":00:00") as `period_start_time`,
86 DATE_SUB(CURDATE(), interval d.N DAY) as `dateday`,
87 60 as `period_duration`,
88 CONCAT(co1.N, '000') as `controllerGID`,
89 CONCAT(co1.N, s2.n, s1.N, '0') as `siteGID`,
90 CONCAT(co1.N, s2.n, s1.N, ce.N) as `cellGID`,
91 rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100, rand()*100,
92 1 as n_count
93FROM
94 (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) co1,
95 (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) s1,
96 (select 0 as N union all select 1 union all select 2) s2,
97 (select 1 as N union all select 2 union all select 3) ce,
98 (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d,
99 (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23) h
100ORDER BY dateday, controllerGID, siteGID, cellGID;
101
102DROP TABLE IF EXISTS counters_table;
103CREATE TABLE `counters_table` (
104 `RegionName` varchar(500) NOT NULL,
105 `SUM_KPI` double DEFAULT NULL,
106 `datetime` datetime DEFAULT NULL,
107 `isCMFlag` int(11) DEFAULT NULL,
108 `PD_NETCHART` int(11) DEFAULT NULL,
109 `NC_SUM_PD` bigint(20) DEFAULT '0',
110 `NC_COUNT_ROWS` double DEFAULT '0',
111 `TABLE_NAME` varchar(200) DEFAULT ''
112) ENGINE=MyISAM DEFAULT CHARSET=latin1;
113
114DROP TABLE IF EXISTS counters_table2;
115CREATE TABLE `counters_table2` (
116 `RegionName` varchar(500) NOT NULL,
117 `SUM_KPI` double DEFAULT NULL,
118 `datetime` datetime DEFAULT NULL,
119 `isCMFlag` int(11) DEFAULT NULL,
120 `PD_NETCHART` int(11) DEFAULT NULL,
121 `NC_SUM_PD` bigint(20) DEFAULT '0',
122 `NC_COUNT_ROWS` double DEFAULT '0',
123 `TABLE_NAME` varchar(200) DEFAULT ''
124) ENGINE=MyISAM DEFAULT CHARSET=latin1;
125```
126
1273. Use two different mysql client sessions.
128
129 a. On the first one, run:
130
131 ```sql
132 TRUNCATE TABLE counters_table;
133 -- This query will take forever, you'll need to kill it eventually (after reproducing the error)
134 insert into `counters_table`
135 (
136 RegionName,
137 `SUM_KPI`,
138 datetime,
139 `PD_NETCHART`,
140 `NC_SUM_PD`,
141 `NC_COUNT_ROWS`,
142 `TABLE_NAME`
143 ) select
144 RegionName,
145 (
146 sum( t.M1023C3 + t.M1023C2 )
147 ) as 'SUM_KPI',
148 t.PERIOD_START_TIME,
149 t.PERIOD_DURATION,
150 sum( t.PERIOD_DURATION ),
151 count(*),
152 'CELL_THRPUT' as netchart_used_table
153 from
154 test_raw_data as t
155 inner join `help_table` as h
156 group by
157 RegionName,
158 PERIOD_START_TIME;
159 ```
160
161 b. On the other, run the following DML, which will be waiting indefinitely:
162
163 ```sql
164 TRUNCATE TABLE counters_table2;
165 -- This query should run very very fast in parallel
166 -- but for some reason it doesn't.
167 -- when I kill the above query it ends instantly
168 insert into `counters_table2`
169 (
170 RegionName,
171 `SUM_KPI`,
172 datetime,
173 `PD_NETCHART`,
174 `NC_SUM_PD`,
175 `NC_COUNT_ROWS`,
176 `TABLE_NAME`
177 ) select
178 'all',
179 (
180 sum( t.M1023C3 + t.M1023C2 )
181 ) as 'SUM_KPI',
182 t.PERIOD_START_TIME,
183 t.PERIOD_DURATION,
184 sum( t.PERIOD_DURATION ),
185 count(*),
186 'CELL_THRPUT' as netchart_used_table
187 from
188 test_raw_data as t
189 WHERE t.DN_RNC = 1000
190 group by
191 PERIOD_START_TIME;
192 ```
193
1944. After see the lock behavior, stop the queries started in step 3.
195
1965. Again, use two different sessions.
197
198 a. On the first one, run:
199
200 ```sql
201 TRUNCATE TABLE counters_table;
202 -- This query will take forever, you'll need to kill it eventually (after reproducing the error)
203 select
204 RegionName,
205 (
206 sum( t.M1023C3 + t.M1023C2 )
207 ) as 'SUM_KPI',
208 t.PERIOD_START_TIME,
209 t.PERIOD_DURATION,
210 sum( t.PERIOD_DURATION ),
211 count(*),
212 'CELL_THRPUT' as netchart_used_table
213 from
214 test_raw_data as t
215 inner join `help_table` as h
216 group by
217 RegionName,
218 PERIOD_START_TIME;
219 ```
220
221 b. On the other, run the following DML, which will run normally (you may need to change the WHERE clause):
222
223 ```sql
224 TRUNCATE TABLE counters_table2;
225 -- This query should run very very fast in parallel
226 -- but for some reason it doesn't.
227 -- when I kill the above query it ends instantly
228 select
229 'all',
230 (
231 sum( t.M1023C3 + t.M1023C2 )
232 ) as 'SUM_KPI',
233 t.PERIOD_START_TIME,
234 t.PERIOD_DURATION,
235 sum( t.PERIOD_DURATION ),
236 count(*),
237 'CELL_THRPUT' as netchart_used_table
238 from
239 test_raw_data as t
240 WHERE t.dateday = '2018-05-06' AND t.DN_RNC = 1000
241 group by
242 PERIOD_START_TIME;
243 ```