Hibernate session.delete issuing wrong SQL command

Question

I am trying to delete something from database using hibernate's session.delete(obj). But it is not working, shows incorrect SQL command.

It issues the SQL command "delete Instructor where id=?". But "from" is missing in the SQL command that hibernate is issuing and I think this is what is causing the error.

//Main class code
//77 is the id
 InstructorDetail tempID=session.get(InstructorDetail.class, 77);
 session.delete(tempID);

Error stack trace:

Aug 14, 2019 10:54:20 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {6.0.0.Alpha2}
Aug 14, 2019 10:54:20 AM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
Aug 14, 2019 10:54:21 AM org.hibernate.boot.jaxb.internal.stax.LocalXmlResourceResolver resolveEntity
WARN: HHH90000012: Recognized obsolete hibernate namespace http://hibernate.sourceforge.net/hibernate-configuration. Use namespace http://www.hibernate.org/dtd/hibernate-configuration instead.  Support for obsolete DTD/XSD namespaces may be removed at any time.
Aug 14, 2019 10:54:21 AM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/hibernateTutorial]
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {user=root, password=****}
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Aug 14, 2019 10:54:22 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 5 (min=1)
Aug 14, 2019 10:54:22 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
Aug 14, 2019 10:54:23 AM org.hibernate.envers.boot.internal.EnversServiceImpl configure
INFO: Envers integration enabled? : true
Aug 14, 2019 10:54:23 AM org.hibernate.envers.boot.internal.AdditionalJaxbMappingProducerImpl produceAdditionalMappings
INFO: Generating Envers XML mappings.
Aug 14, 2019 10:54:23 AM org.hibernate.validator.internal.util.Version <clinit>
INFO: HV000001: Hibernate Validator 6.1.0.Alpha6
Aug 14, 2019 10:54:23 AM org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@2a9bc08f] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Hibernate: select i1_0.id, i1_0.hobby, i2_0.id, i2_0.name, i1_0.youtube_channel from instructor_detail as i1_0 left outer join (Instructor as i2_0) on i1_0.id=i2_0.instructor_detail_id where i1_0.id=?
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.RowReaderStandardImpl readRow
INFO: ---Processing Row---
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 2] - [76]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 0] - [77]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 3] - [Atul]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 1] - [Cricket]
Aug 14, 2019 10:54:24 AM org.hibernate.sql.results.internal.domain.basic.BasicResultAssembler assemble
INFO: Extracted value [Position 4] - [None]
Hibernate: delete Instructor where id=?
Aug 14, 2019 10:54:24 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1064, SQLState: 42000
Aug 14, 2019 10:54:24 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where id=76' at line 1
Aug 14, 2019 10:54:24 AM org.hibernate.internal.ExceptionMapperStandardImpl mapManagedFlushFailure
ERROR: HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [delete Instructor where id=?]]
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [delete Instructor where id=?]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1436)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:487)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2786)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1932)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:456)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:272)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:104)
    at com.aks.Main.main(Main.java:19)
Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [delete Instructor where id=?]
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.sql.exec.internal.JdbcMutationExecutorImpl.execute(JdbcMutationExecutorImpl.java:77)
    at org.hibernate.sql.exec.internal.JdbcMutationExecutorImpl.execute(JdbcMutationExecutorImpl.java:109)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.executeOperation(SingleTableEntityTypeDescriptor.java:768)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.executeDelete(SingleTableEntityTypeDescriptor.java:514)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.deleteRootTable(SingleTableEntityTypeDescriptor.java:458)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.delete(SingleTableEntityTypeDescriptor.java:429)
    at org.hibernate.metamodel.model.domain.internal.entity.SingleTableEntityTypeDescriptor.delete(SingleTableEntityTypeDescriptor.java:420)
    at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:96)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:602)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:474)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:358)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1430)
    ... 9 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where id=76' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1094)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1042)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1345)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1027)
    at org.hibernate.sql.exec.internal.JdbcMutationExecutorImpl.execute(JdbcMutationExecutorImpl.java:68)
    ... 21 more

Can you please share the complete stacktrace of the logged exception? — Safeer Ansari, Aug 13 '19 at 13:08
Sure. I've shared it. Please find it in the updated question. — aks, Aug 14 '19 at 05:32

Stephen C · Answer 1 · 2019-08-14T06:34:57.303

It turns out that the from is optional in delete from table where ... in some dialects of SQL: see In SQL syntax, is 'from' in 'delete from' optional if you plan to use 'where'?

So I suspect that you have configured Hibernate with a "dialect" that is not compatible with the database product that you are using. This page explains how to configure the dialect and gives a list of supported Dialect classnames.

UPDATE - So it looks like you are using an appropriate Dialect. But I also see from the log that you are using a mixed collection of Hibernate components / JARs.

Hibernate Core {6.0.0.Alpha2}
Hibernate Commons Annotations {5.1.0.Final}
Hibernate Validator 6.1.0.Alpha6

You should (probably) use a consistent set of libraries.

Also, since you are using "alpha" libraries, you should expect bugs ... because "alpha" means that the code is still under development.

And when you encounter bugs, you should be:

doing some debugging of your own
checking the issue tracker to see if related issues have been reported, and
reporting issues yourself.

And if you don't want the hassle, use the most recent stable release (currently 5.4.4) rather than a development (alpha) release.

I've tried both "org.hibernate.dialect.MySQLDialect" and "org.hibernate.dialect.MySQLInnoDBDialect" dialects. But still getting the same error. — aks, Aug 14 '19 at 06:09
I'm using "Server version: 8.0.16 MySQL Community Server - GPL" database. — aks, Aug 14 '19 at 06:11

Hibernate session.delete issuing wrong SQL command

1 Answers1