Re: cloudstack-usage no longer working / error saving account to cloud_usage db
Hi Stephan, Glad it resolved your issue. When you upgrade CloudStack always make sure to shutdown all usage servers, as sometimes if the usage server tries to add data/entries to cloud_usage db while the upgraded mgmt server is trying to change the schema (of any tables in cloud_usage db) you might end up with bad data or entries. I'm not sure what happened in your environment, but I can speculate a case where usage server tries to add an account but the role_id column might not have added (alter statement did not run for some reason) this would cause NPEs as the column does not exist in the cloud.account table yet. Nevertheless, I'm glad it's not an issue with the code/release itself. Cheers. From: Stephan Seitz <s.se...@secretresearchfacility.com> Sent: 18 August 2016 18:06:49 To: d...@cloudstack.apache.org; users@cloudstack.apache.org Subject: Re: cloudstack-usage no longer working / error saving account to cloud_usage db Hi Rohit, thanks! Having a recent DB backup at hand and only based on guesses, I did some further experiments yesterday :) - Rerun /usr/share/cloudstack-common/scripts/util/migrate- dynamicroles.py - Checked cloudstack-usage 4.8.0.1 instead of 4.9 - Updated back to cloudstack-usage 4.9 ... and magically, it works after I changed the pid of the latest cloud_usage.usage_job to the corresponding pid. Though, I don't think thats the recommended way for a fix as I don't know why it's working... cloud.account shows role_id for every active account. cloud_usage.account now shows role_id for these accounts also. Only PrjAcct-$projectname-$id has role_id set to NULL, but I assume this is correct since Projects are not assigned to roles. Anyway, the metric/quota reports are working! - Stephan Am Donnerstag, den 18.08.2016, 09:03 + schrieb Rohit Yadav: > Hi Stephan, > > > In cloud_usage.account `role_id` can be NULL as there is no user of > this field within the usage server. In cloud.account, the `role_id` > should be automatically populated/migrated when you upgraded. From > your shared db query result, I'm not sure if that's a select query on > cloud.account or cloud_usage.account, can you confirm it? > > > Based on the exception, we can only get that if the account being > saved don't have any role_id defined. With a new account created and > usage records generated, I could not reproduce your issue. It is > likely caused by an account in cloud.account table whose role_id is > NULL. > > > Can you check (and share) that all of your accounts in cloud database > (cloud.account) have non-NULL role_id? Please fix anything that is > NULL. For root admin account type use set role_id=1, for resource > admin set role_id=2, for domain admin set role_id=3 and for user > account set role_type=4; > > > Regards. > > rohit.ya...@shapeblue.com > www.shapeblue.com<http://www.shapeblue.com> > 53 Chandos Place, Covent Garden, London WC2N 4HSUK > @shapeblue > > > rohit.ya...@shapeblue.com www.shapeblue.com 53 Chandos Place, Covent Garden, London WC2N 4HSUK @shapeblue
Re: cloudstack-usage no longer working / error saving account to cloud_usage db
Hi Rohit, thanks! Having a recent DB backup at hand and only based on guesses, I did some further experiments yesterday :) - Rerun /usr/share/cloudstack-common/scripts/util/migrate- dynamicroles.py - Checked cloudstack-usage 4.8.0.1 instead of 4.9 - Updated back to cloudstack-usage 4.9 ... and magically, it works after I changed the pid of the latest cloud_usage.usage_job to the corresponding pid. Though, I don't think thats the recommended way for a fix as I don't know why it's working... cloud.account shows role_id for every active account. cloud_usage.account now shows role_id for these accounts also. Only PrjAcct-$projectname-$id has role_id set to NULL, but I assume this is correct since Projects are not assigned to roles. Anyway, the metric/quota reports are working! - Stephan Am Donnerstag, den 18.08.2016, 09:03 + schrieb Rohit Yadav: > Hi Stephan, > > > In cloud_usage.account `role_id` can be NULL as there is no user of > this field within the usage server. In cloud.account, the `role_id` > should be automatically populated/migrated when you upgraded. From > your shared db query result, I'm not sure if that's a select query on > cloud.account or cloud_usage.account, can you confirm it? > > > Based on the exception, we can only get that if the account being > saved don't have any role_id defined. With a new account created and > usage records generated, I could not reproduce your issue. It is > likely caused by an account in cloud.account table whose role_id is > NULL. > > > Can you check (and share) that all of your accounts in cloud database > (cloud.account) have non-NULL role_id? Please fix anything that is > NULL. For root admin account type use set role_id=1, for resource > admin set role_id=2, for domain admin set role_id=3 and for user > account set role_type=4; > > > Regards. > > rohit.ya...@shapeblue.com > www.shapeblue.com > 53 Chandos Place, Covent Garden, London WC2N 4HSUK > @shapeblue > > >
Re: cloudstack-usage no longer working / error saving account to cloud_usage db
Hi Stephan, In cloud_usage.account `role_id` can be NULL as there is no user of this field within the usage server. In cloud.account, the `role_id` should be automatically populated/migrated when you upgraded. From your shared db query result, I'm not sure if that's a select query on cloud.account or cloud_usage.account, can you confirm it? Based on the exception, we can only get that if the account being saved don't have any role_id defined. With a new account created and usage records generated, I could not reproduce your issue. It is likely caused by an account in cloud.account table whose role_id is NULL. Can you check (and share) that all of your accounts in cloud database (cloud.account) have non-NULL role_id? Please fix anything that is NULL. For root admin account type use set role_id=1, for resource admin set role_id=2, for domain admin set role_id=3 and for user account set role_type=4; Regards. rohit.ya...@shapeblue.com www.shapeblue.com 53 Chandos Place, Covent Garden, London WC2N 4HSUK @shapeblue
Re: cloudstack-usage no longer working / error saving account to cloud_usage db
role_id in account table shouldn't be null. If the upgrade was successful, it should have updated it with proper mapping from account type. (check the db upgrade log for any exceptions) I suggest you do the update manually following the logic at https://github.com/apache/cloudstack/blob/4.9/engine/schema/src/com/cloud/upgrade/dao/Upgrade481to490.java#L68-L92 Also check that roles and role_permissions tables have the required data. ~ Rajani http://cloudplatform.accelerite.com/ On August 16, 2016 at 7:10 PM, Stephan Seitz (s.se...@secretresearchfacility.com) wrote: Hi! We've recently noticed cloudstack-usage is no longer able to write usage data into mysql. I assume this happened after updateing to 4.8.0.1 or 4.9 - we missed that error in our testsetup... Here's the usage.log: http://pastebin.com/Zpvnw4xX Finally it fails with following message: ERROR [usage.dao.UsageDaoImpl] (Usage-Job-1:null) (logid:) error saving account to cloud_usage db I've checked the changes between 4.8 and 4.9 https://fossies.org/diffs/apache-cloudstack/4.8.0.1-src_vs_4.9.0-src/en gine/schema/src/com/cloud/usage/dao/UsageDaoImpl.java-diff.html cloud_usage.account now shows role_id, but that table looks good to me: mysql> show create table account \G *** 1. row *** Table: account Create Table: CREATE TABLE `account` ( `id` bigint(20) unsigned NOT NULL, `account_name` varchar(100) DEFAULT NULL COMMENT 'an account name set by the creator of the account, defaults to username for single accounts', `uuid` varchar(40) DEFAULT NULL, `type` int(1) unsigned NOT NULL, `role_id` bigint(20) unsigned DEFAULT NULL, `domain_id` bigint(20) unsigned DEFAULT NULL, `state` varchar(10) NOT NULL DEFAULT 'enabled', `removed` datetime DEFAULT NULL COMMENT 'date removed', `cleanup_needed` tinyint(1) NOT NULL DEFAULT '0', `network_domain` varchar(100) DEFAULT NULL COMMENT 'Network domain name of the Vms of the account', `default_zone_id` bigint(20) unsigned DEFAULT NULL, `default` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '1 if account is default', PRIMARY KEY (`id`), UNIQUE KEY `uc_account__uuid` (`uuid`), KEY `i_account__removed` (`removed`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Having a look at that data, I'm a bit confused since uuid, network_domain and default_zone_id are NULL for every account. As an example: mysql> select * from account limit 1 \G *** 1. row *** id: 1 account_name: system uuid: NULL type: 1 role_id: NULL domain_id: 1 state: enabled removed: NULL cleanup_needed: 0 network_domain: NULL default_zone_id: NULL default: 0 1 row in set (0.00 sec) I've already looked at plain SQL statements logged by the mysqld, but that's really messy and grepping for INSERT/UPDATE queries defined in UsageDaoImpl didn't show anything. Finally grepping for 'statistics' in the raw sql-log doesn't result a line. Could someone please shed some light how I could get the usage service collecting and reporting data again? Thanks in advance! cheers, - Stephan