Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
5.5, 10.0, 10.1, 10.0.30, 10.1.21, 10.2
-
None
-
Debian8, Windows 10 (XAMPP)
Description
A query with joins on the same table works for "most" queries, but there are random cases where the query is in "sending data" state forever.
Luckilly these random cases are deterministic enough to provide a test case.
A table dump is provided in attachment. It was generated on debian, and the issue was verified on windows.
The examples below work for the table provided in the attachment (the table must be imported in a database).
In practice, the number of rows expected is determined first with a "COUNT" statement, and then the results are retrieved using the full select statement.
The issue also occurred with the "COUNT" statements. To work around it, the left joins are removed for counting which makes the SQL command work.
Just below is the SQL statement that counts that there are 637 results to be expected from the failing query which is shown just below the COUNT query.
SELECT `t`.`timems` |
FROM `y_metrics` `t` USE INDEX(`entity_time`) |
WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60) |
GROUP BY `t`.`timems` |
The query just further below fails, "mysqladmin proc" or "SHOW PROCESSLIST;" shows query in "sending data" state forever:
---------- PROCESSLIST --------
| 19054 | root | localhost | avy_iot | Query | 5 | Sending data | SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id | 0.000 |
---------- QUERY --------------
SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571Temperature`.`metric_val` as Temperature, |
`s572f`.`metric_val` as f |
FROM `y_metrics` `t` USE INDEX(`entity_time`) |
LEFT OUTER JOIN `y_metrics` s566Cl USE INDEX(time_series) ON `t`.`timems`=`s566Cl`.`timems`AND (`s566Cl`.`series_id`=566) |
LEFT OUTER JOIN `y_metrics` s568rawCl USE INDEX(time_series) ON `t`.`timems`=`s568rawCl`.`timems`AND (`s568rawCl`.`series_id`=568) |
LEFT OUTER JOIN `y_metrics` s570pH USE INDEX(time_series) ON `t`.`timems`=`s570pH`.`timems`AND (`s570pH`.`series_id`=570) |
LEFT OUTER JOIN `y_metrics` s571Temperature USE INDEX(time_series) ON `t`.`timems`=`s571Temperature`.`timems`AND (`s571Temperature`.`series_id`=571) |
LEFT OUTER JOIN `y_metrics` s572f USE INDEX(time_series) ON `t`.`timems`=`s572f`.`timems`AND (`s572f`.`series_id`=572) |
WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60) |
GROUP BY `t`.`timems` LIMIT 637 |
Killing the query and requesting just a few results works for the subset:
-------- LIMIT 5 -------------
SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571Temperature`.`metric_val` as Temperature, |
`s572f`.`metric_val` as f |
FROM `y_metrics` `t` USE INDEX(`entity_time`) |
LEFT OUTER JOIN `y_metrics` s566Cl USE INDEX(time_series) ON `t`.`timems`=`s566Cl`.`timems`AND (`s566Cl`.`series_id`=566) |
LEFT OUTER JOIN `y_metrics` s568rawCl USE INDEX(time_series) ON `t`.`timems`=`s568rawCl`.`timems`AND (`s568rawCl`.`series_id`=568) |
LEFT OUTER JOIN `y_metrics` s570pH USE INDEX(time_series) ON `t`.`timems`=`s570pH`.`timems`AND (`s570pH`.`series_id`=570) |
LEFT OUTER JOIN `y_metrics` s571Temperature USE INDEX(time_series) ON `t`.`timems`=`s571Temperature`.`timems`AND (`s571Temperature`.`series_id`=571) |
LEFT OUTER JOIN `y_metrics` s572f USE INDEX(time_series) ON `t`.`timems`=`s572f`.`timems`AND (`s572f`.`series_id`=572) |
WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60) |
GROUP BY `t`.`timems` LIMIT 5 |
|
Example to show that query logic is ok (this example gives correct results):
-------- determine count — returns 985 -----
select count(*) from (SELECT `t`.`timems` from y_metrics `t` |
WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=47) |
GROUP BY `t`.`timems`) s |
|
-------- get data — with limit 985 – (when using limit 1000 also returns 985 rows) -------------------------
SELECT `t`.`timems`, 505 as Temperature_id, 506 as pH_id, 507 as Cl_id, 508 as rawCl_id, 509 as f_id, `s505Temperature`.`metric_val` as Temperature, `s506pH`.`metric_val` as pH, `s507Cl`.`metric_val` as Cl, `s508rawCl`.`metric_val` as rawCl, `s509f`.`metric_val` as f FROM `y_metrics` `t` USE INDEX(`entity_time`) LEFT OUTER JOIN `y_metrics` s505Temperature USE INDEX(time_series) ON `t`.`timems`=`s505Temperature`.`timems`AND (`s505Temperature`.`series_id`=505) LEFT OUTER JOIN `y_metrics` s506pH USE INDEX(time_series) ON `t`.`timems`=`s506pH`.`timems`AND (`s506pH`.`series_id`=506) LEFT OUTER JOIN `y_metrics` s507Cl USE INDEX(time_series) ON `t`.`timems`=`s507Cl`.`timems`AND (`s507Cl`.`series_id`=507) LEFT OUTER JOIN `y_metrics` s508rawCl USE INDEX(time_series) ON `t`.`timems`=`s508rawCl`.`timems`AND (`s508rawCl`.`series_id`=508) LEFT OUTER JOIN `y_metrics` s509f USE INDEX(time_series) ON `t`.`timems`=`s509f`.`timems`AND (`s509f`.`series_id`=509) |
WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=47) |
GROUP BY `t`.`timems` LIMIT 985 |