Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

SQL Error 17268 : Year out of range (Java/Spring)

Tags:

java

sql

spring

hibernate

oracle

I'm facing an issue related to Oracle / Hibernate when I'm doing a simple SELECT on the database, and I can't find any useful information anywhere on this issue. The application uses Spring Data to create the queries on its own. The database is Oracle 11.02.0.4, and same version of JDBC driver.

SQL Error is 17268 : Year out of range.

I know the message is quite obvious, however when doing the query printed in the logs manually on the database, I have no issue and see that the year of the date field is well set to 2016...

I've tried with a Java object mapping those fields on Date and Timestamp, same error. Strange thing is that the problem suddenly appeared for the client, and I am unable to reproduce the issue on my own environment (client is running WebSphere 8.5.5).

Any pointers where this error might come from, or what should I investigate?

See stack trace below:

[11/14/16 12:05:03:370 CET] 000000d6 SystemOut     O 12:05:03.370 [WebContainer : 1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 17268, SQLState: 99999
[11/14/16 12:05:03:371 CET] 000000d6 SystemOut     O 12:05:03.371 [WebContainer : 1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Year out of range.
[11/14/16 12:05:03:406 CET] 000000d6 ServletWrappe E com.ibm.ws.webcontainer.servlet.ServletWrapper service SRVE0014E: Uncaught service() exception root cause dispatcher: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.GenericJDBCException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:979)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:575)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1232)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:781)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:480)
    at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
    at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1114)
    at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3926)
    at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:304)
    at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1007)
    at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817)
    at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:200)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:463)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:530)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:316)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:287)
    at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.determineNextChannel(SSLConnectionLink.java:1049)
    at com.ibm.ws.ssl.channel.impl.SSLConnectionLink$MyReadCompletedCallback.complete(SSLConnectionLink.java:643)
    at com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1818)
    at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
    at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
    at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
    at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
    at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
    at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
    at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1881)
Caused by: org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.GenericJDBCException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:415)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:418)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:111)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy83.findByEnvelopeIdAndStatusInAndCurrentVersionOrderByTecidAsc(Unknown Source)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
    ...
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
    at java.lang.reflect.Method.invoke(Method.java:620)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
    ... 29 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)
    at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:110)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 72 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3285)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3183)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3525)
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:158)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:453)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:345)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
    at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:67)
    at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1187)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1253)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    ... 85 more
Caused by: java.sql.SQLException: Year out of range.
    at oracle.jdbc.driver.DateCommonBinder.setOracleCYMD(OraclePreparedStatement.java:19395)
    at oracle.jdbc.driver.TimestampBinder.bind(OraclePreparedStatement.java:19668)
    at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:3166)
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2378)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3608)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpdate(WSJdbcPreparedStatement.java:1187)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:804)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    ... 98 more

Generated query:

select
    documentdb0_.tecid as tecid1_3_,
    documentdb0_.comments_nb as comments_nb2_3_,
    documentdb0_.content_path as content_path3_3_,
    documentdb0_.content_size as content_size4_3_,
    documentdb0_.creation_date as creation_date5_3_,
    documentdb0_.creator as creator6_3_,
    documentdb0_.current_version as current_version7_3_,
    documentdb0_.docfamily as docfamily8_3_,
    documentdb0_.doctype as doctype9_3_,
    documentdb0_.attached_document_id as attached_document10_3_,
    documentdb0_.envelope_id as envelope_id11_3_,
    documentdb0_.filename as filename12_3_,
    documentdb0_.hash as hash13_3_,
    documentdb0_.mime_type as mime_type14_3_,
    documentdb0_.modify_date as modify_date15_3_,
    documentdb0_.name as name16_3_,
    documentdb0_.number_of_pages as number_of_pages17_3_,
    documentdb0_.parent_id as parent_id18_3_,
    documentdb0_.root_id as root_id19_3_,
    documentdb0_.serie as serie20_3_,
    documentdb0_.status as status21_3_,
    documentdb0_.teclock as teclock22_3_,
    documentdb0_.version_id as version_id23_3_
from
    tb_document documentdb0_
