Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-38691

Oracle session timestamp handling causes an outage when entering DST

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: RESOLVED
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4, 10.5, 10.6, 11.0, 20.2, 21.0, 22.0 [Tentative]
    • Fix Version/s: 22.0 [Tentative]
    • Component/s: Kernel
    • Labels:
      None
    • Environment:
      Oracle 11 database
    • 21 Status:
      Please Merge
    • 20 status:
      Please Merge
    • Previous Issue Keys:
      KNL-1421

      Description

      I noticed that the nightly Oracle instance was down between March 12,
      9pm-10pm EST, throwing the following exception when creating sessions:

           org.sakaiproject.portal.api.PortalHandlerException: java.lang.RuntimeException: SqlService.dbWrite failure
               at org.sakaiproject.portal.charon.handlers.XLoginHandler.doPost(XLoginHandler.java:50)
           caused by: java.lang.RuntimeException: SqlService.dbWrite failure
               at org.sakaiproject.db.impl.BasicSqlService.dbWriteCount(BasicSqlService.java:1145)
           caused by: java.sql.SQLDataException: ORA-01878: specified field not found in datetime or interval
           
               at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
               at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
               at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
               at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
               at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
               at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
               at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
               at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
               at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
               at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
               at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
               at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897)
               at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
               at com.zaxxer.hikari.proxy.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:61)
               at com.zaxxer.hikari.proxy.HikariPreparedStatementProxy.executeUpdate(HikariPreparedStatementProxy.java:-1)
               at org.sakaiproject.db.impl.BasicSqlService.dbWriteCount(BasicSqlService.java:1236)
               at org.sakaiproject.db.impl.BasicSqlService.dbWriteCount(BasicSqlService.java:1145)
               at org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1130)
               at org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1059)
               at org.sakaiproject.event.impl.UsageSessionServiceAdaptor$ClusterStorage.addSession(UsageSessionServiceAdaptor.java:849)
               at org.sakaiproject.event.impl.UsageSessionServiceAdaptor.startSession(UsageSessionServiceAdaptor.java:310)
               at org.sakaiproject.event.impl.UsageSessionServiceAdaptor.login(UsageSessionServiceAdaptor.java:515)
               at org.sakaiproject.event.impl.UsageSessionServiceAdaptor.login(UsageSessionServiceAdaptor.java:498)
      

      This appears to be due to the session handling trying to store a UTC
      timestamp in the SESSION_START column with an EST timezone set. Since
      9pm EST == 2am UTC, it ends up trying to store "02:00:00 EST", which
      isn't a valid time due to the daylight savings switchover that happens
      on Sunday morning.

      I found one other case of this happening too:

      http://sakai-dev.collab.sakaiproject.narkive.com/QGSb47Ce/building-sakai-issue-with-oracle-timestamp-with-timezone-and-dst

      and I've linked the JIRA issues references in that post.

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  davidpbauer David P. Bauer
                  Reporter:
                  marktriggs Mark Triggs
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  8 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration