Fixing Grails 1.0 Many-to-Many Mappings in 1.1

One of the more frequently asked questions about Grails involves its “backwards” Many-to-Many mapping. Given domain classes Foo and Bar, the join table foo_bar that’s generated for them will have a ‘foo’ column that points to the bar table and a ‘bar’ column that points to the foo table. In the comments of this bug it’s explained that this was intentional. But in 1.1 the Grails team decided to listen to the users and rework the approach.

Unfortunately this is a stealth fix that’s listed in the release notes along with many other changes but not in the breaking changes and it is very much a breaking change – unless you make changes your many-to-many relationships will not work after a 1.0 -> 1.1 upgrade. This is of particular interest to me as a developer on the Spring Security (Acegi) plugin since Role < -> User relationships are modeled as a many-to-many.

The plugin uses three domain classes, User, Role, and Requestmap. You can use whatever names you want but I’ll assume those names here; note that Requestmap isn’t affected by this issue. In Grails 1.0 your database would have two tables, user and role, and a many-to-many join table role_user, with backwards mappings, i.e. people_id refers to role and authorities_id refers to user:

CREATE TABLE role (
   id BIGINT NOT NULL auto_increment,
   version BIGINT NOT NULL,
   authority VARCHAR(255) NOT NULL UNIQUE,
   description VARCHAR(255) NOT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE user (
   id BIGINT NOT NULL auto_increment,
   version BIGINT NOT NULL,
   description VARCHAR(255) NOT NULL,
   email VARCHAR(255) NOT NULL,
   email_show bit NOT NULL,
   enabled bit NOT NULL,
   passwd VARCHAR(255) NOT NULL,
   user_real_name VARCHAR(255) NOT NULL,
   username VARCHAR(255) NOT NULL unique,
   PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE role_user (
   people_id BIGINT NOT NULL,
   authorities_id BIGINT NOT NULL,
   PRIMARY KEY (people_id, authorities_id)
) ENGINE=InnoDB;

ALTER TABLE role_user ADD INDEX FK1407FDF48F01F561 (people_id),
ADD CONSTRAINT FK1407FDF48F01F561 FOREIGN KEY (people_id) REFERENCES role (id);

ALTER TABLE role_user ADD INDEX FK1407FDF4CF6CDEE4 (authorities_id),
ADD CONSTRAINT FK1407FDF4CF6CDEE4 FOREIGN KEY (authorities_id) REFERENCES user (id);

Here the name of the mapping table is the owning end (‘Role’) followed by the owned end (‘User’). Note that the foreign key and index names (‘FK1407FDF48F01F561’, etc.) will probably be different for your tables.

However in Grails 1.1 the join table looks like this:

CREATE TABLE role_people (
	role_id BIGINT NOT NULL,
	user_id BIGINT NOT NULL,
	PRIMARY KEY (role_id, user_id)
) ENGINE=InnoDB;

ALTER TABLE role_people ADD INDEX FK28B75E7852388A1A (role_id),
ADD CONSTRAINT FK28B75E7852388A1A FOREIGN KEY (role_id) REFERENCES role (id);

ALTER TABLE role_people ADD INDEX FK28B75E78F7634DFA (user_id),
ADD CONSTRAINT FK28B75E78F7634DFA FOREIGN KEY (user_id) REFERENCES user (id);

and the mappings are correct – role_id references role and user_id references user. The name is the owning end (‘role’) followed by the collection name of the owned end (‘people’).

This means that your many-to-many relationships are completely broken, and in particular it means that you won’t be able to log in to your application if you’re using the Spring Security plugin. You’ll see errors like this in your logs:

ERROR springsecurity.GrailsDaoImpl  - User [foo] has no GrantedAuthority

So there are two options – migrate your data to the new table, or use the GORM mapping closure to get Grails to work in ‘1.0 mode’. The first option is cleaner, but if you’re accessing your join tables explicitly, e.g. for reporting queries using SQL, then this approach will break those.

For MySQL, the migration command is:

INSERT INTO role_people (role_id, user_id)
SELECT people_id, authorities_id FROM role_user;

and you’ll want to create the missing foreign keys and associated indexes:

ALTER TABLE role_people ADD INDEX FK28B75E7852388A1A (role_id),
ADD CONSTRAINT FK28B75E7852388A1A FOREIGN KEY (role_id) REFERENCES role (id);

ALTER TABLE role_people ADD INDEX FK28B75E78F7634DFA (user_id),
ADD CONSTRAINT FK28B75E78F7634DFA FOREIGN KEY (user_id) REFERENCES user (id);

Once you verify that this worked, you should drop role_user and its foreign keys:

ALTER TABLE role_user DROP FOREIGN KEY FK1407FDF48F01F561;
ALTER TABLE role_user DROP FOREIGN KEY FK1407FDF4CF6CDEE4;
DROP TABLE role_user;

Alternatively, to continue to use “1.0 mode”, you just need to add a mapping closure to each of your domain classes:

In Role.groovy:

static mapping = {
   people column: 'people_id', joinTable: 'role_user'
}

and in User.groovy:

static mapping = {
   authorities column: 'authorities_id', joinTable: 'role_user'
}

and your app will continue to work – and still be backwards 😉

One of the more frequently asked questions about Grails involves its “backwards” Many-to-Many mapping. Given domain classes Foo and Bar, the join table foo_bar that’s generated for them will have...