where
    documentdb0_.envelope_id=44104
    and (documentdb0_.status in (‘TO_SIGN') )
    and documentdb0_.current_version=1
order by
    documentdb0_.tecid asc

Sample data retrieve by the query (manually) :

TECID1_3_;COMMENTS_NB2_3_;CONTENT_PATH3_3_;CONTENT_SIZE4_3_;CREATION_DATE5_3_;CREATOR6_3_;CURRENT_VERSION7_3_;DOCFAMILY8_3_;DOCTYPE9_3_;ATTACHED_DOCUMENT10_3_;ENVELOPE_ID11_3_;FILENAME12_3_;HASH13_3_;MIME_TYPE14_3_;MODIFY_DATE15_3_;NAME16_3_;NUMBER_OF_PAGES17_3_;PARENT_ID18_3_;ROOT_ID19_3_;SERIE20_3_;STATUS21_3_;TECLOCK22_3_;VERSION_ID23_3_
55054;0;/data/f5cbf3373b4d7bdc5abfc7df92334f54-20161024091158880.pdf;190354;24-oct-16;993513;1;329;203;;44104;22878767.pdf;f5cbf3373b4d7bdc5abfc7df92334f54;application/pdf;24-oct-16;CONSENT;2;;55054;11;TO_SIGN;3;1
like image 447
Deathtiny Avatar asked Nov 14 '16 11:11

Deathtiny

2 Answers

The issue was in fact strangely not at the line indicated by Java, which led me nowhere. In step-by-step debugging mode, I've stumbled upon an update query done that was causing an issue before the SELECT.

A date with year 10000 was trying to be introduced during UPDATE, when the maximum supported date by Oracle is 9999. This overflowing date was due to a deadline date read from another system, and defined to 31/12/9999 23:23:59: with a bad timezone it was read as 10000 January.

like image 99
Deathtiny Avatar answered Sep 27 '22 22:09

Deathtiny

SQL Error is 17268 : Year out of range.

Following error Year out of range will occur when we try to insert the Year value more than 9999. ORACLE supports till 9999 - 10000 leads Year out of range

For Util date their is limit for Year you can use any number as 777777 for year, But when you try to insert the same util date into the oracle server it leads to yearOutOfRange.

I have faced the same issue yearOutOfRange in multi-threading concept. When i try to convert String represented date to Sql date with timestamp. To reproduce the same you can the below code form MaintainEqualThreadsPatallel.loopFunction(...)

public static Timestamp getTimeStamp(Date date) { // yearOutOfRange
    //Date dbResponseTime = new java.util.Date();
    Timestamp timestamp = new java.sql.Timestamp(date.getTime());
    System.out.println("Timestamp :"+ timestamp);
    return timestamp;
}
private static final SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
public static Date stringToDate(String date) throws ParseException { // synchronized
    sdf.setTimeZone(TimeZone.getTimeZone("CET"));
    return sdf.parse(date);
}

Output:

getTimeStamp( stringToDate("20-Feb-2020 20:53:12") );

Timestamp :2020-02-20 20:53:12.0
Timestamp :1970-02-20 20:53:12.0
Timestamp :0020-02-20 02:53:12.0
Timestamp :20200-02-20 20:53:12.0  // yearOutOfRange
Timestamp :0020-02-20 02:53:12.0
Timestamp :2020-02-21 04:52:12.0
Timestamp :0012-02-21 20:53:12.0
java.lang.NumberFormatException: For input string: ""

From java.sql.Date source code we can observe that year supports till 9999 i.e., YYYY year code of date format.

Util Date to SQL Date:

java.util.Date utilDate = new java.util.Date();
java.sql.Date sqlDate = new java.sql.Date( utilDate.getTime() );

// SQL Source: new Date(date.getYear() - 1900, date.getMonthValue() -1, date.getDayOfMonth());

Oracle date functions: with some trials as follows

TO_DATE('08-FEB-10000', 'DD-MON-YYYY')
SQL Error: ORA-01830: date format picture ends before converting entire input string

TO_DATE('08-FEB-10000 13:30:12', 'DD-MON-YYYYY HH24:MI:SS')
SQL Error: ORA-01812: year may only be specified once

To avoid SQL Error Year out of range use any one of the following approach

  • SimpleDateFormat.parse(strDate) function call form synchronized block.
  • LocalDateTime.parse(strDate, DateTimeFormatter)
public static Timestamp getTimestamp(String strDate) {
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss");
    LocalDateTime dateTime = LocalDateTime.parse(strDate, formatter);
                            //LocalDateTime.of(10000, Month.DECEMBER, 30, 12, 10, 05);

    Timestamp valueOf = java.sql.Timestamp.valueOf(dateTime);
    System.out.format("TimeStamp Local : [%-30s]\n", valueOf.toString());

    java.sql.Date sqlDate = new java.sql.Date( valueOf.getTime() );
    System.out.format("java.sql.Date : [%-30s]\n", sqlDate);

    return valueOf;

    /*LocalDateTime with = dateTime.with(TemporalAdjusters.lastInMonth(DayOfWeek.SUNDAY));
    int dayOfMonth = with.getDayOfMonth();
    System.out.println("Last Sunday of Month: "+dayOfMonth);*/
}
like image 44
Yash Avatar answered Sep 28 '22 00:09

Yash
Sign in to Comment

Related questions

Jackson - generic getValue method
Wait for multiple async calls to finish in RxJava
How to stop a running Thread in Java
Testing of a new Java parser
Why can a method that takes varargs be optimised into a series of monomorphic calls only if it is static?
Spring RestController produces charset=UTF-8
Windows native File chooser in java
for loop makes pause every 8 million iterations - why?
Connection Pool Leak Causes
volatile vs threadLocal in java
Download a database file to the app directory
Replacing lambda expression with method reference
How to pass the value by reference in java
Maven 3: How to exclude generated sources from Xlint check?
Spring Data JPA/Boot: findBy ... or
Missing field when deserializing using Jackson (Polymorphic)
JavaFX - Many Static FXML Controllers
Spring REST Controller not mapped
Finding distinct from collections in mongodb
Array's lookup time complexity vs. how it is stored

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!