Mysql 8 - How to report of all schemas?
We're using MySQL and wish to report on all of their schemas (databases in MySQL terms) in one view.
In older versions of MySQL connectors you had to specify this schema in the data source connection, but with the new 8.0.x jdbc driver, you don’t need the reference in the connection.
This means that you could see tables from all schemas in the data source connection and also in a single view.
The problem is that when you start using tables from different schemas in the view, there are errors because the schema is not included in the SQL queries.
I have the option ticked in the data source connection to ‘include schema’, but I think this more refers to a typical schema within a database, rather than the MySQL schema which is a database.
Has anyone encountered this issue before or have any work arounds?
The best I can at the moment is use virtual tables for all tables and reference the schema in the SQL, but this is not best practice from a query performance side.