cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Francois Scheurer (JIRA)" <j...@apache.org>
Subject [jira] [Created] (CLOUDSTACK-8966) listCapacity produce wrong result for CAPACITY_TYPE_MEMORY and CAPACITY_TYPE_CPU
Date Mon, 19 Oct 2015 09:03:05 GMT
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)

Mime
View raw message