Thomas Moroder created CLOUDSTACK-8980:
------------------------------------------
Summary: CloudStack 4.5.2 not reporting correct total capacities
on MariaDB-server 10.1
Key: CLOUDSTACK-8980
URL: https://issues.apache.org/jira/browse/CLOUDSTACK-8980
Project: CloudStack
Issue Type: Bug
Security Level: Public (Anyone can view this level - this is the default.)
Components: Management Server
Affects Versions: 4.5.2
Environment: CentOS release 6.7 with MariaDB-server-10.1.8-1.el6.x86_64
Reporter: Thomas Moroder
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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
