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" <rich...@richardlawley.com> 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)
    <gregor.ri...@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
    


dag.sonst...@shapeblue.com
www.shapeblue.com
Amadeus House, Floral Street, London  WC2E 9DPUK @shapeblue
  
 

Reply via email to