Thursday, September 8, 2011

Grails, multi-tenant plugin and a bag of small issues

If you'll ever need a multi-tenant solution and you're lucky enough to use Grails going with the multi-tenant-core plugin is definitely the way to go. It's pretty easy to use in the "multiTenant" mode but some strange issues come up when trying yo use the "singleTenant" mode. In this installment I'm going to walk you through a solution that'll allow you to understand how things are and what you should avoid.

The debug solution

To make our "explorer" live easier we're going to provide a custom tenant resolver so that we'll be able to specify a query string like ?tid=1 to select the tenant with id = 1.
package org.example

import javax.servlet.http.HttpServletRequest
import grails.plugin.multitenant.core.TenantResolver

class TestTenantResolver implements TenantResolver {
    Integer getTenantFromRequest(HttpServletRequest request) {
        def tid = request.queryString =~ /tid=(\d+)/
        return tid ? Integer.parseInt(tid[0][1]) : 0
To use it just register a bean with name tenantResolver in resources.groovy like
this and you're all set.

The data sources

Although the documentation will tell you that in the tenant configuration DSL you can specify JDBC URLs directly it is unfortunately not true. You need your JDBC datasources registered in JNDI for the multi-tenant plugin to pick them up. It's done in Config.groovy like this:
grails.naming.entries = [
    "jdbc/foo": [
        type: "javax.sql.DataSource", 
        driverClassName: "org.hsqldb.jdbcDriver",
        url: "jdbc:hsqldb:file:target/db-dev-tid-1;shutdown=true",
        username: "sa",
        password: "",
        maxActive: "8",
        maxIdle: "4"
    "jdbc/bar": [
        type: "javax.sql.DataSource", 
        driverClassName: "org.hsqldb.jdbcDriver",
        url: "jdbc:hsqldb:file:target/db-dev-tid-2;shutdown=true",
        username: "sa",
        password: "",
        maxActive: "8",
        maxIdle: "4"
And then you need to tell the plugin about your datasources:
tenant {
    mode = "singleTenant"
    dataSourceTenantMap {
        t1 = "java:comp/env/jdbc/foo" 
        t2 = "java:comp/env/jdbc/bar" 

Catch No. 0 (zero)

Be warned that the tenant 0 (zero) has a special meaning that's not mentioned anywhere in the docs. It means "use the connection returned originally by TransactionAwareDataSourceProxy". This means that tenant with id 0 is off limits for you. Don't ever use it!!!


Other than the 2 small issues the plugin is really fun to work with. Good job guys!


WaiLan said...

May i know version of
multi tenant plugin (core)
mysql connector

i get this error:
com.mysql.jdbc.exceptions.jdbc4.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 ...

Thank you

Matthias Hryniszak said...

The error you're getting indicates you have a problem with an SQL statement. Check what is that you're actually doing...

I've been testing my setup using H2 database - not MySQL...