[
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 <[email protected]>
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)
