Hibernate version: 3.2 CR5 with MySQL 5

When I execute some bulk delete HQL statement such as

String hqlStatement = "delete Agent";
Query query = session.createQuery(hqlStatement);
int updatedEntities = query.executeUpdate();

There is a SQLGrammarProblem:

Hibernate:
insert
into
HT_PTY_PartyRole
select
agentimpl0_.oid as oid
from
PTY_PartyRole agentimpl0_
inner join
PTY_Agent agentimpl0_1_
on agentimpl0_.oid=agentimpl0_1_.partyRoleId
where
dt='AG'
[main] 13:47:56 WARN (JDBCExceptionReporter.logExceptions:71) - SQL Error: 1146, SQLState: 42S02
[main] 13:47:56 ERROR (JDBCExceptionReporter.logExceptions:72) - Table 'ht_pty_partyrole' doesn't exist
org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk delete
Caused by: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk delete
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:102)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)


I have traced into the code in org.hibernate.hql.ast.exec.AbstractStatementExecutor:


protected void createTemporaryTableIfNecessary(final Queryable persister, final SessionImplementor session) {
// Don't really know all the codes required to adequately decipher returned jdbc exceptions here.
// simply allow the failure to be eaten and the subsequent insert-selects/deletes should fail
IsolatedWork work = new IsolatedWork() {
public void doWork(Connection connection) throws HibernateException {
Statement stmnt = null;
try {
stmnt = connection.createStatement();
stmnt.executeUpdate(persister.getTemporaryIdTableDDL());
} catch (Throwable t) {
log.debug("unable to create temporary id table [" + t.getMessage() + "]");
} finally {
if (stmnt != null) {
try {
stmnt.close();
} catch (Throwable ignore) {
// ignore
}
}
}
}
};
if (getFactory().getSettings().isDataDefinitionImplicitCommit() || getFactory().getDialect().performTemporaryTableDDLInIsolation()) {
//if (false)
if (getFactory().getSettings().isDataDefinitionInTransactionSupported()) {
Isolater.doIsolatedWork(work, session);
} else {
Isolater.doNonTransactedWork(work, session);
}
} else {
work.doWork(session.getJDBCContext().getConnectionManager().getConnection());
session.getJDBCContext().getConnectionManager().afterStatement();
}
}

It seems that the temporary table is created in another connection, thus the table not existed error occurred. If I swith to other path (I changed the condition :
if (getFactory().getSettings().isDataDefinitionImplicitCommit() || getFactory().getDialect().performTemporaryTableDDLInIsolation()) {

to if (false)

the problem disappears.

Is this a bug or do I miss something?

I'm seeing the same behaviour updating an object with a joined-subclass mapping.

The problem is that DDL statements in MySQL do cause the current transaction to be commited, but creating a _temporary_ table does not cause the current transaction to be commited. Having looked at the code I haven't seen a nice workaround without modifying the hibernate code. I'm not sure under what circumstances it is useful to create a temporary table in a separate connection. Certainly not with MySQL as they arn't visible from another connection.

I have only started seeing this problem when i tried to upgrade to hibernate 3.2. It worked fine in hibernate 3.1.2 and 3.1.3

Donnchadh

This seems to be the change that breaks it for MySQL:
http://fisheye.jboss.com/browse/Hiberna ... 62&r2=9989

Donnchadh

I've logged this in JIRA:
http://opensource.atlassian.com/project ... e/HHH-2221

Donnchadh