Francois Scheurer created CLOUDSTACK-8966:
---------------------------------------------
Summary: listCapacity produce 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.5.2, 4.5.1, 4.3.1
Environment: CloudStack 4.5.1
MariaDB 10.0 and 10.1
Reporter: Francois Scheurer
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.3.4#6332)
