[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-2221) MySQL temp table DDL and isolation
Steve Ebersole (JIRA)
noreply at atlassian.com
Tue Dec 5 16:21:08 EST 2006
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2221?page=all ]
Steve Ebersole updated HHH-2221:
--------------------------------
Summary: MySQL temp table DDL and isolation (was: Bulk-update of joined-subclass doesn't work with )
Fix Version: 3.2.2
Description:
On MySQL, temporary table DDL operations do not cause implicit transaction commits, even though the driver metadata reports that DDL does cause implicit transaction commits. The reason being that MySQL handles temp table DDL operations differently than non-temp DDL, which the JDBC metadata does not account for.
Thus, need a way for the dialect to unequivocally force the temp table DDL to be performed in the current transaction regardless of what the driver reports...
was:
I've created the following test (in JoinedSubclassTest) which reporduced the problem:
public void testBulkUpdateJoinedSubclass() {
Session s = openSession();
Transaction t = s.beginTransaction();
Employee mark = new Employee();
mark.setName("Mark");
mark.setTitle("internal sales");
mark.setSex('M');
mark.setAddress("buckhead");
mark.setZip("30305");
mark.setCountry("USA");
s.save(mark);
String hql = "update Employee employee set employee.sex=:newSex where employee.id in (:ids)";
Query query = s.createQuery(hql);
query.setCharacter("newSex", 'F');
List ids = new ArrayList();
ids.add(Long.valueOf(mark.getId()));
query.setParameterList("ids", ids);
query.executeUpdate();
s.delete(mark);
t.commit();
s.close();
}
and run it with the following properties:
-Dhibernate.dialect=org.hibernate.dialect.MySQLDialect
-Dhibernate.connection.driver_class=com.mysql.jdbc.Driver
-Dhibernate.connection.url=jdbc:mysql://127.0.0.1/hibernateTest
-Dhibernate.connection.username=username
-Dhibernate.connection.password=password
This produces the following error:
org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:127)
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)
at org.hibernate.test.joinedsubclass.JoinedSubclassTest.testBulkUpdateJoinedSubclass(JoinedSubclassTest.java:131)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:589)
at junit.framework.TestCase.runTest(TestCase.java:164)
at org.hibernate.test.TestCase.runTest(TestCase.java:247)
at junit.framework.TestCase.runBare(TestCase.java:130)
at org.hibernate.test.TestCase.runBare(TestCase.java:313)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:228)
at junit.framework.TestSuite.run(TestSuite.java:223)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'hibernateTest.HT_JEmployee' doesn't exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:118)
... 26 more
This works fine in hibernate 3.1.3.
The problem seems to be in AbstractStatementExecutor. It seems to assume that if a DDL statement, in general, results in a commit that this will also happen for a temporary table. In the case of MySQL, at least, this is not the case. The code in AbstractStatementExecutor goes and creates the temporary table in a new connection, which, in the case of MySQL, will
not be visible in the current transaction.
http://fisheye.jboss.com/browse/Hibernate/branches/Branch_3_2/Hibernate3/src/org/hibernate/hql/ast/exec/AbstractStatementExecutor.java?r1=9262&r2=9989
Assign To: Steve Ebersole
ORIGINAL DESCRIPTION:
I've created the following test (in JoinedSubclassTest) which reporduced the problem:
public void testBulkUpdateJoinedSubclass() {
Session s = openSession();
Transaction t = s.beginTransaction();
Employee mark = new Employee();
mark.setName("Mark");
mark.setTitle("internal sales");
mark.setSex('M');
mark.setAddress("buckhead");
mark.setZip("30305");
mark.setCountry("USA");
s.save(mark);
String hql = "update Employee employee set employee.sex=:newSex where employee.id in (:ids)";
Query query = s.createQuery(hql);
query.setCharacter("newSex", 'F');
List ids = new ArrayList();
ids.add(Long.valueOf(mark.getId()));
query.setParameterList("ids", ids);
query.executeUpdate();
s.delete(mark);
t.commit();
s.close();
}
and run it with the following properties:
-Dhibernate.dialect=org.hibernate.dialect.MySQLDialect
-Dhibernate.connection.driver_class=com.mysql.jdbc.Driver
-Dhibernate.connection.url=jdbc:mysql://127.0.0.1/hibernateTest
-Dhibernate.connection.username=username
-Dhibernate.connection.password=password
This produces the following error:
org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:127)
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)
at org.hibernate.test.joinedsubclass.JoinedSubclassTest.testBulkUpdateJoinedSubclass(JoinedSubclassTest.java:131)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:589)
at junit.framework.TestCase.runTest(TestCase.java:164)
at org.hibernate.test.TestCase.runTest(TestCase.java:247)
at junit.framework.TestCase.runBare(TestCase.java:130)
at org.hibernate.test.TestCase.runBare(TestCase.java:313)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:228)
at junit.framework.TestSuite.run(TestSuite.java:223)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'hibernateTest.HT_JEmployee' doesn't exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:118)
... 26 more
This works fine in hibernate 3.1.3.
The problem seems to be in AbstractStatementExecutor. It seems to assume that if a DDL statement, in general, results in a commit that this will also happen for a temporary table. In the case of MySQL, at least, this is not the case. The code in AbstractStatementExecutor goes and creates the temporary table in a new connection, which, in the case of MySQL, will
not be visible in the current transaction.
http://fisheye.jboss.com/browse/Hibernate/branches/Branch_3_2/Hibernate3/src/org/hibernate/hql/ast/exec/AbstractStatementExecutor.java?r1=9262&r2=9989
> MySQL temp table DDL and isolation
> ----------------------------------
>
> Key: HHH-2221
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2221
> Project: Hibernate3
> Type: Bug
> Components: core
> Versions: 3.2.0.ga
> Environment: INFO: Hibernate 3.2.0
> INFO: RDBMS: MySQL, version: 5.0.22
> INFO: JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.0.4 ( $Date: 2006-10-19 17:47:48 +0200 (Thu, 19 Oct 2006) $, $Revision: 5908 $ )
> Reporter: Donnchadh O Donnabhain
> Assignee: Steve Ebersole
> Fix For: 3.2.2
> Attachments: HHH-2221.patch, JoinedSubclassTest.java
>
>
> On MySQL, temporary table DDL operations do not cause implicit transaction commits, even though the driver metadata reports that DDL does cause implicit transaction commits. The reason being that MySQL handles temp table DDL operations differently than non-temp DDL, which the JDBC metadata does not account for.
> Thus, need a way for the dialect to unequivocally force the temp table DDL to be performed in the current transaction regardless of what the driver reports...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list