Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
Guys, As Dag suggest, please stick with 5.5 or 5.6 version, anything else is not officially supported (whatever the "supported" means) since it might stop working again in next minor update or similar. Regards, Andrija On Fri, May 31, 2019, 22:33 Sean Lair wrote: > Update on the issue. Thanks Richard for the hint about MariaDB needing an > update (and everyone else that responded). It's crazy, I did a manual > select, mimicking the host_view SQL, and also received zero rows. I > modifed the select statement to remove the LEFT JOIN with > last_annotation_view, and the select statement returned rows as > expected... No idea (has to be a bug) why a LEFT OUTER JOIN would truncate > a return set like that... > > We were running MariaDB 10.0.33-1.el7.centos, did an upgrade to > 10.0.38-1.el7.centos. Then the host_view (and the GUI) started working as > expected... > > MariaDB bug?? > > > > -Original Message- > From: Dag Sonstebo [mailto:dag.sonst...@shapeblue.com] > Sent: Friday, May 31, 2019 4:47 AM > To: dev@cloudstack.apache.org > Subject: Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2 > > There are known issues with using MariaDB version 10 - I recommend you > stick to version 5.5 for the foreseeable future, and we have had several > cases of people having to downgrade lately. > > The issues you are seeing are most likely down to this Richard - you > should not have to make any DB schema changes / view changes to make the > GUI work. > > Regards, > Dag Sonstebo > Cloud Architect > ShapeBlue > > > On 31/05/2019, 10:34, "Richard Lawley" wrote: > > I don't believe the issue was related to views as such. When I was > trying to diagnose it earlier in the week I ran the query the view > runs manually, and got the same result. I then started removing > joined tables (even though they were all left joins so should not > matter), and data appeared once I removed the join to > last_annotation_view (which was empty). > > We had been running 4.8 on that server previously. The issue was > resolved by updating our database server (to MariaDB 10.1.40, from > 10.1.25 I think) - the same query started returning data properly. > > On Fri, 31 May 2019 at 09:35, Riepl, Gregor (SWISS TXT) > wrote: > > > > > > > - You did the upgrade on a newly built MySQL / MariaDB server > (keep in mind you can not at this point run MariaDB version 10.x) > > > - AND you imported database dumps to the new DB servers > > > - AND you didn't give 'cloud@%' permissions before the import: > > > GRANT ALL ON *.* TO 'cloud'@'%' IDENTIFIED BY '' WITH > GRANT OPTION; > > > > > > If these apply then the import fails after all tables are imported > but before the views are imported - hence the GUI struggles to display data. > > > > Could this be related to the fact that views are created with the > creating user's permissions by default? > > When I recently migrated our CS database to a new host, I ran into > errors because of subtle root user changes (i.e. different host parts) on > the new DB server. > > > > MySQL/MariaDB sets the SQL SECURITY to DEFINER by default, which > means that the exact user/hostname combo must exist on the target host when > importing a database. In my opinion, this makes absolutely no sense. The > default should be INVOKER, i.e. queries on the view should be executed with > the permissions of the user sending the query on the view, not those of the > user who created the view in the first place. > > > > See https://dev.mysql.com/doc/refman/8.0/en/create-view.html for > more info on the topic. > > > > Is there a particular reason why CloudStack uses the MySQL default? > Perhaps all views should be changed to use SQL SECURITY INVOKER? > > > > My quick fix to the problem was to comment out the DEFINER = ... > lines from the database dump during import: > > zcat cloudstack.sql.gz | grep -v "50013 DEFINER" | mysql -p > > > > dag.sonst...@shapeblue.com > www.shapeblue.com > Amadeus House, Floral Street, London WC2E 9DPUK @shapeblue > > > >
RE: Do not see KVM Hosts after 4.9.3 -> 4.11.2
Update on the issue. Thanks Richard for the hint about MariaDB needing an update (and everyone else that responded). It's crazy, I did a manual select, mimicking the host_view SQL, and also received zero rows. I modifed the select statement to remove the LEFT JOIN with last_annotation_view, and the select statement returned rows as expected... No idea (has to be a bug) why a LEFT OUTER JOIN would truncate a return set like that... We were running MariaDB 10.0.33-1.el7.centos, did an upgrade to 10.0.38-1.el7.centos. Then the host_view (and the GUI) started working as expected... MariaDB bug?? -Original Message- From: Dag Sonstebo [mailto:dag.sonst...@shapeblue.com] Sent: Friday, May 31, 2019 4:47 AM To: dev@cloudstack.apache.org Subject: Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2 There are known issues with using MariaDB version 10 - I recommend you stick to version 5.5 for the foreseeable future, and we have had several cases of people having to downgrade lately. The issues you are seeing are most likely down to this Richard - you should not have to make any DB schema changes / view changes to make the GUI work. Regards, Dag Sonstebo Cloud Architect ShapeBlue On 31/05/2019, 10:34, "Richard Lawley" wrote: I don't believe the issue was related to views as such. When I was trying to diagnose it earlier in the week I ran the query the view runs manually, and got the same result. I then started removing joined tables (even though they were all left joins so should not matter), and data appeared once I removed the join to last_annotation_view (which was empty). We had been running 4.8 on that server previously. The issue was resolved by updating our database server (to MariaDB 10.1.40, from 10.1.25 I think) - the same query started returning data properly. On Fri, 31 May 2019 at 09:35, Riepl, Gregor (SWISS TXT) wrote: > > > > - You did the upgrade on a newly built MySQL / MariaDB server (keep in mind you can not at this point run MariaDB version 10.x) > > - AND you imported database dumps to the new DB servers > > - AND you didn't give 'cloud@%' permissions before the import: > > GRANT ALL ON *.* TO 'cloud'@'%' IDENTIFIED BY '' WITH GRANT OPTION; > > > > If these apply then the import fails after all tables are imported but before the views are imported - hence the GUI struggles to display data. > > Could this be related to the fact that views are created with the creating user's permissions by default? > When I recently migrated our CS database to a new host, I ran into errors because of subtle root user changes (i.e. different host parts) on the new DB server. > > MySQL/MariaDB sets the SQL SECURITY to DEFINER by default, which means that the exact user/hostname combo must exist on the target host when importing a database. In my opinion, this makes absolutely no sense. The default should be INVOKER, i.e. queries on the view should be executed with the permissions of the user sending the query on the view, not those of the user who created the view in the first place. > > See https://dev.mysql.com/doc/refman/8.0/en/create-view.html for more info on the topic. > > Is there a particular reason why CloudStack uses the MySQL default? Perhaps all views should be changed to use SQL SECURITY INVOKER? > > My quick fix to the problem was to comment out the DEFINER = ... lines from the database dump during import: > zcat cloudstack.sql.gz | grep -v "50013 DEFINER" | mysql -p dag.sonst...@shapeblue.com www.shapeblue.com Amadeus House, Floral Street, London WC2E 9DPUK @shapeblue
Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
There are known issues with using MariaDB version 10 - I recommend you stick to version 5.5 for the foreseeable future, and we have had several cases of people having to downgrade lately. The issues you are seeing are most likely down to this Richard - you should not have to make any DB schema changes / view changes to make the GUI work. Regards, Dag Sonstebo Cloud Architect ShapeBlue On 31/05/2019, 10:34, "Richard Lawley" wrote: I don't believe the issue was related to views as such. When I was trying to diagnose it earlier in the week I ran the query the view runs manually, and got the same result. I then started removing joined tables (even though they were all left joins so should not matter), and data appeared once I removed the join to last_annotation_view (which was empty). We had been running 4.8 on that server previously. The issue was resolved by updating our database server (to MariaDB 10.1.40, from 10.1.25 I think) - the same query started returning data properly. On Fri, 31 May 2019 at 09:35, Riepl, Gregor (SWISS TXT) wrote: > > > > - You did the upgrade on a newly built MySQL / MariaDB server (keep in mind you can not at this point run MariaDB version 10.x) > > - AND you imported database dumps to the new DB servers > > - AND you didn't give 'cloud@%' permissions before the import: > > GRANT ALL ON *.* TO 'cloud'@'%' IDENTIFIED BY '' WITH GRANT OPTION; > > > > If these apply then the import fails after all tables are imported but before the views are imported - hence the GUI struggles to display data. > > Could this be related to the fact that views are created with the creating user's permissions by default? > When I recently migrated our CS database to a new host, I ran into errors because of subtle root user changes (i.e. different host parts) on the new DB server. > > MySQL/MariaDB sets the SQL SECURITY to DEFINER by default, which means that the exact user/hostname combo must exist on the target host when importing a database. In my opinion, this makes absolutely no sense. The default should be INVOKER, i.e. queries on the view should be executed with the permissions of the user sending the query on the view, not those of the user who created the view in the first place. > > See https://dev.mysql.com/doc/refman/8.0/en/create-view.html for more info on the topic. > > Is there a particular reason why CloudStack uses the MySQL default? Perhaps all views should be changed to use SQL SECURITY INVOKER? > > My quick fix to the problem was to comment out the DEFINER = ... lines from the database dump during import: > zcat cloudstack.sql.gz | grep -v "50013 DEFINER" | mysql -p dag.sonst...@shapeblue.com www.shapeblue.com Amadeus House, Floral Street, London WC2E 9DPUK @shapeblue
Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
I don't believe the issue was related to views as such. When I was trying to diagnose it earlier in the week I ran the query the view runs manually, and got the same result. I then started removing joined tables (even though they were all left joins so should not matter), and data appeared once I removed the join to last_annotation_view (which was empty). We had been running 4.8 on that server previously. The issue was resolved by updating our database server (to MariaDB 10.1.40, from 10.1.25 I think) - the same query started returning data properly. On Fri, 31 May 2019 at 09:35, Riepl, Gregor (SWISS TXT) wrote: > > > > - You did the upgrade on a newly built MySQL / MariaDB server (keep in mind > > you can not at this point run MariaDB version 10.x) > > - AND you imported database dumps to the new DB servers > > - AND you didn't give 'cloud@%' permissions before the import: > > GRANT ALL ON *.* TO 'cloud'@'%' IDENTIFIED BY '' WITH GRANT > > OPTION; > > > > If these apply then the import fails after all tables are imported but > > before the views are imported - hence the GUI struggles to display data. > > Could this be related to the fact that views are created with the creating > user's permissions by default? > When I recently migrated our CS database to a new host, I ran into errors > because of subtle root user changes (i.e. different host parts) on the new DB > server. > > MySQL/MariaDB sets the SQL SECURITY to DEFINER by default, which means that > the exact user/hostname combo must exist on the target host when importing a > database. In my opinion, this makes absolutely no sense. The default should > be INVOKER, i.e. queries on the view should be executed with the permissions > of the user sending the query on the view, not those of the user who created > the view in the first place. > > See https://dev.mysql.com/doc/refman/8.0/en/create-view.html for more info on > the topic. > > Is there a particular reason why CloudStack uses the MySQL default? Perhaps > all views should be changed to use SQL SECURITY INVOKER? > > My quick fix to the problem was to comment out the DEFINER = ... lines from > the database dump during import: > zcat cloudstack.sql.gz | grep -v "50013 DEFINER" | mysql -p
Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
> - You did the upgrade on a newly built MySQL / MariaDB server (keep in mind > you can not at this point run MariaDB version 10.x) > - AND you imported database dumps to the new DB servers > - AND you didn't give 'cloud@%' permissions before the import: > GRANT ALL ON *.* TO 'cloud'@'%' IDENTIFIED BY '' WITH GRANT OPTION; > > If these apply then the import fails after all tables are imported but before > the views are imported - hence the GUI struggles to display data. Could this be related to the fact that views are created with the creating user's permissions by default? When I recently migrated our CS database to a new host, I ran into errors because of subtle root user changes (i.e. different host parts) on the new DB server. MySQL/MariaDB sets the SQL SECURITY to DEFINER by default, which means that the exact user/hostname combo must exist on the target host when importing a database. In my opinion, this makes absolutely no sense. The default should be INVOKER, i.e. queries on the view should be executed with the permissions of the user sending the query on the view, not those of the user who created the view in the first place. See https://dev.mysql.com/doc/refman/8.0/en/create-view.html for more info on the topic. Is there a particular reason why CloudStack uses the MySQL default? Perhaps all views should be changed to use SQL SECURITY INVOKER? My quick fix to the problem was to comment out the DEFINER = ... lines from the database dump during import: zcat cloudstack.sql.gz | grep -v "50013 DEFINER" | mysql -p
Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
Sean,
As Rohit hints at this we see this often after upgrades if the following has
occurred:
- You did the upgrade on a newly built MySQL / MariaDB server (keep in mind you
can not at this point run MariaDB version 10.x)
- AND you imported database dumps to the new DB servers
- AND you didn't give 'cloud@%' permissions before the import:
GRANT ALL ON *.* TO 'cloud'@'%' IDENTIFIED BY '' WITH GRANT OPTION;
If these apply then the import fails after all tables are imported but before
the views are imported - hence the GUI struggles to display data.
Regards,
Dag Sonstebo
Cloud Architect
ShapeBlue
On 31/05/2019, 06:08, "Rohit Yadav" wrote:
Sean,
Can you try to drop and recreate host_view, as in your DB:
https://github.com/apache/cloudstack/blob/master/engine/schema/src/main/resources/META-INF/db/schema-41000to41100.sql#L159
Regards,
Rohit Yadav
Software Architect, ShapeBlue
https://www.shapeblue.com
From: Sean Lair
Sent: Friday, May 31, 2019 3:34:18 AM
To: dev@cloudstack.apache.org
Subject: Do not see KVM Hosts after 4.9.3 -> 4.11.2
After upgrading from 4.9.3 to 4.11.2, we no longer see hosts in the
CloudStack web-interface. Hitting the listHosts API directly also does not
return any results. It's just an empty list. When looking in the DB we do see
the hosts and there are rows where the version is 4.11.2.0.
The agent.log on the KVM (CentOS 7) looks good. We can see a list of
running VMs in CloudStack and their Running/Stopped statuses look good. We can
see Zones/Pods/Clusters, just not Hosts. The "Infrastructure" page also
correctly says "2" in the Host Count. But when we Click Hosts it just says:
No data to show
Any ideas?
Thanks
Sean
rohit.ya...@shapeblue.com
www.shapeblue.com
Amadeus House, Floral Street, London WC2E 9DPUK
@shapeblue
dag.sonst...@shapeblue.com
www.shapeblue.com
Amadeus House, Floral Street, London WC2E 9DPUK
@shapeblue
Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
Sean, Can you try to drop and recreate host_view, as in your DB: https://github.com/apache/cloudstack/blob/master/engine/schema/src/main/resources/META-INF/db/schema-41000to41100.sql#L159 Regards, Rohit Yadav Software Architect, ShapeBlue https://www.shapeblue.com From: Sean Lair Sent: Friday, May 31, 2019 3:34:18 AM To: dev@cloudstack.apache.org Subject: Do not see KVM Hosts after 4.9.3 -> 4.11.2 After upgrading from 4.9.3 to 4.11.2, we no longer see hosts in the CloudStack web-interface. Hitting the listHosts API directly also does not return any results. It's just an empty list. When looking in the DB we do see the hosts and there are rows where the version is 4.11.2.0. The agent.log on the KVM (CentOS 7) looks good. We can see a list of running VMs in CloudStack and their Running/Stopped statuses look good. We can see Zones/Pods/Clusters, just not Hosts. The "Infrastructure" page also correctly says "2" in the Host Count. But when we Click Hosts it just says: No data to show Any ideas? Thanks Sean rohit.ya...@shapeblue.com www.shapeblue.com Amadeus House, Floral Street, London WC2E 9DPUK @shapeblue
Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
Update your database server. We had the same thing this week - updating the mariadb server fixed it. The problem was the hosts_view which returned no data, presumably due to a bug. I've also seen someone else mention this using mysql server instead of mariadb. On Thu, 30 May 2019, 23:04 Sean Lair, wrote: > After upgrading from 4.9.3 to 4.11.2, we no longer see hosts in the > CloudStack web-interface. Hitting the listHosts API directly also does not > return any results. It's just an empty list. When looking in the DB we do > see the hosts and there are rows where the version is 4.11.2.0. > > The agent.log on the KVM (CentOS 7) looks good. We can see a list of > running VMs in CloudStack and their Running/Stopped statuses look good. We > can see Zones/Pods/Clusters, just not Hosts. The "Infrastructure" page > also correctly says "2" in the Host Count. But when we Click Hosts it just > says: > > No data to show > > Any ideas? > > Thanks > Sean > > > > > >
Do not see KVM Hosts after 4.9.3 -> 4.11.2
After upgrading from 4.9.3 to 4.11.2, we no longer see hosts in the CloudStack web-interface. Hitting the listHosts API directly also does not return any results. It's just an empty list. When looking in the DB we do see the hosts and there are rows where the version is 4.11.2.0. The agent.log on the KVM (CentOS 7) looks good. We can see a list of running VMs in CloudStack and their Running/Stopped statuses look good. We can see Zones/Pods/Clusters, just not Hosts. The "Infrastructure" page also correctly says "2" in the Host Count. But when we Click Hosts it just says: No data to show Any ideas? Thanks Sean
