[
https://issues.apache.org/jira/browse/CLOUDSTACK-8966?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15085785#comment-15085785
]
ASF GitHub Bot commented on CLOUDSTACK-8966:
--------------------------------------------
GitHub user lttmtins opened a pull request:
https://github.com/apache/cloudstack/pull/1314
Fix mariadb related listCapacity bug (CLOUDSTACK-8966)
type bigint(20) with type varchar does not work well on MariaDB
So forcing it to type decimal
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/lttmtins/cloudstack 4.7-CLOUDSTACK-8966
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/cloudstack/pull/1314.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 #1314
----
commit b90019e0541b18580d654870d5cce2430072dbd7
Author: Anton Opgenoort <aopgeno...@schubergphilis.com>
Date: 2016-01-06T16:30:55Z
Fix mariadb related listCapacity bug (CLOUDSTACK-8966)
type bigint(20) with type varchar 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)
