US and Worldwide: +1 (866) 660-7555
Results 1 to 6 of 6

Thread: Multiple databases on the same connection?

  1. #1
    Join Date
    Dec 2013
    Posts
    3

    Default Multiple databases on the same connection?

    Hi,

    We have a MySQL server that has multiple databases, unfortunately some of the data between those databases is related and would be of use to build analysis on.

    I've tried calling the tables using 'database.table' in the schema/cube, however that doesn't seem to work (I get "SocketException: Connection timed out" when trying to load the schema in Saiku). When defining the data sources, I can't leave the database name blank to make it default to a top level view.

    The MySQL user/password has select permissions on all databases in question.

    Any advice on how to make this work, or can I use multiple data sources within a single schema/cube? Thanks for any suggestions.

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    I seriously doubt that would be supported. Mondrian relies on the JOIN and GROUP BY features of SQL and those operations are not supported across multiple databases.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Dec 2013
    Posts
    3

    Default

    MySQL supports JOIN and GROUP BY statements across different databases though. These databases are on the same host server, they're not separate MySQL servers, so should be able to be addressed like database1.table1 and database2.table2.

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    You are right! MySQL does support those SQL statements across different database schemas. I've never tried that before but it does indeed work.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Dec 2013
    Posts
    3

    Default

    I've tried a test just using one database, but using the 'database1.table1' syntax for naming the tables in order to keep things simple, and I'm getting an error:

    "MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`table1` group by `database1`.`table1`.`active` order by ISNULL(`database1`.`' at line 1"

    Does anyone have any suggestions? Is it possible for me to get the entire SQL error (or query) somehow (I don't see it in any obvious logs)?

    Is there a way to reference multiple data sources (JDBC connections) in a schema instead - so I could setup JDBC connections to each of the databases and then reference those somehow in my Mondrian schema?

  6. #6
    Join Date
    Nov 2008
    Posts
    777

    Default

    Using SchemaWorkbench, Mondrian doesn't seem to allow multiple JDBC connections. You can see the full stack trace in the .schemaWorkbench\workbench.log file located in your "home" directory.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •