a really weird problem with a ternary setup. My database runs case insensitive queries. Now, if I try to load a group (a.k.a. a role) by a name with casing different than that specified in the database two things happen: 1) the user set does not contain any items (even though the query return the proper number of user records; 3 in my test case); 2) When the session closes, Hibernate issues a delete query that tries to remove user/group associations for the association table for the group being queried (see the queries below).

The exception trace i'm providing happens because I revoked the delete privilege from the current database user.

in the database I have a group named "administrator". In code, I load it as "Administrator".

So what do I need to do to prevent this deletion behavior from happening?



Dmitry


The mappings are provided below. Here's what the tables look like:

Table core_role:

fields: core_role_id, role, title
1, administrator, Administrators
2, dbo, Department Business Officers
3, fiscalaffairs, Fiscal Affairs
4, deansoffice, Dean's Office
5, HRComp, HR Compensation
6, Employees, HS Employees
7, manager, Tomcat Managers
8, admin, Tomcat Administrators
9, hrcontacts, HR Contacts

Table core_user_role:

fields: core_user_role_id, name, user_id, role
1, dberan, 156, administrator
2, lmajor, 1, administrator
6, respir, 13, deansoffice
7, mppal, 138, HRComp
12, k1chg, 176, administrator
17, ehidago, 174, deansoffice
18, radsm, 71, dbo
19, lmaors, 1, manager


all the users above are also in the user table.

Hibernate version:
2.1.3
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Group group = session.load(Group.class, "Administrator");
group.getUsers().size();

Full stack trace of any exception that occurs:

edu.ucsd.som.util.DataStoreException: net.sf.hibernate.JDBCException: Could not execute JDBC batch update
at edu.ucsd.som.hibernate.AbstractHibernateDataStore.endSession(AbstractHibernateDataStore.java:110)
at edu.ucsd.som.portal.junit.SomPortalTestCase.tearDown(SomPortalTestCase.java:67)
at com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)
Caused by: net.sf.hibernate.JDBCException: Could not execute JDBC batch update
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:129)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2410)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2362)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2229)
at edu.ucsd.som.hibernate.AbstractHibernateDataStore.endSession(AbstractHibernateDataStore.java:103)
... 19 more
Caused by: java.sql.BatchUpdateException: General error, message from server: "delete command denied to user: 'som_fiscal@cmg203-106.ucsd.edu' for table 'core_user_role'"
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1446)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:122)
... 23 more

Name and version of the database you are using:

MySql 3.23.54

The generated SQL (show_sql=true):

1598 Query select group0_.role as role0_, group0_.title as title0_, users1_.user_id as user_id__, users1_.role as role__ from core_role group0_ left outer join core_user_role users1_ on group0_.role=users1_.role where group0_.role='Administrator'

041023 19:50:07 1598 Query select users0_.user_id as user_id__, users0_.role as role__ from core_user_role users0_ where users0_.role='Administrator'

1598 Query select somuser0_.core_user_id as core_use1_0_, somuser0_.name as name0_, somuser0_.first_name as first_name0_, somuser0_.last_name as last_name0_, somuser0_.email as email0_, somuser0_.employee_id as employee6_0_ from core_user somuser0_ where ((somuser0_.core_user_id=156) or (somuser0_.core_user_id=1) or (somuser0_.core_user_id=176))

041023 19:50:09 1598 Query delete from core_user_role where role='administrator'


Debug level Hibernate log excerpt: