java.sql.SQLException

tip

MySQL doesn't support some types of characters (such as emojis) with the utf8 encoding. You need to be on MySQL 5.5+ and force utf8mb4 (everywhere, client and server). You can do this sending the query "SET NAMES utf8mb4". Check this https://goo.gl/3E2qzg

You have a different solution? A short tip here would help you and many other users who saw this issue last week.

  • Given the code and testcase below: {code} /** * Created by Pawel_Solarski on 21.09.2016. */ package org.hibernate.bugs; import org.junit.After; import org.junit.Before; import org.junit.Test; import javax.persistence.*; import java.util.Date; import java.util.List; /** * This template demonstrates how to develop a test case for Hibernate ORM, using the Java Persistence API. */ public class JPAUnitFieldResultMappingTestCase { private EntityManagerFactory entityManagerFactory; @Before public void init() { entityManagerFactory = Persistence.createEntityManagerFactory("templatePU"); } @After public void destroy() { entityManagerFactory.close(); } // Entities are auto-discovered, so just add them anywhere on class-path // Add your tests, using standard JUnit. @Test public void shouldMapNativeQueryUsingFieldResultMapping() throws Exception { EntityManager entityManager = entityManagerFactory.createEntityManager(); entityManager.getTransaction().begin(); entityManager.persist(new AuditLog(1L, "foo", new Date())); entityManager.persist(new AuditLog(2L, "bar", new Date())); entityManager.getTransaction().commit(); entityManager.close(); entityManager = entityManagerFactory.createEntityManager(); // new EntityManager instance is being created entityManager.getTransaction().begin(); List<AuditLog> list = entityManager.createNamedQuery("getAuditLogByUsername", AuditLog.class).setParameter("username", "foo").getResultList(); list.stream().forEach(System.out::println); entityManager.getTransaction().commit(); entityManager.close(); } } @Entity @SqlResultSetMapping( name = "auditLogResultSet", entities = @EntityResult( entityClass = AuditLog.class, fields = { @FieldResult(name = "id", column = "id"), @FieldResult(name = "ts", column = "insert_date"), @FieldResult(name = "username", column = "username") } ) ) @NamedNativeQuery( name = "getAuditLogByUsername", query = "select id, username, ts as insert_date from auditlog where username = :username", resultSetMapping = "auditLogResultSet" ) class AuditLog { @Id private Long id; private String username; @Temporal(TemporalType.TIMESTAMP) @Column(name = "ts") private Date date; public AuditLog() { } public AuditLog(Long id, String username, Date date) { this.id = id; this.username = username; this.date = date; } public AuditLog(Long id, String username) { this(id, username, new Date()); } public Long getId() { return id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } @Override public String toString() { return "AuditLog{" + "id=" + id + ", username='" + username + '\'' + ", date=" + date + '}'; } } {code} The following exception is thrown: {code} Hibernate: insert into AuditLog (ts, username, id) values (?, ?, ?) Hibernate: insert into AuditLog (ts, username, id) values (?, ?, ?) Hibernate: select id, username, ts as insert_date from auditlog where username = ? wrz 21, 2016 11:49:27 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 0, SQLState: S0022 wrz 21, 2016 11:49:27 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: Column 'ts2_2_0_' not found. wrz 21, 2016 11:49:27 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop INFO: HHH10001008: Cleaning up connection pool [jdbc:mysql://localhost:3306/test] javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1383) at org.hibernate.Query.getResultList(Query.java:417) at org.hibernate.bugs.JPAUnitFieldResultMappingTestCase.shouldMapNativeQueryUsingFieldResultMapping(JPAUnitFieldResultMappingTestCase.java:47) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.junit.runner.JUnitCore.run(JUnitCore.java:137) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42) at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262) at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.loader.Loader.doList(Loader.java:2612) at org.hibernate.loader.Loader.doList(Loader.java:2592) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2424) at org.hibernate.loader.Loader.list(Loader.java:2419) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2119) at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:952) at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:147) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1374) ... 31 more Caused by: java.sql.SQLException: Column 'ts2_2_0_' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076) at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5647) at org.hibernate.type.descriptor.sql.TimestampTypeDescriptor$2.doExtract(TimestampTypeDescriptor.java:76) at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:258) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:254) at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:244) at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:327) at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2775) at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1735) at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1661) at org.hibernate.loader.Loader.getRow(Loader.java:1550) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:734) at org.hibernate.loader.Loader.processResultSet(Loader.java:979) at org.hibernate.loader.Loader.doQuery(Loader.java:937) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:343) at org.hibernate.loader.Loader.doList(Loader.java:2609) ... 39 more {code} The problem is gone when using same EntityManager instance throughout all transactions
    via by Pawel Solarski,
  • SQL exception launching RDFizer
    via GitHub by scanbit
    ,
  • GitHub comment 1#135685107
    via GitHub by chatainsim
    ,
    • java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1101) at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:126) at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6588) at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6188) at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6226) at org.grails.datastore.gorm.GormStaticApi$_methodMissing_closure2.doCall(GormStaticApi.groovy:102) at org.bbop.apollo.PreferenceService.$tt__getCurrentOrganismPreference(PreferenceService.groovy:130) at org.bbop.apollo.PreferenceService.$tt__getCurrentOrganismPreference(PreferenceService.groovy:181) at org.bbop.apollo.AnnotatorService.$tt__getAppState(AnnotatorService.groovy:46) at org.bbop.apollo.AnnotatorController.$tt__getAppState(AnnotatorController.groovy:371) at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:198) at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63) at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383) at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)

    Users with the same issue

    DoktorDoener666DoktorDoener666
    1 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    ivanszkypeterivanszkypeter
    490 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    linxiaolonglinxiaolong
    1 times, last one,
    146 more bugmates