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:19:39 GMT

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

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

GitHub user lttmtins opened a pull request:

    https://github.com/apache/cloudstack/pull/1313

    fix mariadb related listCapacity bug (CLOUDSTACK-8966)  a bigint(20) …

    …with varchar type does not work well on MariaDB
    so forcing it to type decimal
    Tested on ACS4.7 with MariaDB10.0.22 (galera)
    
    To test (this is the output with the pull request code underneath):
    (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) > 

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/lttmtins/cloudstack master

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/cloudstack/pull/1313.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #1313
    
----
commit 957e37df012f0b3be84735a5bde70eb004d18493
Author: Anton Opgenoort <aopgenoort@schubergphilis.com>
Date:   2016-01-06T16:15:02Z

    fix mariadb related listCapacity bug (CLOUDSTACK-8966)  a bigint(20) with varchar type
does not work well on MariaDB
    so forcing it to type decimal

----


> 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