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
