Uploaded image for project: 'CloudStack'
  1. CloudStack
  2. CLOUDSTACK-8966

listCapacity produces wrong result for CAPACITY_TYPE_MEMORY and CAPACITY_TYPE_CPU

    XMLWordPrintableJSON

    Details

    • 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.)
    • Labels:
      None
    • Environment:
      CloudStack 4.5.1
      MariaDB 10.0 and 10.1

      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              francois.scheurer Francois Scheurer
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: