Mapping large domain model over several MySQL databases - GrailsDomainBinder + hbm2ddl issues

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Mapping large domain model over several MySQL databases - GrailsDomainBinder + hbm2ddl issues

Tim Pedersen
Hi all,

Executive summary: mapping large domain model across lots of MySQL databases gives rise to certain database schema/catalog naming problems.

Question: has anybody managed to configure Grails so that the Grails domain mapping + hbm2ddl combination work correctly (smoothly?) with joins across multiple MySQL Databases ( - or alternatively - managed to get the Database Migration Plugin to work for Grails domain models that span multiple MySQL Databases without having to manually edit lots of changelogs...)

Background: we are developing a large Grails app (1.3.7) that has ~80 domain classes mapped across 13 MySql databases/schemas (as you probably know in MySql database & schema are used interchangeably. However, it seems the MySQL JDBC driver uses 'catalog' in the various JDBC APIs to map to MySQL databases, and ignores 'schema'. )

We chose to partition the database this way, because other applications will be using subsets of this data. (E.g. we have an 'org' database to hold common information about employees, a 'gis' database to hold common information about addresses, suburbs, geocoding, etc.). The domain model package naming reflects this partitioning (and will be reused in other applications in future.)

There are a lot of many-to-many relationships, often spanning databases. E.g. a 'Case' (mapped to has many 'OffenceReports' (mapped to ors.offence_report), with Case belongsTo OffenceReport. This is where the problems start, and seem to arise because of the lack of distinction between 'schema' and 'catalog' in MySQL...

In order for the hbm2ddl to work correctly with MySQL in this situation (e.g. when you want to use dbCreate="update" in Datasources.groovy), it is necessary to specify a table mapping in the domain class that specifies the catalog; viz:

static mappings = {
  table name:"offence_report", catalog:"ors"

If you don't specify a catalog, the table is mapped into whatever the default catalog you specify in the database connection string in Datasources.groovy.

All well and good, hbm2ddl interrogates MySQL, gets the metadata and creates/updates the table accordingly. (FYI if you log what is happening in MySQL you see queries like "SHOW FULL TABLES FROM `ors` LIKE 'offence_report'" and "SHOW FULL COLUMNS FROM `offence_report` FROM `ors` LIKE '%'" when Hibernate is working out what it needs to do.)

The *problem* arises when mapping join tables. Whether or not you directly specify a joinTable in mappings, it appears Grails will map the resulting table to the *schema* of the owning table, and the catalog name of the default database. You can see this in, ~line 1121:

 private static void bindCollectionTable(GrailsDomainClassProperty property, Mappings mappings,
            Collection collection, Table ownerTable, String sessionFactoryBeanName) {

        String prefix = ownerTable.getSchema();
        String tableName = (prefix == null ? "" : prefix + '.') + calculateTableForMany(property, sessionFactoryBeanName);
              mappings.getSchemaName(), mappings.getCatalogName(),
              tableName, null, false));

You can't specify both 'schema' and 'catalog' in a table mapping for MySQL because you end up with generated queries like 'create table ors.ors.offence_report...' which crash and burn.

The way around it for the moment appears to to put the database+name in the joinTable mapping, and not specify any schema in the table mapping:

class OffenceReport{
static hasMany = [cases:Case]

static mappings = {
  table name:"offence_report", catalog:"ors"
  cases joinTable:"cms.case__offence_report"

This works as far as automatically creating the join table initially, but any further updates (e.g. dbCreate="update") fail on the join tables, because database mapping falls over on resolving the join table names +catalog correctly. So you tend to get a lot of errors like:
"ERROR hbm2ddl.SchemaUpdate  - Table 'case__offence_report' already exists"
(MySQL logs show commands like "SHOW FULL TABLES FROM `cas` LIKE 'case__offence_report'" - where `cas` is specified as the default database in the JDBC connection string - followed by "create table cms.case__offence_report...", which shows the correct catalog is not being picked up the by domain mapper, causing the spurious create table statement.)

This error noise is not too bad as it only occurs for the joinTables mapped with names like above. The setup works in the most part, and is only a problem when we are modifying relationships etc.

To prevent this problem it would be good if we had finer control over the disposition of joinTables - though reading the Hibernate documentation I am not sure if you can set the catalog of join tables in the Hibernate mappings...??? If it were so, then it would be great if a Grails guru could mod GrailsDomainBinder to allow specifying schema and/or catalog in joinTable mappings.

Anyway, has anybody come up against this situation...and what did you do to get it working smoothly?? What have been others' experiences with large apps on multi-database MySQL instances?

Tim Pedersen

PS. I'm hoping to use the Database Migration Plugin to resolve these issues - but that has issues of its own wrt. multiple catalogs/schemas.

PPS. A colleague has tried this in Grails 2.0, same results...