Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.7, 10.1.8, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
CentOS release 6.7 with MariaDB-server-10.1.8-1.el6.x86_64
-
5.5.47-1, 5.5.48-0
Description
The CPU and memory total capacity as reported by the API and as visible on the CS dashboard are shown as zero. This is due to the following query failing to get correct results when the database is run on MariaDB 10.1:
SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled' AND capacity.data_center_id = ? AND capacity.cluster_id = ? AND capacity.capacity_type = ? GROUP BY data_center_id, pod_id, cluster_id, capacity_type |
The output is incorrect and like this:
+-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
|
| sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value fro | percent | capacity_type | data_center_id | pod_id | cluster_id |
|
+-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
|
| 404750336000 | 0 | NULL | NULL | 0 | 3 | 3 | 3 |
|
+-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
|
After some analysis this seems due to the value of "capacity.cluster_id" as set in the final WHERE-clause not being used in the (nested) previous WHERE clauses like in:
cluster_details.cluster_id=capacity.cluster_id
If I manually update the query by specifying the cluster_id-number it works correctly, e.g.:
SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled' AND capacity.data_center_id = 3 AND capacity.cluster_id = 3 AND capacity.capacity_type = 0 GROUP BY data_center_id, pod_id, cluster_id, capacity_type |
The output then is correct and like this:
+-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
|
| sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster | percent | capacity_type | data_center_id | pod_id | cluster_id |
|
+-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
|
| 404750336000 | 0 | 810895474688 | 0.49913996147990797 | 0 | 3 | 3 | 3 |
|
+-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
|
1 row in set (0.00 sec)
|
I don't know if this is an issue with MariaDB or correct SQL to use; the query works on older MySQL versions.
As a workaround an older MySQL-version can be used. If this needs fixing, then the CS management server could specify the cluster_id during the query in all the required places.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
The CPU and memory total capacity as reported by the API and as visible on the CS dashboard are shown as zero. This is due to the following query failing to get correct results when the database is run on MariaDB 10.1: SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled' AND capacity.data_center_id = ? AND capacity.cluster_id = ? AND capacity.capacity_type = ? GROUP BY data_center_id, pod_id, cluster_id, capacity_type The output is incorrect and like this: +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+ | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value fro | percent | capacity_type | data_center_id | pod_id | cluster_id | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+ | 404750336000 | 0 | NULL | NULL | 0 | 3 | 3 | 3 | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+ After some analysis this seems due to the value of "capacity.cluster_id" as set in the final WHERE-clause not being used in the (nested) previous WHERE clauses like in: cluster_details.cluster_id=capacity.cluster_id If I manually update the query by specifying the cluster_id-number it works correctly, e.g.: SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled' AND capacity.data_center_id = 3 AND capacity.cluster_id = 3 AND capacity.capacity_type = 0 GROUP BY data_center_id, pod_id, cluster_id, capacity_type The output then is correct and like this: +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+ | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster | percent | capacity_type | data_center_id | pod_id | cluster_id | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+ | 404750336000 | 0 | 810895474688 | 0.49913996147990797 | 0 | 3 | 3 | 3 | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+ 1 row in set (0.00 sec) I don't know if this is an issue with MariaDB or correct SQL to use; the query works on older MySQL versions. As a workaround an older MySQL-version can be used. If this needs fixing, then the CS management server could specify the cluster_id during the query in all the required places. |
The CPU and memory total capacity as reported by the API and as visible on the CS dashboard are shown as zero. This is due to the following query failing to get correct results when the database is run on MariaDB 10.1: {code:sql} SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled' AND capacity.data_center_id = ? AND capacity.cluster_id = ? AND capacity.capacity_type = ? GROUP BY data_center_id, pod_id, cluster_id, capacity_type {code} The output is incorrect and like this: {noformat} +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+ | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value fro | percent | capacity_type | data_center_id | pod_id | cluster_id | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+ | 404750336000 | 0 | NULL | NULL | 0 | 3 | 3 | 3 | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+ {noformat} After some analysis this seems due to the value of "capacity.cluster_id" as set in the final WHERE-clause not being used in the (nested) previous WHERE clauses like in: cluster_details.cluster_id=capacity.cluster_id If I manually update the query by specifying the cluster_id-number it works correctly, e.g.: {code:sql} SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled' AND capacity.data_center_id = 3 AND capacity.cluster_id = 3 AND capacity.capacity_type = 0 GROUP BY data_center_id, pod_id, cluster_id, capacity_type {code} The output then is correct and like this: {noformat} +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+ | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster | percent | capacity_type | data_center_id | pod_id | cluster_id | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+ | 404750336000 | 0 | 810895474688 | 0.49913996147990797 | 0 | 3 | 3 | 3 | +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+ 1 row in set (0.00 sec) {noformat} I don't know if this is an issue with MariaDB or correct SQL to use; the query works on older MySQL versions. As a workaround an older MySQL-version can be used. If this needs fixing, then the CS management server could specify the cluster_id during the query in all the required places. |
| Labels | need_feedback |
1) This is the ouput of the original query:
http://pastebin.com/raw.php?i=Zejbq0XJ
This is the output of the modified query:
http://pastebin.com/raw.php?i=sezU9vwM
2) This is the output:
http://pastebin.com/raw.php?i=13MF2wH9
3) I will upload the dumps and update this issue.
3) I have uploaded the dumps by FTP:
ftp> put cluster_details.sql
|
local: cluster_details.sql remote: cluster_details.sql
|
227 Entering Passive Mode (194,136,193,154,94,68).
|
150 Ok to send data.
|
226 Transfer complete.
|
1194 bytes sent in 3.2e-05 secs (37312.50 Kbytes/sec)
|
ftp> put op_host_capacity.sql
|
local: op_host_capacity.sql remote: op_host_capacity.sql
|
227 Entering Passive Mode (194,136,193,154,50,111).
|
150 Ok to send data.
|
226 Transfer complete.
|
4785 bytes sent in 4.2e-05 secs (113928.58 Kbytes/sec)
|
|
*3a)* |
MariaDB [cloud]> SHOW CREATE TABLE cluster_details;
|
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| cluster_details | CREATE TABLE `cluster_details` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`cluster_id` bigint(20) unsigned NOT NULL COMMENT 'cluster id',
|
`name` varchar(255) NOT NULL,
|
`value` varchar(255) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `fk_cluster_details__cluster_id` (`cluster_id`),
|
CONSTRAINT `fk_cluster_details__cluster_id` FOREIGN KEY (`cluster_id`) REFERENCES `cluster` (`id`) ON DELETE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
|
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
MariaDB [cloud]> SHOW CREATE TABLE op_host_capacity;
|
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| op_host_capacity | CREATE TABLE `op_host_capacity` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`host_id` bigint(20) unsigned DEFAULT NULL,
|
`data_center_id` bigint(20) unsigned NOT NULL,
|
`pod_id` bigint(20) unsigned DEFAULT NULL,
|
`cluster_id` bigint(20) unsigned DEFAULT NULL COMMENT 'foreign key to cluster',
|
`used_capacity` bigint(20) NOT NULL,
|
`reserved_capacity` bigint(20) NOT NULL,
|
`total_capacity` bigint(20) NOT NULL,
|
`capacity_type` int(1) unsigned NOT NULL,
|
`capacity_state` varchar(32) NOT NULL DEFAULT 'Enabled' COMMENT 'Is this capacity enabled for allocation for new resources',
|
`update_time` datetime DEFAULT NULL COMMENT 'time the capacity was last updated',
|
`created` datetime DEFAULT NULL COMMENT 'date created',
|
PRIMARY KEY (`id`),
|
KEY `i_op_host_capacity__host_type` (`host_id`,`capacity_type`),
|
KEY `i_op_host_capacity__pod_id` (`pod_id`),
|
KEY `i_op_host_capacity__data_center_id` (`data_center_id`),
|
KEY `i_op_host_capacity__cluster_id` (`cluster_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=208 DEFAULT CHARSET=utf8 |
|
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
MariaDB [cloud]> SHOW INDEX IN cluster_details;
|
+-----------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| cluster_details | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | |
|
| cluster_details | 1 | fk_cluster_details__cluster_id | 1 | cluster_id | A | 6 | NULL | NULL | | BTREE | | |
|
+-----------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
MariaDB [cloud]> SHOW INDEX IN op_host_capacity;
|
+------------------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+------------------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| op_host_capacity | 0 | PRIMARY | 1 | id | A | 35 | NULL | NULL | | BTREE | | |
|
| op_host_capacity | 1 | i_op_host_capacity__host_type | 1 | host_id | A | 35 | NULL | NULL | YES | BTREE | | |
|
| op_host_capacity | 1 | i_op_host_capacity__host_type | 2 | capacity_type | A | 35 | NULL | NULL | | BTREE | | |
|
| op_host_capacity | 1 | i_op_host_capacity__pod_id | 1 | pod_id | A | 4 | NULL | NULL | YES | BTREE | | |
|
| op_host_capacity | 1 | i_op_host_capacity__data_center_id | 1 | data_center_id | A | 2 | NULL | NULL | | BTREE | | |
|
| op_host_capacity | 1 | i_op_host_capacity__cluster_id | 1 | cluster_id | A | 7 | NULL | NULL | YES | BTREE | | |
|
+------------------+------------+------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
Please let me know if there is anything else needed.
| Labels | need_feedback |
moroder,
Thank you, it is easily reproducible with the provided data.
psergey (or whoever ends up fixing it),
I've put together the data file as ftp.askmonty.org/private/mdev8988_data.sql – it is the same data for two tables as provided, I only added schema creation, removed a foreign key from one of the tables, and added ANALYZE TABLE at the end, otherwise sometimes the wrong result was not triggered.
Here is a somewhat simplified queries – again, it is the same as the initial queries, I just removed unimportant fields, CASE clauses and such:
# Bad query
|
SELECT |
sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id) fff, |
capacity.capacity_type,
|
cluster_id
|
FROM `cloud`.`op_host_capacity` capacity |
WHERE capacity.cluster_id = 3 AND capacity.capacity_type = 0 |
GROUP BY cluster_id, capacity_type; |
|
|
# Good query
|
SELECT |
sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3) fff, |
capacity.capacity_type,
|
cluster_id
|
FROM `cloud`.`op_host_capacity` capacity |
WHERE capacity.cluster_id = 3 AND capacity.capacity_type = 0 |
GROUP BY cluster_id, capacity_type; |
# Bad result
|
+------+---------------+------------+
|
| fff | capacity_type | cluster_id |
|
+------+---------------+------------+
|
| NULL | 0 | 3 |
|
+------+---------------+------------+
|
1 row in set (0.01 sec)
|
|
|
# Good result
|
+--------------+---------------+------------+
|
| fff | capacity_type | cluster_id |
|
+--------------+---------------+------------+
|
| 810895474688 | 0 | 3 |
|
+--------------+---------------+------------+
|
1 row in set (0.00 sec)
|
# Bad explain
|
+------+--------------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|
| 1 | PRIMARY | capacity | ref | i_op_host_capacity__cluster_id | i_op_host_capacity__cluster_id | 9 | const | 17 | 100.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | cluster_details | ref | cluster_id | cluster_id | 8 | func | 1 | 100.00 | Using where |
|
+------+--------------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|
|
|
# Good explain
|
+------+-------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|
| 1 | PRIMARY | capacity | ref | i_op_host_capacity__cluster_id | i_op_host_capacity__cluster_id | 9 | const | 17 | 100.00 | Using where |
|
| 2 | SUBQUERY | cluster_details | ALL | cluster_id | NULL | NULL | NULL | 6 | 66.67 | Using where |
|
+------+-------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------+
|
After fixing, please check the original query as well, just in case..
| Component/s | Optimizer [ 10200 ] | |
| Component/s | Platform RedHat [ 11302 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Assignee | Sergei Petrunia [ psergey ] |
| Assignee | Sergei Petrunia [ psergey ] | Vicentiu Ciorbaru [ cvicentiu ] |
So, a scalar context subquery produces a wrong result.
The subquery is correlated, it is evaluated in a post-group-by context.
It is correlated via reference to capacity.cluster_id , which is a grouping field.
An ideal assignee would be sanja, but he's booked. cvicentiu, could you take a look?
| Sprint | 5.5.47-1 [ 22 ] |
| Rank | Ranked higher |
This affects CloudStack users – this needs to be fixed as soon as possible – https://issues.apache.org/jira/browse/CLOUDSTACK-8980
| Sprint | 5.5.47-1 [ 22 ] | 5.5.47-1, 5.5.48-0 [ 22, 33 ] |
| Rank | Ranked higher |
| Status | Open [ 1 ] | In Progress [ 3 ] |
Can this be accelerated somehow?
I have to advise customers to refrain from using MariaDB if this does not get solved within the next two weeks.
As far as I understand, there is a problem in the OPTIMIZER of MariaDB that causes valid SQL queries to give back wrong results, which is quite an issue. This ticket has been raised the 22nd of OCTOBER 2015 and as of today, 22 FEB 2016 it is still not resolved. It specifically affects CloudStack users, but it shows a more general problem in the OPTIMIZER with context subqueries that generally affects MariaDB.
If there is anything else needed from my part to solve this issue more quickly, please let me know.
But that it takes so long to solve such an issue is quite surprising if not shocking for me.
moroder We've started investigating this in more detail.
After experimenting with the queries, I've narrowed it down to the correlated subquery returning an empty set. The difference between the Good and the Bad query is that the Good query is viewed as a constant table (cacheable) while the correlated one is not. Using a group by statement is not necessary to show this behavior. In fact, removing the group by leads to the same result in the case of the bad query.
Another thing that can be eliminated is the condition
cluster_details.name= 'memoryOvercommitRatio'
|
.
This does not change the correlated subquery result, however it does make the non correlated subquery to return multiple rows, hence the full query fails with the error:
ERROR 1242 (21000): Subquery returns more than 1 row
|
For reference:
# Good Query
|
SELECT sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.cluster_id=3) fff
|
FROM `cloud`.`op_host_capacity` capacity
|
WHERE capacity.cluster_id = 3 AND capacity.capacity_type = 0;
|
|
|
ERROR 1242 (21000): Subquery returns more than 1 row
|
|
|
#Bad Query
|
SELECT sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.cluster_id=capacity.cluster_id) fff
|
FROM `cloud`.`op_host_capacity` capacity
|
WHERE capacity.cluster_id = 3 AND capacity.capacity_type = 0;
|
|
|
+------+
|
| fff |
|
+------+
|
| NULL |
|
+------+
|
|
|
|
|
So, the wrong result is because the subquery returns no rows.
The subquery decides to return no rows here in do_select:
if (join->outer_ref_cond && !join->outer_ref_cond->val_int())
|
error= NESTED_LOOP_NO_MORE_ROWS;
|
When we're in the subquery, we get:
(gdb) p join->select_lex->select_number
|
$46 = 2
|
(gdb) p dbug_print_item(join->outer_ref_cond)
|
$47 = 0x555556d19300 "(capacity.cluster_id is not null)"
|
(gdb) p ((Item*)join->outer_ref_cond)->args[0]
|
$48 = (Item_field *) 0x7fff5c006e00
|
So, outer_ref_cond uses an Item_field to refer to parent select's capacity table directly.
AFAIU, this is wrong, because the upper query invokes the subquery in a post-group-by context:
#0 do_select (join=0x7fff5c0090e8, fields=0x7fff5c005910, table=0x0, procedure=0x0) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:17933
|
#1 0x0000555555a9384a in JOIN::exec_inner (this=0x7fff5c0090e8) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:3228
|
#2 0x0000555555a90b2f in JOIN::exec (this=0x7fff5c0090e8) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:2518
|
#3 0x0000555555d33e4b in subselect_single_select_engine::exec (this=0x7fff5c0072b8) at /home/psergey/dev-git/10.1-dbg5/sql/item_subselect.cc:3747
|
#4 0x0000555555d2a5ca in Item_subselect::exec (this=0x7fff5c007180) at /home/psergey/dev-git/10.1-dbg5/sql/item_subselect.cc:672
|
#5 0x0000555555d2c036 in Item_singlerow_subselect::val_str (this=0x7fff5c007180, str=0x7fff5c07a0a8) at /home/psergey/dev-git/10.1-dbg5/sql/item_subselect.cc:1283
|
#6 0x000055555599d528 in Item::str_result (this=0x7fff5c007180, tmp=0x7fff5c07a0a8) at /home/psergey/dev-git/10.1-dbg5/sql/item.h:1040
|
#7 0x0000555555cb3204 in Item_cache_str::cache_value (this=0x7fff5c079fb8) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:9081
|
#8 0x0000555555cb9b98 in Item_cache_wrapper::cache (this=0x7fff5c079f08) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:7614
|
#9 0x0000555555caeeff in Item_cache_wrapper::val_real (this=0x7fff5c079f08) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:7695
|
#10 0x0000555555cf0634 in Item_func_mul::real_op (this=0x7fff5c007300) at /home/psergey/dev-git/10.1-dbg5/sql/item_func.cc:1597
|
#11 0x0000555555cee23e in Item_func_hybrid_field_type::val_real (this=0x7fff5c007300) at /home/psergey/dev-git/10.1-dbg5/sql/item_func.cc:936
|
#12 0x0000555555cab9a9 in Item::send (this=0x7fff5c007300, protocol=0x55555aadac70, buffer=0x7ffff42fdc40) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:6447
|
#13 0x000055555599a894 in Protocol::send_result_set_row (this=0x55555aadac70, row_items=0x7fff5c008b48) at /home/psergey/dev-git/10.1-dbg5/sql/protocol.cc:905
|
#14 0x0000555555a119ac in select_send::send_data (this=0x7fff5c008738, items=...) at /home/psergey/dev-git/10.1-dbg5/sql/sql_class.cc:2792
|
#15 0x0000555555abae26 in end_send_group (join=0x7fff5c008758, join_tab=0x7fff5c00b0c8, end_of_records=true) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:19545
|
#16 0x0000555555ab7b18 in sub_select (join=0x7fff5c008758, join_tab=0x7fff5c00ad80, end_of_records=true) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:18233
|
#17 0x0000555555ab75f2 in do_select (join=0x7fff5c008758, fields=0x7fff5c008b48, table=0x0, procedure=0x0) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:17939
|
In that context, table capacity has no "current row".
Query plan of the query that produces bad results:
+------+--------------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+-------------+
|
| 1 | PRIMARY | capacity | ref | i_op_host_capacity__cluster_id | i_op_host_capacity__cluster_id | 9 | const | 17 | Using where |
|
| 2 | DEPENDENT SUBQUERY | cluster_details | ref | cluster_id | cluster_id | 8 | func | 1 | Using where |
|
+------+--------------------+-----------------+------+--------------------------------+--------------------------------+---------+-------+------+-------------+
|
So, we fail in the "early NULLs filtering" optimization, where we check that capacity.cluster_id IS NOT NULL before making an index lookup in the cluster_details table
.
Let's check if making-the-lookup part works. I use gdb to make Item_func_isnotnull::val_int() return true, and we end up here:
(gdb) wher
|
#0 field_conv (to=0x7fff5402e880, from=0x7fff54030738) at /home/psergey/dev-git/10.1-dbg5/sql/field_conv.cc:870
|
#1 0x0000555555ca9901 in save_field_in_field (from=0x7fff54030738, null_value=0x7fff540306ba, to=0x7fff5402e880, no_conversions=true) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:5783
|
#2 0x0000555555ca9b71 in Item_field::save_in_field (this=0x7fff54030648, to=0x7fff5402e880, no_conversions=true) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:5839
|
#3 0x0000555555caddd9 in Item_ref::save_in_field (this=0x7fff5400bd18, to=0x7fff5402e880, no_conversions=true) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:7327
|
#4 0x0000555555caddd9 in Item_ref::save_in_field (this=0x7fff5400b9b8, to=0x7fff5402e880, no_conversions=true) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:7327
|
#5 0x0000555555ad0386 in store_key_item::copy_inner (this=0x7fff5402e848) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.h:1757
|
#6 0x0000555555acff75 in store_key::copy (this=0x7fff5402e848) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.h:1647
|
#7 0x0000555555ac06d9 in cp_buffer_from_ref (thd=0x55555aada8a0, table=0x7fff54019f50, ref=0x7fff5402e6d8) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:21821
|
#8 0x0000555555ab9672 in join_read_always_key (tab=0x7fff5402e4b0) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:19023
|
#9 0x0000555555ab7ce3 in sub_select (join=0x7fff5400b348, join_tab=0x7fff5402e4b0, end_of_records=false) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:18278
|
#10 0x0000555555ab75a5 in do_select (join=0x7fff5400b348, fields=0x7fff54007b70, table=0x0, procedure=0x0) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:17936
|
#11 0x0000555555a9384a in JOIN::exec_inner (this=0x7fff5400b348) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:3228
|
#12 0x0000555555a90b2f in JOIN::exec (this=0x7fff5400b348) at /home/psergey/dev-git/10.1-dbg5/sql/sql_select.cc:2518
|
#13 0x0000555555d33e4b in subselect_single_select_engine::exec (this=0x7fff54009518) at /home/psergey/dev-git/10.1-dbg5/sql/item_subselect.cc:3747
|
#14 0x0000555555d2a5ca in Item_subselect::exec (this=0x7fff540093e0) at /home/psergey/dev-git/10.1-dbg5/sql/item_subselect.cc:672
|
(gdb) up
|
...
|
(gdb) up
|
#3 0x0000555555caddd9 in Item_ref::save_in_field (this=0x7fff5400bd18, to=0x7fff5402e880, no_conversions=true) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:7327
|
(gdb) p this
|
$58 = (Item_ref *) 0x7fff5400bd18
|
(gdb) up
|
#4 0x0000555555caddd9 in Item_ref::save_in_field (this=0x7fff5400b9b8, to=0x7fff5402e880, no_conversions=true) at /home/psergey/dev-git/10.1-dbg5/sql/item.cc:7327
|
(gdb) p this
|
$61 = (Item_outer_ref *) 0x7fff5400b9b8
|
Note that references are correctly wrapped into Item_ref and Item_direct_ref objects.
Looking at add_not_null_conds():
Item *item= tab->ref.items[keypart];
|
...
|
Item *real= item->real_item();
|
(gdb) p item
|
$68 = (Item_outer_ref *) 0x7fff5400b9b8
|
(gdb)
|
(gdb) p real
|
$71 = (Item_field *) 0x7fff54009060
|
This is where the Item_ref wrappers are "peeled off".
In our example, the "peeling off" causes wrong query result.
I'm not sure if there are cases where this "peeling off" is useful.
Checked why mysql-5.7 doesn't have this problem. They have add_not_null_conds, but they don't have JOIN::outer_ref_cond. So, they don't attach "outside_reference IS NOT NULL" condition is not attached anywhere.
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 5.5 [ 15800 ] |
| Fix Version/s | 10.1.12 [ 21502 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Sergei Petrunia [ psergey ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
moroder, thanks for your patience and for bringing this bug up for our attention again.
I have tested this version on our dev-system and today also on our reference cloud and it works correctly.
Thanks for fixing this!
| Workflow | MariaDB v3 [ 72233 ] | MariaDB v4 [ 149740 ] |
moroder,
1) Could you please run EXPLAIN SELECT ... for the original query and the modified query, and paste the output?
2) Please attach your cnf file(s) or paste the output of SHOW VARIABLES
3) If possible, please provide the dump of `cloud`.`cluster_details` and `cloud`.`op_host_capacity` (if you don't want to make it public, or if it's big, you can upload it to ftp.askmonty.org/private, this way only MariaDB developers will have access to it);
3a) if you cannot provide the dump, please at least paste SHOW CREATE TABLE ... and SHOW INDEX IN ... for the both tables.
Thanks.