Return-Path: X-Original-To: apmail-cloudstack-issues-archive@www.apache.org Delivered-To: apmail-cloudstack-issues-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 460C918765 for ; Thu, 22 Oct 2015 10:17:26 +0000 (UTC) Received: (qmail 72376 invoked by uid 500); 22 Oct 2015 10:16:28 -0000 Delivered-To: apmail-cloudstack-issues-archive@cloudstack.apache.org Received: (qmail 72343 invoked by uid 500); 22 Oct 2015 10:16:28 -0000 Mailing-List: contact issues-help@cloudstack.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@cloudstack.apache.org Delivered-To: mailing list issues@cloudstack.apache.org Received: (qmail 72332 invoked by uid 500); 22 Oct 2015 10:16:28 -0000 Delivered-To: apmail-incubator-cloudstack-issues@incubator.apache.org Received: (qmail 72328 invoked by uid 99); 22 Oct 2015 10:16:28 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Oct 2015 10:16:28 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id A43DB2C044E for ; Thu, 22 Oct 2015 10:16:27 +0000 (UTC) Date: Thu, 22 Oct 2015 10:16:27 +0000 (UTC) From: "Thomas Moroder (JIRA)" To: cloudstack-issues@incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (CLOUDSTACK-8980) CloudStack 4.5.2 not reporting correct total capacities on MariaDB-server 10.1 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 Thomas Moroder created CLOUDSTACK-8980: ------------------------------------------ Summary: CloudStack 4.5.2 not reporting correct total capaciti= es on MariaDB-server 10.1 Key: CLOUDSTACK-8980 URL: https://issues.apache.org/jira/browse/CLOUDSTACK-8980 Project: CloudStack Issue Type: Bug Security Level: Public (Anyone can view this level - this is the defa= ult.) Components: Management Server Affects Versions: 4.5.2 Environment: CentOS release 6.7 with MariaDB-server-10.1.8-1.el6.x= 86_64 Reporter: Thomas Moroder The CPU and memory total capacity as reported by the API and as visible on = the CS dashboard are shown as zero. This is due to the following query fail= ing to get correct results when the database is run on MariaDB 10.1: SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case = capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`= .`cluster_details` where cluster_details.name=3D 'cpuOvercommitRatio' AND c= luster_details.cluster_id=3Dcapacity.cluster_id)) when '0' then (sum(total_= capacity) * (select value from `cloud`.`cluster_details` where cluster_deta= ils.name=3D 'memoryOvercommitRatio' AND cluster_details.cluster_id=3Dcapaci= ty.cluster_id))else sum(total_capacity) end),((sum(capacity.used_capacity) = + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(= total_capacity) * (select value from `cloud`.`cluster_details` where cluste= r_details.name=3D 'cpuOvercommitRatio' AND cluster_details.cluster_id=3Dcap= acity.cluster_id)) when '0' then (sum(total_capacity) * (select value from = `cloud`.`cluster_details` where cluster_details.name=3D 'memoryOvercommitRa= tio' AND cluster_details.cluster_id=3Dcapacity.cluster_id)) else sum(total_= capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, po= d_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total_capa= city > 0 AND data_center_id is not null AND capacity_state=3D'Enabled' AND= capacity.data_center_id =3D ? AND capacity.cluster_id =3D ? AND capacity.c= apacity_type =3D ? GROUP BY data_center_id, pod_id, cluster_id, capacity_ty= pe The output is incorrect and like this: +-----------------------------+---------------------------------+----------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------+---------------+----------------+--------= +------------+ | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case cap= acity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`c= luster_details` where cluster_details.name=3D 'cpuOvercommitRatio' AND clus= ter_details.cluster_id=3Dcapacity.cluster_id)) when '0' then (sum(total_cap= acity) * (select value fro | percent | capacity_type | data_center_id | pod= _id | cluster_id | +-----------------------------+---------------------------------+----------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------+---------------+----------------+--------= +------------+ | 404750336000 | 0 | = = = = NULL | NULL | 0 | 3 | 3 = | 3 | +-----------------------------+---------------------------------+----------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------+---------------+----------------+--------= +------------+ After some analysis this seems due to the value of "capacity.cluster_id" as= set in the final WHERE-clause not being used in the (nested) previous WHER= E clauses like in: cluster_details.cluster_id=3Dcapacity.cluster_id If I manually update the query by specifying the cluster_id-number it works= correctly, e.g.: SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case = capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`= .`cluster_details` where cluster_details.name=3D 'cpuOvercommitRatio' AND c= luster_details.cluster_id=3D3)) when '0' then (sum(total_capacity) * (selec= t value from `cloud`.`cluster_details` where cluster_details.name=3D 'memor= yOvercommitRatio' AND cluster_details.cluster_id=3D3))else sum(total_capaci= ty) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) /= ( case capacity_type when 1 then (sum(total_capacity) * (select value from= `cloud`.`cluster_details` where cluster_details.name=3D 'cpuOvercommitRati= o' AND cluster_details.cluster_id=3D3)) when '0' then (sum(total_capacity) = * (select value from `cloud`.`cluster_details` where cluster_details.name= =3D 'memoryOvercommitRatio' AND cluster_details.cluster_id=3D3)) else sum(t= otal_capacity) end)) percent,capacity.capacity_type, capacity.data_center_i= d, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE total= _capacity > 0 AND data_center_id is not null AND capacity_state=3D'Enabled'= AND capacity.data_center_id =3D 3 AND capacity.cluster_id =3D 3 AND capac= ity.capacity_type =3D 0 GROUP BY data_center_id, pod_id, cluster_id, capaci= ty_type The output then is correct and like this: +-----------------------------+---------------------------------+----------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------------------+---------------+-------------= ---+--------+------------+ | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case cap= acity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`c= luster_details` where cluster_details.name=3D 'cpuOvercommitRatio' AND clus= ter_details.cluster_id=3D3)) when '0' then (sum(total_capacity) * (select v= alue from `cloud`.`cluster | percent | capacity_type | data_cen= ter_id | pod_id | cluster_id | +-----------------------------+---------------------------------+----------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------------------+---------------+-------------= ---+--------+------------+ | 404750336000 | 0 | = = = = 810895474688 | 0.49913996147990797 | 0 | = 3 | 3 | 3 | +-----------------------------+---------------------------------+----------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------------------+---------------+-------------= ---+--------+------------+ 1 row in set (0.00 sec) I don't know if this is an issue with MariaDB or correct SQL to use; the qu= ery works on older MySQL versions. As a workaround an older MySQL-version can be used. If this needs fixing, t= hen the CS management server could specify the cluster_id during the query = in all the required places. -- This message was sent by Atlassian JIRA (v6.3.4#6332)