[CLOUDSTACK-8966] listCapacity produces wrong result for CAPACITY_TYPE_MEMORY and CAPACITY_TYPE_CPU

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.3.1, 4.5.1, 4.5.2
• Fix Version/s: 4.9.0
• Component/s: API, Cloudmonkey, UI
• Security Level: Public (Anyone can view this level - this is the default.)
• Environment: CloudStack 4.5.1 ; MariaDB 10.0 and 10.1
• Assignee: Unassigned
• Reporter: Francois Scheurer
• Created: 19/Oct/15 09:02
• Updated: 29/Dec/17 14:25
• Resolved: 29/Dec/17 14:25

Description

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