Just a quick note regarding the schema.
On mysql 5.0.37 the last constraint could not be created. You get this error:
Code:
mysql> ALTER TABLE rbac_users_has_roles ADD CONSTRAINT fk_users_has_roles_roles FOREIGN KEY (roles_id) REFERENCES rbac_roles (id);
ERROR 1005 (HY000): Can't create table './irie/#sql-4d0_86.frm' (errno: 150)
Which through a strange series of translations thanks to bad error messages equals:
Code:
mysql> SHOW INNODB STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
070513 11:51:55 Error in foreign key constraint of table irie/#sql-4d0_86:
FOREIGN KEY (roles_id) REFERENCES rbac_roles (id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
In short, rbac_users_has_roles(roles_id) has a different type to rbac_roles (id).
To fix this I removed the unsigned from rbac_users_has_roles(roles_id)
Code:
mysql> alter table rbac_users_has_roles change roles_id roles_id int(11) NOT NULL;
I guess this will only impact if you get beyond 2147483647 rows in rbac_users_has_roles? (a scary thought).