cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Richard Lawley <rich...@richardlawley.com>
Subject Re: Do not see KVM Hosts after 4.9.3 -> 4.11.2
Date Fri, 31 May 2019 09:34:16 GMT
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)
<Gregor.Riepl@swisstxt.ch> 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 '<PASSWORD>' 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

Mime
View raw message