You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@cloudstack.apache.org by "Francois Scheurer (JIRA)" <ji...@apache.org> on 2017/12/29 14:26:00 UTC
[jira] [Closed] (CLOUDSTACK-8966) listCapacity produces wrong
result for CAPACITY_TYPE_MEMORY and CAPACITY_TYPE_CPU
[ https://issues.apache.org/jira/browse/CLOUDSTACK-8966?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Francois Scheurer closed CLOUDSTACK-8966.
-----------------------------------------
Resolution: Fixed
Fix Version/s: 4.9.0
> listCapacity produces wrong result for CAPACITY_TYPE_MEMORY and CAPACITY_TYPE_CPU
> ---------------------------------------------------------------------------------
>
> Key: CLOUDSTACK-8966
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-8966
> Project: CloudStack
> Issue Type: Bug
> Security Level: Public(Anyone can view this level - this is the default.)
> Components: API, Cloudmonkey, UI
> Affects Versions: 4.3.1, 4.5.1, 4.5.2
> Environment: CloudStack 4.5.1
> MariaDB 10.0 and 10.1
> Reporter: Francois Scheurer
> Fix For: 4.9.0
>
>
> Dear CloudStack Contributors,
> listCapacity produces wrong result for CAPACITY_TYPE_MEMORY and CAPACITY_TYPE_CPU when MariaDB is used instead of MySQL.
> More specifically,
> capacityused is correct.
> capacitytotal is incorrect (null).
> Example:
> (man02-test-admin) 🐵 > list capacity type=0 listall=true
> count = 1
> capacity:
> capacitytotal = 0
> capacityused = 16106127360
> percentused = 0
> type = 0
> zoneid = e683eeaa-92c9-4651-91b9-165939f9000c
> zonename = ZH-EP-Z00
> (man02-test-admin) 🐵 > list capacity type=1 listall=true
> count = 1
> capacity:
> capacitytotal = 0
> capacityused = 24500
> percentused = 0
> type = 1
> zoneid = e683eeaa-92c9-4651-91b9-165939f9000c
> zonename = ZH-EP-Z00
> (man02-test-admin) 🐵 >
> Analysis:
> The issue is caused by an SQL Query that provide inconsistent result between MySQL and MariaDB.
> cf. http://fossies.org/dox/apache-cloudstack-4.5.2-src/CapacityDaoImpl_8java_source.html#l00418
> MariaDB [cloud]> --not working:
> MariaDB [cloud]> SELECT sum(capacity.used_capacity),
> -> sum(capacity.reserved_capacity),
> -> sum(total_capacity) * (select value from cloud.cluster_details where cluster_details.name='memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id),
> -> capacity.capacity_type,
> -> capacity.data_center_id,
> -> pod_id,
> -> cluster_id
> -> FROM cloud.op_host_capacity capacity WHERE capacity_type=0 \G
> *************************** 1. row ***************************
> sum(capacity.used_capacity): 16106127360
> sum(capacity.reserved_capacity): 0
> sum(total_capacity) * (select value from cloud.cluster_details where cluster_details.name='memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id): NULL
> capacity_type: 0
> data_center_id: 2
> pod_id: 1
> cluster_id: 1
> Possible fixes:
> MariaDB [cloud]> -- working, using a CAST:
> MariaDB [cloud]> SELECT sum(capacity.used_capacity),
> -> sum(capacity.reserved_capacity),
> -> sum(total_capacity) * CAST((select value from cloud.cluster_details where cluster_details.name='memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id) AS DECIMAL(10,4)),
> -> capacity.capacity_type,
> -> capacity.data_center_id,
> -> pod_id,
> -> cluster_id
> -> FROM cloud.op_host_capacity capacity WHERE capacity_type=0 \G
> *************************** 1. row ***************************
> sum(capacity.used_capacity): 16106127360
> sum(capacity.reserved_capacity): 0
> sum(total_capacity) * CAST((select value from cloud.cluster_details where cluster_details.name='memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id) AS DECIMAL(10,4)): 126858785280.0000
> capacity_type: 0
> data_center_id: 2
> pod_id: 1
> cluster_id: 1
> MariaDB [cloud]> -- working, using a aliases:
> MariaDB [cloud]> SELECT used_capacity, reserved_capacity, total_capacity * value, capacity_type, data_center_id, pod_id, cluster_id
> -> FROM ( SELECT sum(capacity.used_capacity) used_capacity,
> -> sum(capacity.reserved_capacity) reserved_capacity,
> -> sum(total_capacity) total_capacity,
> -> (select value from cloud.cluster_details where cluster_details.name='memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id) value,
> -> capacity.capacity_type capacity_type,
> -> capacity.data_center_id data_center_id,
> -> pod_id pod_id,
> -> cluster_id cluster_id
> -> FROM cloud.op_host_capacity capacity WHERE capacity_type=0
> -> ) x \G
> *************************** 1. row ***************************
> used_capacity: 16106127360
> reserved_capacity: 0
> total_capacity * value: 126858785280
> capacity_type: 0
> data_center_id: 2
> pod_id: 1
> cluster_id: 1
> IMHO the solution using aliases seems to be the cleanest way to fix the query.
> Thank you in advance and Best Regards,
> Francois Scheurer
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)