cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CLOUDSTACK-8966) listCapacity produces wrong result for CAPACITY_TYPE_MEMORY and CAPACITY_TYPE_CPU
Date Wed, 06 Jan 2016 16:46:40 GMT

    [ https://issues.apache.org/jira/browse/CLOUDSTACK-8966?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15085804#comment-15085804
] 

ASF GitHub Bot commented on CLOUDSTACK-8966:
--------------------------------------------

Github user lttmtins commented on the pull request:

    https://github.com/apache/cloudstack/pull/1314#issuecomment-169384312
  
    To test with cloudmonkey (this is the output with the pull request code underneath) you
need 2 API calls (e.g. via cloudmonkey):
    (nl2) > list capacity sortby=usage type=0
    count = 1
    capacity:
    capacitytotal = 0
    capacityused = 3501740523520
    percentused = 0
    type = 0
    zoneid = 76251030-aca1-44c6-b47d-8010ee17e0ad
    zonename = NL2
    (nl2) >
    
    Suggested tests to see if the 'capacitytotal != 0':
    (nl2) > list capacity type=0 clusterid=<uuid>
    (nl2) > list capacity type=1 clusterid=<uuid>
    (nl2) > list capacity type=3 clusterid=<uuid>
    (nl2) > list capacity type=0 sortby=usage
    (nl2) > list capacity type=1 sortby=usage
    (nl2) > list capacity type=3 sortby=usage


> 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
>
> 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