[
https://issues.apache.org/jira/browse/CLOUDSTACK-8980?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas Moroder resolved CLOUDSTACK-8980.
----------------------------------------
Resolution: Fixed
Fixed by upstream (MariaDB 10.1.12)
https://mariadb.atlassian.net/browse/MDEV-8988
> 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
> Labels: cloudstack-management, mariadb
>
> 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)
